O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required