Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3

On 03/15/2011 01:23 PM, Timothy Garnett wrote:

> Column | Type
> --------------------------+------------------------+
> id | integer |
> bl_number | character varying(16) |
> month | date |
> buyer_id | integer |
> supplier_id | integer |

Ok. In your table description, you don't really talk about the
distribution of bl_number. But this part of your query:

ORDER BY month DESC LIMIT 100 OFFSET 0

Is probably tricking the planner into using that index. But there's the
fun thing about dates: we almost always want them in order of most
recent to least recent. So you might want to try again with your
index_customs_records_on_month_and_bl_number declared like this instead:

CREATE INDEX index_customs_records_on_month_and_bl_number
ON customs_records (month DESC, bl_number);

Or, if bl_number is more selective anyway, but you need both columns for
other queries and you want this one to ignore it:

CREATE INDEX index_customs_records_on_month_and_bl_number
ON customs_records (bl_number, month DESC);

Either way, I bet you'll find that your other queries that use this
index are also doing a backwards index scan, which will always be slower
by about two orders of magnitude, since backwards reads act basically
like random reads.

The effect you're getting is clearly exaggerated, and I've run into it
on occasion for effectively the entire history of PostgreSQL. Normally
increasing the statistics on the affected columns and re-analyzing fixes
it, but on a composite index, that won't necessarily be the case.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email