SQL Query Performance: 5 Fixes That Actually Work

SQL Query Performance: 5 Fixes That Actually Work

Slow queries are the most common thing that makes a data engineer look bad in a meeting. Someone runs a dashboard, the spinner turns for 20 seconds, and the Slack message hits your DMs before the query finishes. This guide covers the five fixes that resolve 80% of production slowdowns—each with a concrete example you can run against your own warehouse.

In this guide
  1. Fix 1: Missing (or wrong) indexes
  2. Fix 2: SELECT * on wide tables
  3. Fix 3: Nested subqueries vs CTEs vs JOINs
  4. Fix 4: Implicit type casting in JOINs
  5. Fix 5: Unanchored JOINs (cartesian products)
  6. FAQ
Quick answer: If a query is slow and you can only check one thing, check the JOIN columns for indexes. A single missing index on a foreign-key column will turn an O(log n) lookup into a full table scan across millions of rows.

Fix 1: Missing (or wrong) indexes

An index is a sorted lookup structure. Without one on a JOIN or WHERE column, the database scans every row in the table. With one, it jumps directly to the matching rows.

Before (full table scan):

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01';

-- Seq Scan on orders  (cost=0.00..48500.00 rows=120000 width=40)
--   Filter: (order_date >= '2026-01-01')

After (indexed lookup):

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Index Scan using idx_orders_customer_date on orders
--   (cost=0.42..8500.00 rows=120000 width=40)

The index cut the cost from 48,500 to 8,500—roughly a 6x improvement, and the gap grows with table size. The compound index on (customer_id, order_date) serves both the JOIN and the WHERE in one structure.

Rule: Index every column used in JOINs and WHERE clauses on large tables. Use compound indexes when a query filters on multiple columns. But don’t index everything: each index adds write overhead. For a table that’s read 100x more than written, index freely. For a high-write table (event logs, clickstreams), be selective.

Fix 2: SELECT * on wide tables

SELECT * fetches every column, including large text fields, JSON blobs, and geo columns you don’t need. In a warehouse with columnar storage (BigQuery, Snowflake, Redshift), the cost difference is proportional: reading three columns is often 10–20x cheaper than reading forty.

Before:

SELECT *
FROM analytics_events
WHERE event_date = '2026-07-06';
-- Scanned: 42 GB (all 68 columns)

After:

SELECT event_id, event_type, user_id, event_timestamp
FROM analytics_events
WHERE event_date = '2026-07-06';
-- Scanned: 2.1 GB (4 columns)

The fix is mechanical: list the columns you actually need. If you’re building a dashboard query, only select the columns that appear in the chart. Your warehouse bill will thank you.

Fix 3: Nested subqueries vs CTEs vs JOINs

A subquery inside a WHERE clause often runs once per outer row, turning an O(n) scan into O(n²). A CTE or a JOIN gives the optimizer room to pick a better plan.

Before (correlated subquery — runs per row):

SELECT user_id, order_total
FROM orders o
WHERE order_total > (
  SELECT AVG(order_total)
  FROM orders
  WHERE user_id = o.user_id
);

After (CTE + JOIN — runs once):

WITH user_avg AS (
  SELECT user_id, AVG(order_total) AS avg_total
  FROM orders
  GROUP BY user_id
)
SELECT o.user_id, o.order_total
FROM orders o
JOIN user_avg u ON o.user_id = u.user_id
WHERE o.order_total > u.avg_total;

In modern optimisers (Postgres ≥ 12, recent MySQL), simple subqueries are often rewritten into JOINs automatically. But correlated subqueries—those referencing a column from the outer query—rarely get rewritten. The CTE pattern makes the intent explicit and nearly always beats the correlated version.

Fix 4: Implicit type casting in JOINs

When you JOIN on columns of different types—say a VARCHAR customer_id in one table and an INTEGER in another—the database silently casts one side for every comparison. This drops the index and triggers a full scan.

Before (implicit cast kills the index):

-- orders.customer_id is INTEGER, users.id is VARCHAR
SELECT *
FROM orders o
JOIN users u ON o.customer_id = u.id;
-- Seq Scan on users: implicit cast on every row

After (explicit cast, or fix the schema):

-- Option A: cast the indexed side (cheap, one-time)
SELECT *
FROM orders o
JOIN users u ON CAST(o.customer_id AS VARCHAR) = u.id;

-- Option B (better): fix the column type in the source table
ALTER TABLE users ALTER COLUMN id TYPE INTEGER USING id::INTEGER;

How to spot it: Look at your EXPLAIN output for rows where the database says “Filter” on a JOIN column instead of “Index Scan.” Check the column types in your schema with SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders';

Fix 5: Unanchored JOINs (cartesian products)

An unanchored JOIN—also called a cross join or cartesian product—mates every row in the left table with every row in the right. Two tables of 10,000 rows each produce 100 million intermediate rows. This is the fastest way to bring a warehouse to its knees.

Before (accidental cross join):

SELECT o.order_id, p.product_name
FROM orders o, products p
WHERE o.order_date = '2026-07-06';
-- 10,000 orders x 5,000 products = 50 million rows (then filtered)

After (anchored JOIN):

SELECT o.order_id, p.product_name
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date = '2026-07-06';
-- Each JOIN has a real condition; row count stays sane

The comma-style FROM a, b syntax is the most common culprit—it’s easy to forget the WHERE clause that anchors it. Prefer explicit JOIN ... ON syntax: it makes the anchor condition mandatory and obvious to anyone reading the query.

FAQ

How do I read an EXPLAIN plan?

Start from the innermost node and read outward. Each row is an operation: a Seq Scan reads the whole table (bad for large tables), an Index Scan uses an index (good), a Nested Loop JOINs rows one-by-one (fine for small sets, expensive for large), a Hash Join builds an in-memory hash table (good for medium-to-large). The cost=x..y numbers are arbitrary units: lower is faster. Focus on the ratio, not the absolute number.

Should I use a covering index?

A covering index includes all columns the query needs, so the database never touches the main table. For high-frequency dashboard queries that fetch the same 3–5 columns, a covering index can be 10–100x faster. The trade-off is more storage and slower writes. Use them for read-heavy, narrow queries on large tables.

When do indexes hurt performance?

Every INSERT, UPDATE, and DELETE on an indexed column must also update the index. On a table getting thousands of writes per second, too many indexes slow ingestion to a crawl. Profile your read/write ratio: if writes dominate, be conservative with indexes.

Does query optimization differ by warehouse?

Yes. Row-store databases (Postgres, MySQL) rely heavily on indexes. Column-store warehouses (BigQuery, Snowflake, Redshift) don’t use traditional indexes—they optimize through partitioning, clustering keys, and minimizing bytes scanned. The SELECT * fix is far more important on columnar warehouses than the index fixes, which mostly apply to row-store systems.

These five fixes cover the bulk of what makes a slow query slow. Internalize them, and the next Slack DM about a slow dashboard will have an answer in under two minutes. For a deeper look at how data flows through the systems you’re querying, start with the star schema guide and the modern data stack overview. For more advanced SQL patterns, the SQL window functions guide covers the next level.

Now a book: The Data Engineer's Blueprint The whole DataStack Daily series, rebuilt into one plain-English guide to the modern data stack — warehouses, pipelines, dbt, SQL, and dashboards. Paperback & Kindle. Get it on Amazon →

Keep reading: Star Schema Explained: Facts & Dimensions · SQL Window Functions: A Practical Guide · The Modern Data Stack Explained (Plain English)

Last updated: July 10, 2026

Comments

Post a Comment

Popular posts from this blog

The Modern Data Stack Explained (Plain English)

Image
The modern data stack is the set of cloud tools teams use to move data from where it’s created to where people can actually use it — dashboards, reports, and models. If you’re new to data and the jargon feels like alphabet soup, this guide is the map. We’ll walk each layer in plain English, follow one order all the way from a store database to a dashboard, and point you to where to start. In this guide What 'the modern data stack' actually means The five layers, from source to dashboard Ingestion: getting data in Storage: the warehouse or lakehouse Transformation: turning raw into useful BI and activation: where people see it How the pieces fit together (a worked example) Where to start if you're new FAQ Quick answer: The modern data stack is a set of specialized cloud tools — ingestion, storage, transformation, and BI — that together turn scattered raw data into trustworthy numbers people can query. Instead of one big monolith, each...
Read more

Data Warehouse vs Data Lake vs Lakehouse

Image
‘Data warehouse vs data lake’ trips up almost everyone new to the field, partly because people use the two words as if they mean the same thing. They don’t. A warehouse stores clean, structured tables ready for analysis; a lake stores raw files of any shape for later. This guide explains both (plus the newer lakehouse), who uses each, and how to pick — in plain English. In this guide The three terms, defined Structured vs unstructured: the core split The warehouse: fast, clean, pricey The lake: cheap, raw, flexible The lakehouse: the merge A comparison table Which one you actually need FAQ Quick answer: A data warehouse stores structured, cleaned data optimized for fast SQL analytics; a data lake stores raw data of any type cheaply for flexible, later use. A lakehouse combines the two — lake-style storage with warehouse-style querying layered on top. The three terms, defined The data warehouse vs data lake question is really about how mu...
Read more

ETL vs ELT: What's the Difference (and Which)?

Image
‘ETL vs ELT’ is one of the most common interview questions in data — and one of the most over-complicated. The difference comes down to a single letter’s worth of ordering: do you transform data before you load it, or after? This guide is for beginners who want the real distinction, the reason the default flipped, and a straight answer on which to choose. In this guide ETL and ELT in plain terms The one difference that matters: order of operations Why cloud warehouses changed the default Trade-offs side by side When ETL still wins When ELT is the better call What most teams do in 2026 FAQ Quick answer: ETL transforms data before loading it into the warehouse; ELT loads raw data first and transforms it inside the warehouse. Same three steps — extract, transform, load — in a different order, and today ELT is the more common default for cloud analytics. ETL and ELT in plain terms Both terms describe the same three jobs: Extract data f...
Read more