Recap — The Query Performance Checklist

When a query is slow, run through this checklist before asking engineering.

1) Missing index on the filter or join column

SELECT * FROM orders WHERE customer_id = 12345;

If there's no index on customer_id, Postgres reads the entire table. For 10M rows, that's seconds. With an index, microseconds.

Check: \d orders (psql) or SHOW INDEX FROM orders; (MySQL). If customer_id isn't indexed and it's a common filter, you've found the bug.

Fix: CREATE INDEX orders_customer_id_idx ON orders(customer_id);

2) Function applied to indexed column in WHERE

SELECT * FROM orders WHERE DATE(created_at) = '2026-05-25';

DATE(created_at) cannot use the index on created_at — the function blocks it. The query falls back to Seq Scan.

Rewrite as a range:

SELECT * FROM orders
WHERE created_at >= '2026-05-25'
  AND created_at <  '2026-05-26';

Same logic, uses the index.

Other variants of this trap:

• WHERE UPPER(email) = 'X@Y.COM' → either lowercase the input, or use a functional index.
• WHERE CAST(user_id AS varchar) = '123' → just compare to the integer.
• WHERE order_date + INTERVAL '1 day' > NOW() → rearrange to WHERE order_date > NOW() - INTERVAL '1 day'.

3) SELECT * when you only need 3 columns

SELECT * FROM orders WHERE customer_id = 12345;

If the table has 40 columns and you need 3, you're moving 40 columns' worth of data over the wire. Worse, you can't use a covering index (an index that contains all needed columns and avoids a heap lookup).

SELECT order_id, total_amount, status FROM orders WHERE customer_id = 12345;

If you have CREATE INDEX orders_customer_idx ON orders(customer_id) INCLUDE (order_id, total_amount, status), this never touches the table — pure index read.

4) Joining before filtering

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

In many query plans, Postgres correctly applies the filter early. But for complex queries with many joins, it sometimes joins everything first, then filters. EXPLAIN tells you which.

If the planner is choosing badly, force early filtering with a CTE or subquery:

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2026-01-01'
)
SELECT c.name, o.total
FROM customers c
JOIN recent_orders o ON o.customer_id = c.id;

This is mostly a problem in older databases (MySQL pre-8) or with complicated views. PostgreSQL's planner usually does the right thing.

5) Stale statistics

After a big insert/update, the table's row counts and value distributions change. The planner's estimates become stale, and it picks the wrong join algorithm.

Fix: ANALYZE table_name; (PostgreSQL) or ANALYZE TABLE table_name; (MySQL). Run after big data changes; usually autovacuum handles it.

Bonus: ORDER BY + LIMIT without an index

SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Without an index on created_at, Postgres reads the whole table, sorts it, takes the top 10. With an index, it reads exactly 10 rows in order.

CREATE INDEX orders_created_at_idx ON orders(created_at DESC);

This is the highest-leverage "1 line" fix in BI workloads. Top-N queries are everywhere.

The 60-second triage

1. Is there an index on the WHERE column? (no → likely fix)
2. Is a function applied to the WHERE column? (yes → rewrite without)
3. Is SELECT * being used unnecessarily? (yes → name the columns)
4. Does EXPLAIN show wrong row estimates? (yes → ANALYZE)
5. Is there an ORDER BY + LIMIT without a matching index? (yes → add index)

If none of these apply, the query might genuinely be complex. Then it's time to think harder — or ask engineering.

Takeaway

90% of slow analyst queries fall into one of these five buckets. Develop the reflex: see a slow query, run through the list, fix the most likely cause. You'll outpace engineers who haven't built this muscle.