From: Shaun Thomas <sthomas@peak6.com>
To: Timothy Garnett <tgarnett@panjiva.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
Date: Wed, 16 Mar 2011 12:05:06 -0500
Message-ID: <4D80EDC2.8010103@peak6.com> (raw)
In-Reply-To: <AANLkTi=25ZFSjMPYfXZg9UPpTkzKMnETryfyRArZXfa9@mail.gmail.com>
References: <AANLkTi=25ZFSjMPYfXZg9UPpTkzKMnETryfyRArZXfa9@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, tgarnett@panjiva.com
Subject: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
In-Reply-To: <4D80EDC2.8010103@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox