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 |
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 |