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 12:24:41 -0400
Message-ID: <26708.1300033481@sss.pgh.pa.us> (raw)
In-Reply-To: <C0B87DEF8EF4F94C9D8FBD3151B627B26C0C23@digimapserver.digimap.local>
References: <C0B87DEF8EF4F94C9D8FBD3151B627B26C0C23@digimapserver.digimap.local>
"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?
I tried to duplicate this locally, without success, so there's some
contributing factor you've neglected to mention. Can you put together a
self-contained test case that acts like this?
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: <26708.1300033481@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