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  (Neil Conway <neilc@samurai.com>)
Список 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 по дате отправления:

Предыдущее
От: Tyler MacDonald
Дата:
Сообщение: W3C XML Schema -> PostgreSQL?
Следующее
От: Jerry LeVan
Дата:
Сообщение: Postgresql won't run after upgrade to fc5