On 31 January 2017 at 10:43, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> I don't think that's possible. The whole point that the current join >> removal code retries to remove joins which it already tried to remove, >> after a successful removal is exactly because it is possible for a >> join to become provability unique on the removal of another join. > > Not seeing that ... example please?
I had a quick look at this again as it had been a while since I noticed that. The sample case was: create temp table uniquetbl (f1 text unique); explain (costs off) select t1.* from uniquetbl as t1 left join (select *, '***'::text as d1 from uniquetbl) t2 on t1.f1 = t2.f1 left join uniquetbl t3 on t2.d1 = t3.f1; However, what it actually fails on depends on if you check for unused columns or uniqueness first as initially the subquery fails both of the tests. I was under the impression it was failing the unique test, as that's what I was doing first in my patch. If you test uniqueness first it'll fail on: /* * If such a clause actually references the inner rel then join * removal has to be disallowed. We have to check this despite * the previous attr_needed checks because of the possibility of * pushed-down clauses referencing the rel. */ if (bms_is_member(innerrelid, restrictinfo->clause_relids)) return false; but if you test for unused columns first, it'll fail on: if (bms_is_subset(phinfo->ph_eval_at, innerrel->relids)) return false; /* there isn't any other place to eval PHV */ -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers