Database Reference
In-Depth Information
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
---------------------------------------------------------------------
Aggregate (cost=188.75..188.76 rows=1 width=0)
(actual time=37.958..37.959 rows=1 loops=1)
-> Seq Scan on itable (cost=0.00..165.00 rows=9500 width=0)
(actual time=0.290..18.792 rows=9500
loops=1)
Filter: (id > 500)
Total runtime: 38.027 ms
(4 rows)
mydb=# set enable_seqscan to false;
SET
mydb=# explain analyse select count(*) from itable where id > 500;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=323.25..323.26 rows=1 width=0)
(actual time=44.467..44.469 rows=1 loops=1)
-> Index Scan using itable_pkey on itable
(cost=0.00..299.50 rows=9500 width=0)
(actual time=0.100..23.240 rows=9500 loops=1)
Index Cond: (id > 500)
Total runtime: 44.556 ms
(4 rows)
