To: Mitchell Skinner <mitch@egcrc.net>
cc: Richard Huxton <dev@archonet.com>, pgsql-performance@postgresql.org
Subject: Re: same plan, add 1 condition, 1900x slower 
In-reply-to: <1131728255.10481.51.camel@firebolt> 
References: <1131630176.29496.91.camel@enzian>
 <10164.1131643400@sss.pgh.pa.us>
	<1131700635.29496.136.camel@enzian>
	<437485DB.50005@archonet.com> <18120.1131718624@sss.pgh.pa.us>
	<1131722682.29496.229.camel@enzian>
	<18883.1131723210@sss.pgh.pa.us> <19095.1131724394@sss.pgh.pa.us>
	<1131728255.10481.51.camel@firebolt>
Comments: In-reply-to Mitchell Skinner <mitch@egcrc.net>
	message dated "Fri, 11 Nov 2005 08:57:35 -0800"
Date: Fri, 11 Nov 2005 12:16:33 -0500
Message-ID: <19851.1131729393@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mitchell Skinner <mitch@egcrc.net> writes:
> On Fri, 2005-11-11 at 10:53 -0500, Tom Lane wrote:
>> what you need to do is incorporate the "source" value into the
>> external_id_map index key somehow.  Then the index scan would be able to
>> realize that there is no possibility of finding another row with source
>> = 'SCH'.  The simplest way is just to make a 2-column index

> I thought that's what I had done with the
> external_id_map_source_target_id index:
>     "external_id_map_source_target_id" btree (source, target_id)

> If I changed that index to be (target_id, source) would it make a difference?

[ fools around with a test case ... ]  Seems like not :-(.  PG is not
bright enough to realize that an index on (source, target_id) can be
used with a mergejoin on target_id, because the index sort order isn't
compatible.  (Given the equality constraint on source, there is an
effective compatibility.  I had thought that 8.1 might be able to
detect this, but it seems not to in a simple test case --- there may be
a bug involved there.  In any case 8.0 definitely won't see it.)  An
index on (target_id, source) would be recognized as mergejoinable, but
that doesn't solve the problem because an index condition on the second
column doesn't provide enough information to know that the scan can stop
early.

Given your comment that the correlation is accidental, it may be that
there's not too much point in worrying.  The planner is picking this
plan only because it notices the asymmetry in key ranges, and as soon
as some more rows get added with higher-numbered target_ids it will
shift to something else (probably a hash join).

			regards, tom lane