You are rarely the only thing writing your SQL. Your ORM writes some of it, your nested views write more, and sooner or later one of them joins a table to itself on its own primary key. That join returns exactly the rows it started with. enable_self_join_elimination is the PostgreSQL 18 optimization that notices and deletes it.
Default on, context user, brand new in 18. You can turn it off per session or per query, which is the only context in which you will ever want to.
The optimization is narrow and provable. When a plain table is inner-joined to itself and the join is an equality on a column the planner knows is unique, two things hold for every outer row: at most one inner row can match, and that one matching row is physically the same row. The join therefore cannot add, remove, or duplicate anything, so the planner discards it and scans the table once. If the unique column is the primary key, the join vanishes outright. If it is instead a nullable UNIQUE constraint, the surviving scan picks up a Filter: (c1 IS NOT NULL), because NULLs never satisfy the join and have to be excluded to keep the answer identical.
Here is what that looks like. A self-join on a million-row table that PostgreSQL 17 runs as a hash join over two sequential scans:
1 Hash Join 2 Hash Cond: (t1.c1 = t1bis.c1) 3 -> Seq Scan on t1 4 -> Hash 5 -> Seq Scan on t1 t1bis
becomes, in 18 with c1 a primary key, this:
1 Seq Scan on t1 t1bis
The join is gone. In Dalibo’s measurement, execution drops from roughly 530 ms to 185 ms, and planning gets cheaper as well, because a shorter range table is less work to plan. When it fires it is close to free, and you did nothing to earn it but upgrade.
Which brings us to why there is an off switch. This optimization was more than seven years in the making and does not have a spotless record. The idea dates to 2018; an earlier version landed during the PostgreSQL 17 cycle as enable_self_join_removal, then got reverted after Tom Lane flagged problems, with an explicit plan to re-commit early in 18. It returned in 18 under the current name. And since 18 shipped, a correctness fix in its row-marking handling (the machinery behind SELECT ... FOR UPDATE and its relatives) has already been backpatched to the stable branch. This is among the newest members of the enable_* family and the most invasive: a transformation that rewrites the query tree, not a scan type the planner is merely told to avoid.
None of which is a reason to turn it off. It is a reason to know where the switch is. Leave enable_self_join_elimination = on; the win is real and almost entirely invisible. But this is the one planner knob on 18 where off is a legitimate first move during an incident. If you are staring at wrong results or a planner anomaly on a query that joins a table to itself, especially one holding row locks, set it off for that session and see whether the problem leaves with the transformation. And keep your minor version current, because for this feature the fixes are still arriving.