Postgre database ignoring created index ?!
- by drasto
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 ?
