Discovering why a query is not using an index

This recipe explains what to do if you think your query should use an index, but it isn’t.

There could be several reasons for this but, most often, the reason is that the optimizer believes that, based on the available distribution statistics, it is cheaper and faster to use a query plan that does not use that specific index.

Getting ready

First, check that your index exists, and ensure that the table has been analyzed. If there is any doubt, rerun it to be sure—though it’s better to do this only on specific tables:

postgres=# ANALYZE;
ANALYZE

How to do it…

Force index usage and compare plan costs with an index and without, as follows:

postgres=# EXPLAIN ANALYZE SELECT count(*) FROM itable WHERE id > 500;
                         QUERY PLAN
---------------------------------------------------------------------
  Aggregate  (cost=188.75..188.76 rows=1 width=0)
            (actual...