I am trying to match a text field so I created an md5 function index as it suggested since a full index was not allowed because the text data was too large. But I can't understand why postgres is not using the index. I am using PostGIS 2.2.1 and Postgresql 9.5.0 but I have other function indexes which do use the index.
production=# set enable_seqscan = off;
SET
production=# CREATE INDEX searches_helper_query_index ON searches(md5(helper_query));
CREATE INDEX
production=# \d searches;
Column | Type | Modifiers
helper_query | text |
Indexes:
"searches_helper_query_index" btree (md5(helper_query))
production=# EXPLAIN SELECT id FROM "searches" WHERE md5(helper_query) = md5('abcd');
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on searches (cost=10000000000.00..10000017792.92 rows=1631 width=4)
Filter: (md5(helper_query) = 'e2fc714c4727ee9395f324cd2e7f331f'::text)
(2 rows)
The table has about 7000 rows and many other columns and indexes. I only included the relevant column.
I ran vacuum verbose analyze
on the table but it does not change anything either.
I also tried USING hash
since I only need equality but it did not help either.
"searches_helper_query_index" hash (md5(helper_query))
As a comment this seems to only be with Postgresql 9.5.0. Either PostGIS or regular Postgres. My development machine has 9.5.1 and does not have this issue.
dev=# EXPLAIN SELECT id FROM "searches" WHERE md5(helper_query) = md5('abcd') ;
QUERY PLAN
Index Scan using searches_helper_query_index on searches (cost=0.27..8.29 rows=1 width=4)
Index Cond: (md5(helper_query) = 'e2fc714c4727ee9395f324cd2e7f331f'::text) (2 rows)
Best Answer
Your
WHERE
condition is not selective enough. It returns 1631 rows which is 1/5 of the whole table, while it should be not more than 1/20 for an index to be used.