Postgresql function index not being used by query

indexpostgresql

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.