index scan backward plan question
От | Kevin Murphy |
---|---|
Тема | index scan backward plan question |
Дата | |
Msg-id | 442076E9.5070106@genome.chop.edu обсуждение исходный текст |
Ответы |
Re: index scan backward plan question
|
Список | 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
В списке pgsql-general по дате отправления: