From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Surcombe <John.Surcombe@digimap.gg>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner wrongly shuns multi-column index for select .. order by col1, col2 limit 1
Date: Sun, 13 Mar 2011 18:10:29 -0400
Message-ID: <5238.1300054229@sss.pgh.pa.us> (raw)
In-Reply-To: <26708.1300033481@sss.pgh.pa.us>
References: <C0B87DEF8EF4F94C9D8FBD3151B627B26C0C23@digimapserver.digimap.local>
<26708.1300033481@sss.pgh.pa.us>
I wrote:
> "John Surcombe" <John.Surcombe@digimap.gg> writes:
>> When we 'EXPLAIN' this query, PostgreSQL says it is using the index
>> idx_receiveddatetime. The way the application is designed means that in
>> virtually all cases the query will have to scan a very long way into
>> idx_receiveddatetime to find the first record where userid = 311369000.
>> If however we delete the idx_receiveddatetime index, the query uses the
>> idx_userid_receiveddatetime index, and the query only takes a few
>> milliseconds.
> That's just bizarre ... it knows the index is applicable, and the cost
> estimates clearly favor the better index, so why did it pick the worse
> one?
No, scratch that, I misread the plans. It *is* picking the plan it
thinks has lower cost; it's just a mistaken cost estimate. It's strange
though that the less selective indexscan is getting a lower cost
estimate. I wonder whether your table is (almost) perfectly ordered by
receiveddatetime, such that the one-column index has correlation close
to 1.0. That could possibly lower the cost estimate to the point where
it'd appear to dominate the other index. It'd be useful to see the
pg_stats.correlation value for both the userid and receiveddatetime
columns.
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, John.Surcombe@digimap.gg
Subject: Re: Planner wrongly shuns multi-column index for select .. order by col1, col2 limit 1
In-Reply-To: <5238.1300054229@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