index scan backward plan question

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

Responses

Browse pgsql-general by date

  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