O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

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 is my query 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 it is that the optimizer believes that 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 of using it with an index and without, using the following:

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=# analyse; ANALYZE mydb=# explain analyse select count(*) from itable where id > 500; QUERY PLAN --------------------------------------------------------------------- ...

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