In some cases, we have SQL being submitted that has superfluous self-joins. An example would be
select count(*) from foo1 a, foo1 b where a.c1 = b.c1 /* PK join */ and a.c2 = 5 and b.c2 = 10; We can recognise that <a> and <b> are the same table because they are joined on the PK. PK is never NULL, so a join b == a in set terms. We can use this to re-write the query as if all predicates on either of the two aliases were on the LHS only. e.g. rewrite query like this: select count(*) from foo1 a, foo1 b where a.c1 = b.c1 and a.c2 = 5 and a.c2 = 10; /* predicate migration */ Predicate migration is important because it either allows us to detect impossible logic, as above, or to use multi-column index access/ bitmap scans, or to allow join removal of the RHS as a superfluous join. (I believe that self-joins were not originally part of the analysis of potentially removable joins). You may well ask who would be stupid enough to write SQL like that. The answer is of course that it is automatically generated by an ORM. Implementing something along these lines is secondary to join removal, but it seems worth noting as non-high priority item for the TODO. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
