From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@peak6.com
Cc: 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 13:38:17 -0400
Message-ID: <17124.1300297097@sss.pgh.pa.us> (raw)
In-Reply-To: <4D80EDC2.8010103@peak6.com>
References: <AANLkTi=25ZFSjMPYfXZg9UPpTkzKMnETryfyRArZXfa9@mail.gmail.com>
<4D80EDC2.8010103@peak6.com>
Shaun Thomas <sthomas@peak6.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
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: tgl@sss.pgh.pa.us, 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: <17124.1300297097@sss.pgh.pa.us>
* 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