Predicate migration on complex self joins

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