On Mon, Jul 13, 2009 at 1:33 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.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