Recap — Choosing the Right Pattern

# Choosing the Right Pattern You have an intermediate result. You need to use it in the final query. Three ways to organise this: ## Option 1 — Subquery (inline) ```sql SELECT * FROM ( SELECT user_id, MAX(login_time) AS last_login FROM logins GROUP BY user_id ) recent WHERE recent.last_login < CURRENT_DATE - INTERVAL '30 days'; ``` Inline subqueries are evaluated once (per outer reference). For most engines, this is the simplest and fastest pattern when used once. ## Option 2 — CTE (Common Table Expression) ```sql WITH recent AS ( SELECT user_id, MAX(login_time) AS last_login FROM logins GROUP BY user_id ) SELECT * FROM recent WHERE last_login < CURRENT_DATE - INTERVAL '30 days'; ``` CTEs are clearer to read when you have multiple intermediate steps. Performance-wise: - **PostgreSQL ≥12** — CTEs are inlined like subqueries by default. Use freely. - **PostgreSQL <12** — CTEs were an *optimization barrier*: forced materialization, sometimes slower than subqueries. Avoid in legacy systems. - **SQL Server, MySQL 8+** — CTEs are inlined. Performance equivalent to subqueries. CTEs become slower than subqueries when used multiple times: the engine re-runs them. If you reference the same CTE 3 times in the outer query, it executes 3 times. ## Option 3 — Temp table ```sql CREATE TEMP TABLE recent AS SELECT user_id, MAX(login_time) AS last_login FROM logins GROUP BY user_id; CREATE INDEX ON recent(user_id); SELECT * FROM recent WHERE last_login < CURRENT_DATE - INTERVAL '30 days'; ``` Temp tables materialize the intermediate result once and let you **index it**. Use when: - You need the same intermediate in multiple downstream queries (saves recomputing). - The intermediate is large and you benefit from indexing for joins/filters. - You're building a multi-step analysis script. For one-shot queries, temp tables are overkill — the cost of creating/destroying the table dwarfs the benefit. ## Decision tree ``` Will I use this intermediate ONCE in this query? ├─ Yes → Subquery (or CTE for readability) └─ No ├─ Used 2-3 times in same query → CTE (modern engines) or subquery └─ Used across multiple queries / very large └─ Temp table with index ``` ## Recursive CTEs — the exception If you need recursion (org charts, category trees), you must use a recursive CTE. Subqueries don't support recursion. Temp tables would need a loop in application code. ## Materialized CTE (Postgres ≥12) If you specifically want a CTE to *not* be inlined (force materialization), use `WITH ... AS MATERIALIZED`: ```sql WITH recent AS MATERIALIZED ( SELECT user_id, MAX(login_time) AS last_login FROM logins GROUP BY user_id ) SELECT ... ``` Useful when: - The CTE is expensive to compute and you reference it many times. - The planner is making bad choices and you want to control execution. `WITH ... AS NOT MATERIALIZED` forces inlining (rarely needed; default in PG 12+). ## What about views? Views are essentially saved subqueries. They expand at query time. Performance: same as subqueries. Materialized views are the persisted-table equivalent. Use when: - The underlying query is expensive. - You can tolerate stale data (refresh on schedule). - Read frequency >> refresh frequency. For DA dashboards refreshed once a day, materialized views are often the right call. ## Common mistakes 1. **Using CTEs for everything** because they're "clean" — fine in PG12+, but be aware of the legacy materialization barrier. 2. **Recomputing the same CTE 5 times in one query** — switch to a temp table if the CTE is expensive. 3. **Creating a temp table for a one-shot query** — overkill. Use a subquery. 4. **Using views to "save complexity"** — fine, but understand the view re-runs every time it's referenced. ## Takeaway CTE for readability and the modern default. Subquery for one-off intermediates. Temp table when you need an index on the intermediate or use it across multiple queries. Materialized view when the source query is expensive and stale data is acceptable.