Parameter · Query Tuning / Planner Method Configuration
enable_self_join_elimination
Enables removal of unique self-joins.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
Sometimes a query (often generated by an ORM or a view) joins a table to itself on its primary key, so each row just meets itself. New in PostgreSQL 18, the planner spots this and removes the pointless join. This switch turns that off.
For DBAs and operators
Self-join elimination (SJE) applies to an inner join of a table with itself where the join condition proves that both sides are the same row, i.e. equality on all columns of a unique index or primary key. The two references are merged into one scan, and their WHERE conditions are combined.
Measured on 18.6: SELECT t1.* FROM t t1 JOIN t t2 ON t1.id = t2.id WHERE t2.a = 5 planned a single Bitmap Heap Scan on t t2, cost 606. With enable_self_join_elimination = off: Nested Loop of a bitmap scan and a primary-key index scan, cost 2,133.
The feature was first committed for 17 and reverted, then shipped in 18. It works only with plain tables and inner joins, and it adds a little planning work. Row locking can block it: on 18.6, FOR UPDATE OF t2 kept the join, while a plain FOR UPDATE still removed it. If a query returns wrong results only on 18 and involves a self-join, turning this off is a quick way to rule out the optimization before filing a bug.
How to change it
Any user can change it for their session with SET.