Have Geek Brain

Postgre database ignoring created index?

I have a Postgre database and a my_table. The table has 4 columns ( id

, column1

, column2

, column3

). The column id

is the primary key, there are no other constraints or indexes on the columns. The table has about 200,000 rows.

I want to print all rows for which the column value column2

is equal (case insensitive) to 'value12'

. I am using this:

SELECT * FROM my_table WHERE column2 = lower('value12')

here is the execution plan for this statement (result set enable_seqscan=on; EXPLAIN SELECT * FROM my_table WHERE column2 = lower('value12')

):

Seq Scan on my_table (cost=0.00..4676.00 rows=10000 width=55)
Filter: ((column2)::text = 'value12'::text)

I believe this should slow down, so I create an index on the column column2

for better query performance:

CREATE INDEX my_index ON my_table (lower(column2))

Now I ran the same selection:

SELECT * FROM my_table WHERE column2 = lower('value12')

and I expect it to be much faster because it can use an index. However, it is not as fast, as slow as before. So I check the execution plan and it is similar to the previous one (see above). So it still uses sequential script and ignores the index! Where is the problem?

+2


a source to share


4 answers


It:

SELECT * 
 FROM my_table 
WHERE column2 = lower('value12')

... assumes that all data in column2

is string values. The cases where this is not true will not coincide.

SELECT * 
 FROM my_table 
WHERE LOWER(column2) = LOWER('value12')

Using a function on a column can render the index useless.



The decision is made by the optimizer, which takes into account things in addition to the indexes in order to return the result as quickly as possible. As far as I understand, Postgres does not provide the syntax for enforcing the index as you see in MySQL, Oracle, or SQL Server.

You can see how to use SET STATISTICS:

ALTER TABLE <table> ALTER COLUMN <column> SET STATISTICS <number>;

This value can be a number between 0 and 1000 and helps PostgreSQL determine what level of statistics collection should be performed on this column. It helps you monitor the generated query plans without a slow vacuum and analyze operations due to the generation of large amounts of statistics for all tables and columns.

Ref: PostgreSQL Performance Tuning

+4


a source


You are not actually comparing lowercase values column2

. You are comparing (literal) values column2

to the lower version of "value12".

Did you mean the comparison with lower(column2)

?



SELECT * FROM my_table WHERE lower(column2) = lower('value12')
+2


a source


You may need to use:

WHERE lower(column2) = lower('value12')

In general, the expression containing the column must match the index expression in order for it to optimize that index.

+1


a source


In addition to the answers about lower()

, this should convince PostgreSQL to use indexes - run explain

to check.

set enable_seqscan = false;

You can also run vacuum analyze

after index creation.

0


a source






More articles:


All Articles