Re: Slow performance with trivial self-joins

Benny Kramek <benny@medflyt.com> writes:
> I expect the query plan to be identical for both of the below queries (and I
> expect the performance to also be identical).

[ shrug... ] Your expectation is mistaken.  There is no code in Postgres
to eliminate useless self-joins.  People have been fooling around with
a patch to do so [1], but I'm unsure whether it'll ever get committed,
or whether we even want the feature.  It seems not unlikely that the
time wasted trying to identify useless self-joins (in queries where
the optimization doesn't actually apply) would outweigh the win when
it does apply.  So there's a limit to how much effort the server should
spend trying to clean up after poorly-written queries.

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru