Every SELECT in PostgreSQL is made of smaller SELECT s, even when it doesn't look that way. WHERE col IN (SELECT ...) , WHERE EXISTS (SELECT ...) , (SELECT count(*) FROM ... WHERE ...) in the column list, WITH x AS (SELECT ...) — these look syntactically different but all get rewritten into plan nodes at plan time. Which plan node the planner chooses determines whether your query runs in three milliseconds or three seconds, and the rules are different for each pattern.

This is part of the Complete Guide to PostgreSQL SQL Query Analysis & Optimization . Assumes you can read EXPLAIN output and are familiar with how the planner chooses join strategies .…