Choice of bitmap scan over index scan

From: Mathieu De Zutter <mathieu(at)dezutter(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Choice of bitmap scan over index scan
Date: 2010-01-10 12:28:11
Message-ID: d4468d971001100428v30236b55nb4cc82d216c7819e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

Part of a larger problem, I'm trying to optimize a rather simple query which
is basically:
SELECT * FROM table WHERE indexed_column > ... ORDER BY indexed_column DESC;

(see attachment for all details: table definition, query, query plans)

For small ranges it will choose an index scan which is very good. For
somewhat larger ranges (not very large yet) it will switch to a bitmap scan
+ sorting. Pgsql probably thinks that the larger the range, the better a
bitmap scan is because it reads more effectively. However, in my case, the
larger the query, the worse bitmap+sort performs compared to index scan:

Small range (5K rows): 5.4 msec (b+s) vs 3.3 msec (i) -- performance penalty
of ~50%
Large range (1.5M rows): 6400 sec (b+s) vs 2100 msec (i) -- performance
penalty of ~200%

How can I make pgsql realize that it should always pick the index scan?

Thanks!

Kind regards,
Mathieu

Attachment Content-Type Size
log_event_plan.txt text/plain 6.9 KB
postgresql.conf application/octet-stream 16.3 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeremy Harris 2010-01-10 14:04:47 Re: Choice of bitmap scan over index scan
Previous Message Pierre Frédéric Caillaud 2010-01-10 10:52:01 Re: PG optimization question