postgresql function index not being used by query

Question

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)

• Mladen Uzelac: There is no PostGIS 9.5.0. There is Postgresql 9.5.0 and PostGIS 2.2.1.
• Amala: @MladenUzelac Thank you. I corrected. I am trying to upgrade the DB now to Postgresql 9.5.2 and PostGIS 2.2.2. I will see if that corrects the issue.

Answer 1

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.

• András Váczi: That 5% is not an exact number - it can be 10%, too, depending on circumstances.
• Amala: You are just looking at the query planner. As the other comment said, it is some sort of estimate. In fact it returns 0 results. I will add to the question with the results in Postgresql 9.5.1 which has the correct result.

Answer 2

This was an issue with 9.5.0. I upgraded to 9.5.2 and things are ok.