To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Simon Riggs <simon@2ndQuadrant.com>,
	Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>,
	List pgsql-patches <pgsql-patches@postgresql.org>
Subject: Re: WIP Join Removal
In-Reply-To: <14377.1220369736@sss.pgh.pa.us> (Tom Lane's message of "Tue\,
	02 Sep 2008 11\:35\:36 -0400")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <1220176372.4371.118.camel@ebony.2ndQuadrant>
	<48BC4127.30706@enterprisedb.com>
	<1220348487.4371.307.camel@ebony.2ndQuadrant>
	<14377.1220369736@sss.pgh.pa.us>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Tue, 02 Sep 2008 17:03:15 +0100
Message-ID: <87k5dusfgc.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On Mon, 2008-09-01 at 22:23 +0300, Heikki Linnakangas wrote:
>>> Did plan invalidation make it safe to rely on the presence of a unique 
>>> index for planning decisions?
>
>> My understanding was "Yes" and this case was the specific reason I
>> originally wanted to pursue plan invalidation back in 2006.

It may be worth considering what other cases might need this info and taking
them into account to be sure the solution is usable for them too. I suspect
we'll probably need a generic function for determining whether a PathKey list
can be proved unique.

Other cases off the top of three other cases where this could be useful -- but
generally anywhere the planner introduces a Unique node could benefit from
looking at this.

a) Turn a UNION into UNION ALL if there are unique indexes for any column in each
side and at least one column is a constant in each side and none of the
constants are equal.


b) Remove the aggregate on IN subqueries when there's a unique constraint so
that:

  SELECT * from a where a.fk IN (select pk FROM b)

Can do a semijoin without taking care to avoid duplicating records in "a" if
there should be duplicate values of "pk" in "b".


c) Turn bad mysqlish queries which are really semijoins (used to work around
their historic lack of subqueries) such as:

 SELECT DISTINCT a.pk FROM a JOIN b USING (x)

into

 SELECT a.pk FROM a WHERE x IN (SELECT x FROM b)


-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!