PostgreSQL 10 High Performance - Third Edition

Join removal

Starting from PostgreSQL 9.0, a new feature will remove joins in circumstances where they aren't really required. The following JOIN would be common to many queries running against this data that wanted to match up a product with its matching inventory data:

SELECT * FROM products LEFT JOIN inventory ON products.prod_id=inventory.prod_id;  

You could even put this into a view as a shorthand for the combined result, then filter it down from there, so that not everyone has to remember how to do the join. But, some queries using that general form might not even need to use the inventory data, such as this one that only references products fields:

EXPLAIN ANALYZE SELECT products.title FROM products LEFT JOIN inventory ON products.prod_id=inventory.prod_id; ...