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@peak6.com

______________________________________________

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