I have a table for which PG 8.3 is guessing wrong about a plan when the
result set gets large. For these large result sets, it uses an Index
Scan Backward/Filter (slower) instead of Sort/Bitmap Heap Scan/Bitmap
Index Scan (faster). See below.
I fooled around with various planner variables, but the only thing I
found that worked was:
set enable_indexscan = off;
BTW, without turning index scans off, the largest queries take many
minutes to return (in fact, I've never had the patience to wait for the
result).
Is there some other way I can persuade PG to not ever use the Index Scan
Backward approach?
Thanks,
Kevin Murphy
Limit (cost=104804.79..110320.84 rows=25 width=229) (actual
time=1653.686..10381.264 rows=25 loops=1)
-> Index Scan Backward using merged_weight_date_idx on merged
(cost=0.00..31295593.98 rows=141839 width=229) (actual
time=3.888..10380.783 rows=500 loops=1)
Filter: (symbol = 'ERVK6'::text)
Total runtime: 10381.552 ms
Limit (cost=278766.87..278766.93 rows=25 width=229) (actual
time=5140.604..5140.639 rows=25 loops=1)
-> Sort (cost=278765.81..279120.41 rows=141839 width=229) (actual
time=5140.098..5140.571 rows=450 loops=1)
Sort Key: weight, date
-> Bitmap Heap Scan on merged (cost=920.44..245618.72
rows=141839 width=229) (actual time=61.265..354.795 rows=142814 loops=1)
Recheck Cond: (symbol = 'ERVK6'::text)
-> Bitmap Index Scan on merged_symbol_idx
(cost=0.00..920.44 rows=141839 width=0) (actual time=58.846..58.846
rows=142814 loops=1)
Index Cond: (symbol = 'ERVK6'::text)
Total runtime: 5903.179 ms
