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