Database Reference
In-Depth Information
mydb=# explain select * from table_with_no_index where id > 10;
QUERY PLAN
----------------------------------------------------------------------
Bitmap Heap Scan on table_with_no_index (cost=10.45..30.45 rows=800
width=4)
Recheck Cond: (id > 10)
-> Bitmap Index Scan on table_with_no_index_now_has_one
(cost=0.00..10.25 rows=800 width=0)
Index Cond: (id > 10)
(4 rows)
Once you enable
seqscans
again, it will use a sequential scan instead of the more costly
(in this case) bitmap index scan as follows:
mydb=# set enable_seqscan to true;
SET
mydb=# explain select * from table_with_no_index where id > 10;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on table_with_no_index
(cost=0.00..40.00 rows=800 width=4)
Filter: (id > 10)
(2 rows)
Lower random_page_cost
For a softer nudge towards using indexes, set
random_page_cost
to a lower value, maybe
even make it equal to
seq_page_cost
. This makes PostgreSQL prefer index scans on more
occasions, but does still not produce entirely unreasonable plans, at least for cases where
data is mostly cached in shared buffers or systems disk cache.
Default values for these parameters are as follows:
random_page_cost = 4;
seq_page_cost = 1;
Try setting:
set random_page_cost = 2;
and see if it helps; if not, set it to
1
.
