Tom Lane <[EMAIL PROTECTED]> writes: > Simon Riggs <[EMAIL PROTECTED]> 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! -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
