Postgre database ignoring created index ?!

Question

I have an Postgre database and a table called my_table. There are 4 columns in that table (id, column1, column2, column3). The id column is primary key, there are no other constrains or indexes on columns. The table has about 200000 rows.

I want to print out all rows which has value of column column2 equal(case insensitive) to 'value12'. I use this:

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

here is the execution plan for this statement(result of 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 consider this to be to slow so I create an index on column column2 for better prerformance of searches:

CREATE INDEX my_index ON my_table (lower(column2))

Now I ran the same select:

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

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

Answer 1

You might need to use:

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

In general, the expression involving the column has to match the index expression in order for it to be able to optimize on that index.

Answer 2

You are not actually comparing the lower case values of column2. You are comparing the (literal) values of column2 with a lowercased version of 'value12'.

Did you mean to compare against lower(column2)?

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

Answer 3

In addition to answers about lower(), this should persuade PostgreSQL to use indexes -- run explain to verify.

set enable_seqscan = false;

You may also want to run vacuum analyze after creating the index.

Answer 4

This:

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

...assumes that all the data in column2 is lowercase. Cases where this is not true will not match.

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

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

The decision is made by the optimizer, which takes things in addition to indexes into account to return the result as fast as possible. From what I understand, Postgres doesn't provide the syntax to force index use like you see in MySQL, Oracle or SQL Server.

You can look at using 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 gathering should be performed on that column. This helps you to control the generated query plans without having slow vacuum and analyze operations because of generating large amounts of stats for all tables and columns.

Reference: Performance Tuning PostgreSQL