PostgreSQL 9 Administration Cookbook - Second Edition

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 does not.

There can 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 an index.

How to do it…

Force index usage and compare plan costs with an index and without, like this:

mydb=# CREATE TABLE itable(id int PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "itable_pkey" for table "itable"
CREATE TABLE
mydb=# INSERT INTO itable SELECT generate_series(1,10000);
INSERT 0 10000
mydb=# ANALYZE;
ANALYZE
mydb=# EXPLAIN ANALYZE SELECT count(*) FROM itable WHERE ...