From: Tom Lane <tgl@sss.pgh.pa.us>
To: Benny Kramek <benny@medflyt.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow performance with trivial self-joins
Date: Mon, 03 Feb 2020 16:10:20 -0500
Message-ID: <28148.1580764220@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGPCyEZWEVjkDtuHipOaPKbO2WXdLUuNBhj818-wEuhi4KLbzA@mail.gmail.com>
References: <CAGPCyEZWEVjkDtuHipOaPKbO2WXdLUuNBhj818-wEuhi4KLbzA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, benny@medflyt.com, pgsql-performance@lists.postgresql.org
Subject: Re: Slow performance with trivial self-joins
In-Reply-To: <28148.1580764220@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox