To: "John Surcombe" <John.Surcombe@digimap.gg>
cc: pgsql-performance@postgresql.org,
	"Kieran Senior" <Kieran.Senior@digimap.gg>
Subject: Re: Planner wrongly shuns multi-column index for select .. order by
	col1, col2 limit 1
In-reply-to: 
 <C0B87DEF8EF4F94C9D8FBD3151B627B26C0C47@digimapserver.digimap.local>
References: 
 <C0B87DEF8EF4F94C9D8FBD3151B627B26C0C23@digimapserver.digimap.local>
	<26708.1300033481@sss.pgh.pa.us> <5238.1300054229@sss.pgh.pa.us>
	<C0B87DEF8EF4F94C9D8FBD3151B627B26C0C47@digimapserver.digimap.local>
Comments: In-reply-to "John Surcombe" <John.Surcombe@digimap.gg>
	message dated "Mon, 14 Mar 2011 09:37:40 -0000"
Date: Mon, 14 Mar 2011 13:24:33 -0400
Message-ID: <13031.1300123473@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"John Surcombe" <John.Surcombe@digimap.gg> writes:
>> It'd be
>> useful to see the pg_stats.correlation value for both the userid and
>> receiveddatetime columns.

> Yes, the table is indeed nearly perfectly ordered by receiveddatetime
> (correlation 0.998479).  correlation on userid is -0.065556.  n_distinct
> on userid is also low: 1097.

Ah-hah.

> Is the problem perhaps something like the following:  PostgreSQL is
> thinking that because there are not many userids and there is low
> correlation, that if it just scans the table from the top in date order,
> this will be cheap (because receiveddatetime correlation is high so it
> won't have to seek randomly), and it won't have to scan very far before
> it finds the first row with a matching userid.

There's some of that, but I think the main problem is that there's a
very high discount on the cost estimate for a perfectly-correlated
index, and that makes it end up looking cheaper to use than the
uncorrelated one.  (It doesn't help any that we don't do correlation
properly for multicolumn indexes; but given what you say above, the
correlation estimate for the two-column index would be small even if
we'd computed it exactly.)

You might find that reducing random_page_cost would avoid the problem.
That should reduce the advantage conferred on the high-correlation
index, and it probably would represent your actual configuration better
anyway, given the results you're showing here.

			regards, tom lane