CatchCase

Speed up a slow SQL query with EXPLAIN

Paste a slow PostgreSQL or MySQL query and its EXPLAIN ANALYZE output. Get the bottlenecks explained, the indexes to add and a faster rewrite.

How to use it

  1. Get the query execution plan Run `EXPLAIN (ANALYZE, BUFFERS)` on your slow query to capture timings and scan methods.
  2. Paste the query, schema and plan Fill in the form with your SQL, the table definitions and the EXPLAIN output, then run the case.
  3. Apply indexes and the rewritten query Create the recommended indexes in a development database, run the new query and compare execution times.

The prompt

You are a Senior Database Administrator specializing in PostgreSQL query optimization. I have a slow database query. Please analyze it, explain the bottlenecks from the EXPLAIN output, and write the optimized SQL alongside DDL commands for new indexes. Table Schemas: [TABLE_SCHEMAS] Slow Query: [SLOW_QUERY] EXPLAIN ANALYZE Output: [EXPLAIN_OUTPUT] Format your output into 3 parts: 1. Analysis of bottlenecks (Sequential scans, temp file usage, etc.) 2. Recommended index creation SQL 3. Optimized/Rewritten query SQL

Example result

1. Where the time goes

  • Sequential scan of the whole orders table (≈2.1 s of 2.8 s). There is no index on status or created_at, so Postgres reads all ~4.2 million rows and throws away 4.0 million of them (Rows Removed by Filter: 1338760 × 3 workers) to find ~184,000 shipped orders from the last 30 days.
  • Sorting on disk. It then sorts all ~184,000 matches by created_at to return 50. The sort doesn't fit in work_mem and spills 51 MB to disk (external merge Disk: 51832kB).
  • Hashing every customer. The hash join builds a hash table of all 180,000 customers, once per worker, although only 50 orders are returned.

The root cause is the first point: without an index that matches both the filter and the sort order, Postgres has to read and sort everything before it can stop at 50 rows.

2. Index to create

-- Matches the filter (status) and the sort (created_at DESC), so Postgres can read
-- the newest shipped orders in order and stop after 50 rows.
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
    ON orders (status, created_at DESC);

ANALYZE orders;

CONCURRENTLY builds the index without locking writes; it takes longer and can't run inside a transaction. customers.id is already the primary key, so the join needs no new index.

If shipped orders are queried far more than other statuses, a smaller partial index is an alternative:

CREATE INDEX CONCURRENTLY idx_orders_shipped_created_at
    ON orders (created_at DESC)
    WHERE status = 'shipped';

3. Rewritten query

-- Take the 50 newest shipped orders first (index-ordered, stops early),
-- then join only those 50 rows to customers.
WITH recent_shipped AS (
    SELECT id, customer_id, total_cents, created_at
    FROM orders
    WHERE status = 'shipped'
      AND created_at >= now() - interval '30 days'
    ORDER BY created_at DESC
    LIMIT 50
)
SELECT r.id, r.total_cents, r.created_at, c.name, c.email
FROM recent_shipped r
JOIN customers c ON c.id = r.customer_id
ORDER BY r.created_at DESC;

With the index, the expected plan is an Index Scan on idx_orders_status_created_at reading about 50 rows, followed by a Nested Loop with 50 primary-key lookups on customers: typically a few milliseconds instead of 2.8 seconds, and no disk sort. Run EXPLAIN (ANALYZE, BUFFERS) again after creating the index to confirm.

Related cases