From: | Kevin Murphy <murphy(at)genome(dot)chop(dot)edu> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | index scan backward plan question |
Date: | 2006-03-21 21:58:01 |
Message-ID: | 442076E9.5070106@genome.chop.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jerry LeVan | 2006-03-21 22:02:01 | Postgresql won't run after upgrade to fc5 |
Previous Message | Rushabh Doshi | 2006-03-21 21:39:55 | Re: db sever seems to be dropping connections |