How to Optimize a Slow SQL Query in PostgreSQL

A SQL report that ran fine at 1k rows grinds to a halt at 50k because the query plan changes - a subquery that was fast becomes a per-row sequential scan. EXPLAIN ANALYZE tells you exactly what the planner chose, and a JOIN rewrite plus one index is usually all it takes to drop query time from 10s to under 2s.

Data Engineerpythonpostgresqlsql

Read the plan first with EXPLAIN ANALYZE

Before touching any SQL, run EXPLAIN ANALYZE to see what the query planner actually does:

EXPLAIN ANALYZE
SELECT c.category_name,
       EXTRACT(MONTH FROM s.sale_date) AS month,
       COUNT(*) AS num_sales,
       SUM(s.amount) AS total_revenue
FROM sales s, categories c
WHERE c.id = (SELECT category_id FROM sales WHERE id = s.id)
GROUP BY c.category_name, EXTRACT(MONTH FROM s.sale_date);

Look for Seq Scan on large tables and rows= estimates that are wildly off. A correlated subquery usually shows up as a SubPlan node that executes once per outer row - visible as a high actual loops= count next to a table scan.

Replace correlated subqueries with JOINs

A correlated subquery like WHERE col = (SELECT ... WHERE id = outer.id) forces tuple-at-a-time execution - the engine re-runs the inner query for every row in the outer table. Rewrite it as a JOIN and the planner can choose a hash join or merge join across the whole set at once:

# Before: correlated subquery (slow at scale)
query = """
    SELECT (SELECT category_name FROM categories WHERE id = s.category_id) AS category,
           EXTRACT(MONTH FROM s.sale_date) AS month,
           COUNT(*) AS num_sales,
           SUM(s.amount) AS total_revenue
    FROM sales s
    GROUP BY s.category_id, EXTRACT(MONTH FROM s.sale_date)
    ORDER BY total_revenue DESC;
"""

# After: JOIN (fast at scale)
query = """
    SELECT c.category_name AS category,
           EXTRACT(MONTH FROM s.sale_date) AS month,
           COUNT(*) AS num_sales,
           SUM(s.amount) AS total_revenue
    FROM sales s
    JOIN categories c ON c.id = s.category_id
    WHERE s.sale_date >= '2024-01-01' AND s.sale_date < '2025-01-01'
    GROUP BY c.category_name, EXTRACT(MONTH FROM s.sale_date)
    ORDER BY total_revenue DESC;
"""

Add indexes on the columns you filter and group by

A JOIN rewrite helps the planner - but if the tables have no indexes on category_id or sale_date, it still falls back to sequential scans. Create a composite index on the columns the query filters and groups by:

CREATE INDEX IF NOT EXISTS sales_cat_date_idx
    ON sales(category_id, sale_date);

ANALYZE sales;   -- update planner statistics immediately

Run EXPLAIN ANALYZE again. Seq Scan on sales should flip to Index Scan using sales_cat_date_idx and the execution time should drop proportionally.

What to look for in the output

Node	What it means	Action
`Seq Scan` on a large table	No usable index	Add an index on the filter/join column
`SubPlan` with `loops=N`	Correlated subquery, runs N times	Rewrite as a JOIN
`Hash Join`	Good - set-based, uses the index	No change needed
Rows estimate far off actual	Stale statistics	Run `ANALYZE <table>`

The combination - JOIN rewrite plus a covering index - routinely turns 10-second reports into sub-2-second ones on 50k-row tables without touching the database schema or application code beyond the query itself.

Want to try it hands-on? HeyDevJob gives you this exact setup in a live cloud workspace in your browser - edit it, run it, and see it work. Free, nothing to install.

Try it in a workspace →

What you'll practice

Running EXPLAIN ANALYZE and reading the execution plan
Rewriting correlated subqueries as JOINs for set-based execution
Creating composite indexes on filter and GROUP BY columns

FAQ

How do I find out why my SQL query is slow?

Run EXPLAIN ANALYZE before your query in psql. It shows the actual execution plan the planner chose, including whether it used a Seq Scan or Index Scan and how many times each node executed. Look for Seq Scan on large tables and SubPlan nodes with high loop counts.

What is a correlated subquery and why is it slow?

A correlated subquery references a column from the outer query (e.g. WHERE id = outer.id). The database re-runs it once per row of the outer table, so on 50k rows it executes 50k times. Rewriting it as a JOIN lets the planner hash or merge the sets in one pass.

What index should I add to speed up a GROUP BY query?

Create a composite index on the columns used in your WHERE filter and GROUP BY clause - for example, CREATE INDEX ON sales(category_id, sale_date). Run ANALYZE afterward so the planner picks up the new statistics. EXPLAIN ANALYZE should then show Index Scan instead of Seq Scan.

Keep learning

Make an ETL Pipeline IdempotentData project Make a CSV Importer Resilient to Bad DataData project Convert Timestamps to Local Time (Timezones)Data project Data roadmapStep by step to hired Data interview questionsSTAR answers All Data projectsProjects hub

Learn it by doing. Open this in a live cloud workspace, make the change yourself, and keep a record of the work you can share.

Open the workspace →