Re: Removing unneeded self joins

From: Alexander Korotkov <aekorotkov(at)gmail(dot)com>
To: Alena Rybakina <a(dot)rybakina(at)postgrespro(dot)ru>
Cc: Andrei Lepikhov <lepihov(at)gmail(dot)com>, Richard Guo <guofenglinux(at)gmail(dot)com>, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Alexander Lakhin <exclusion(at)gmail(dot)com>, "Gregory Stark (as CFM)" <stark(dot)cfm(at)gmail(dot)com>, Michał Kłeczek <michal(at)kleczek(dot)org>, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Subject: Re: Removing unneeded self joins
Date: 2025-04-26 20:04:22
Message-ID: CAPpHfdtGBX+oATYRoMyxcyQeuKCeYT3J3kDs_MA9AwberJrUfA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi, Alena!

On Sun, Apr 6, 2025 at 12:02 AM Alena Rybakina
<a(dot)rybakina(at)postgrespro(dot)ru> wrote:
> Should we add more regression tests covering these cases?
>
> I experimented with some examples like this and noticed that it does affect cardinality estimation, though I'm not sure the impact is significant.
> I used the tables from the regression tests, so if they’re appropriate for reproducing this case, it should be straightforward to add them.

Thank you for your feedback. I've check the cases you've provided. I
found that the differences here are related to the SJE itself, not to
changes regarding PHVs handling. I think it generally OK that
estimates are somewhat changed due to such significant query
transformation. Hopefully they should be improved in the majority of
cases.

I did some improvements to PHVs patch: revised comments and commit
message. I'm going to push it if no objections.

------
Regards,
Alexander Korotkov
Supabase

Attachment Content-Type Size
v2-0001-Disallow-removing-placeholders-during-Self-Join-E.patch application/octet-stream 6.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2025-04-26 20:05:27 Re: Removing unneeded self joins
Previous Message Sami Imseih 2025-04-26 18:57:27 Re: [BUG] temporary file usage report with extended protocol and unnamed portals