Re: Predicate migration on complex self joins

On Mon, Jul 13, 2009 at 1:33 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> 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;
>
>> 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.
>
> Seems like the right answer is "fix the damn ORM".  It's hard to believe
> this sort of case comes up often enough to justify the cycles that would
> be expended (on *every* join query) to try to recognize it.

I think it's more common than you might think. It's been requested on
-performance within recent memory, and I've had cases where I needed
to deal with it as well. You can't write:

DELETE FROM table AS alias LEFT JOIN othertable ...

so you end up writing:

DELETE FROM table AS alias USING sametable LEFT JOIN othertable ...

or sometimes:

DELETE FROM table AS alias USING viewthatconstainstable ...

...Robert