Mastering PostgreSQL 12 - Third Edition

Join pruning

PostgreSQL also provides an optimization called join pruning. The idea is to remove joins if they are not needed by the query. This can come in handy if queries are generated by some middleware or some ORM. If a join can be removed, it naturally speeds things up dramatically and leads to less overhead.

The question now is, how does join pruning work? Here is an example:

CREATE TABLE x (id int, PRIMARY KEY (id));CREATE TABLE y (id int, PRIMARY KEY (id));

First of all, two tables are created. Make sure that both sides of the join condition are actually unique. Those constraints will be important in a minute.

Now, we can write a simple query:

test=# EXPLAIN SELECT * FROM  x LEFT JOIN y ON (x.id = y.id) WHERE x.id = 3; QUERY PLAN ...