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 . Running dataset: 500k-row sim_bp_orders , 200k-row sim_bp_users , on Neon Postgres 17.8. We'll cover: scalar and existence subqueries ( SubPlan , EXISTS , IN ), when correlated subqueries should be rewritten as joins, how CTEs are executed on modern PostgreSQL, when to use MATERIALIZED vs NOT MATERIALIZED , LATERAL joins, and recursive CTEs.…