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

Shaun Thomas <sthomas(at)peak6(dot)com> writes:
> 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);

That isn't going to dissuade the planner from using that index for this
query. It would result in the scan being a forward indexscan instead of
backwards. Now it'd be worth trying that, to see if you and Kevin are
right that it's the backwards aspect that's hurting. I'm not convinced
though. I suspect the issue is that the planner is expecting the target
records (the ones selected by the filter condition) to be approximately
equally distributed in the month ordering, but really there is a
correlation which causes them to be much much further back in the index
than it expects. So a lot more of the index has to be scanned than it's
expecting.

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

Flipping bl_number around to the front would prevent this index from
being used in this way, but it might also destroy the usefulness of the
index for its intended purpose. Tim didn't show us the queries he
wanted this index for, so it's hard to say if he can fix it by
redefining the index or not.

regards, tom lane