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
In-reply-to: 
 <C0B87DEF8EF4F94C9D8FBD3151B627B26C0C23@digimapserver.digimap.local>
References: 
 <C0B87DEF8EF4F94C9D8FBD3151B627B26C0C23@digimapserver.digimap.local>
Comments: In-reply-to "John Surcombe" <John.Surcombe@digimap.gg>
	message dated "Sat, 12 Mar 2011 10:07:41 +0000"
Date: Sun, 13 Mar 2011 12:24:41 -0400
Message-ID: <26708.1300033481@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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