Re: Extending Self-Join Elimination to partitioned tables

On 11/07/2026 01:44, Trương Hoàng Quân wrote:
> I'd appreciate any feedback. If anyone has objections, or can think of a
> query pattern where this would break (wrong results, crashes, planner
> assertions), please let me know. In the meantime I'll keep expanding the
> test coverage for the corner cases I can think of.

Your patch is a big deal for partitioned configurations. But it still has some
issues. For example, one direct bug:

create table sjo (id int primary key, note text) partition by range (id);
create table sjo_1 partition of sjo for values from (0) to (100);
insert into sjo values (1,'a'), (2,'b');
select p.id from ONLY sjo p, sjo q where p.id = q.id;

This query shouldn't return anything, but I see the output:

id
----
1
2
(2 rows)

After fixing this, I think you should add a regression test that checks correctness.

Next, in config.sgml for enable_self_join_elimination I see the following:
"Takes into consideration only plain tables." — must be updated.

Further in the code, I see a stale comment:
"We only consider ordinary relations as candidates ..."

Performance evidence. It would be nice to see at least a couple of benchmarks:
1. Overhead in case of unsuccessful SJE operation (no unique index present)
2. How much faster (or slower) planning is if SJE has happened. We know it
should be a clear profit for the executor, but what does it cost for the planner?

--
regards, Andrei Lepikhov,
pgEdge