Re: Self-join optimisation

On 3/11/21 3:39 PM, Hywel Carver wrote:
> Great! It looks like it's been in commitfest status for a few years. Is
> there anything someone like me (outside the pgsql-hackers community) can
> do to help it get reviewed this time around?
>

Well, you could do a review, or at least test it with the queries your
application is actually running. And explain why your application is
doing queries like this, and why it can't be changed to changed to not
generate such queries.

The first couple of messages from the patch thread [1] (particularly the
messages from May 2018) are a good explanation why patches like this are
tricky to get through.

The basic assumption is that such queries are a bit silly, and it'd be
probably easier to modify the application not to generate them instead
of undoing the harm in the database planner. The problem is this makes
the planner more expensive for everyone, including people who carefully
write "good" queries.

And we don't want to do that, so we need to find a way to make this
optimization very cheap (essentially "free" if not applicable), but
that's difficult because there may be cases where the self-joins are
intentional, and undoing them would make the query slower. And doing
good decision requires enough information, but this decision needs to
happen quite early in the planning, when we have very little info.

So while it seems like a simple optimization, it's actually quite tricky
to get right.

(Of course, there are cases where you may get such queries even if you
try writing good SQL, say when joining views etc.)

regards

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

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company