Database Reference
In-Depth Information
As you see, PostgreSQL estimates (rightly) that this query is better-served by performing a
sequential scan.
How do I force a query to use an index
Here, we show how to force the database to use an index. In fact, it is not possible to tell
PostgreSQL to use an index, but you can trick it into using one by telling the optimizer that all
other options are prohibitively expensive.
Getting ready
First, you have to make sure that it is worth it to use the index. This is best done on a
development or testing system, but if done carefully, can also be done on the production
server. Sometimes it is very hard to generate a load similar to a live system in a test
environment, and then your best option may be carefully testing on live.
As the PostgreSQL optimizer does not take into account the parallel load caused by other
backends, it may make sense to lie to PostgreSQL about some statistics in order to make it
use indexes.
How to do it...
set enable_seqscan to false
If you do:
set enable_seqscan to false;
you tell PostgreSQL that it is really very expensive to do sequential scans. It still does a
seqscan
(instead of failing) if it is the only way to do the query:
mydb=# create table table_with_no_index(id int);
CREATE TABLE
mydb=# set enable_seqscan to false;
SET
mydb=# explain select * from table_with_no_index where id > 10;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on table_with_no_index (cost=10000000000.00..10000000040.00
rows=800 width=4)
Filter: (id > 10)
(2 rows)
but it is very likely that it selects some other way of doing the query as cheaper:
mydb=# create index table_with_no_index_now_has_one on table_with_no_
index(id);
CREATE INDEX
