From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mitchell Skinner <mitch@egcrc.net>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: same plan, add 1 condition, 1900x slower
Date: Fri, 11 Nov 2005 12:16:33 -0500
Message-ID: <19851.1131729393@sss.pgh.pa.us> (raw)
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>
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
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, mitch@egcrc.net, dev@archonet.com
Subject: Re: same plan, add 1 condition, 1900x slower
In-Reply-To: <19851.1131729393@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