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;
production=# CREATE INDEX searches_helper_query_index ON searches(md5(helper_query));

production=# \d searches;
Column         |            Type             |                       Modifiers                       
helper_query   | text                        | 
 "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)
  • 1
    There is no PostGIS 9.5.0. There is Postgresql 9.5.0 and PostGIS 2.2.1. – Mladen Uzelac Apr 9 '16 at 12:57
  • @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. – Amala Apr 9 '16 at 18:39

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.

| improve this answer | |
  • That 5% is not an exact number - it can be 10%, too, depending on circumstances. – dezso Apr 9 '16 at 11:19
  • 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. – Amala Apr 9 '16 at 18:41

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.