EXPLAIN: Asking the Database Why Your Query Is Slow

Quick Answer

Put EXPLAIN before your query and the database reports its execution plan. The key thing to look for is whether it scans the whole table or seeks through an index, and why.

Scan versus seek, shown directly

On a table of 50,000 students, filtering by stream with no index:

EXPLAIN QUERY PLAN
SELECT * FROM student WHERE stream = 'Science';

-- SCAN student

SCAN means the database is reading every row and testing each one. On 50,000 rows that is tolerable; on 50 million it is not.

Add an index and run exactly the same query:

CREATE INDEX idx_stream ON student(stream);

-- SEARCH student USING INDEX idx_stream (stream=?)

Now it seeks through the index rather than reading everything. Same query, same data, different plan — and that difference is the entire subject.

Other databases word it differently: PostgreSQL says Seq Scan and Index Scan, MySQL shows a type column where ALL means a full scan and ref or const means index use. The vocabulary differs; the distinction is the same.

Queries that silently defeat an index

The index exists, the query looks reasonable, and the plan still says scan. Two causes account for most cases, and both can be demonstrated.

A function around the column. With an index on name:

EXPLAIN QUERY PLAN
SELECT * FROM student WHERE UPPER(name) = 'S5';
-- SCAN student            <-- index not used

SELECT * FROM student WHERE name = 's5';
-- SEARCH student USING INDEX idx_name (name=?)

The index stores names, not uppercased names, so it cannot help. Rewrite to avoid wrapping the column, or create a functional index on the expression if your database supports it.

A leading wildcard.

SELECT * FROM student WHERE name LIKE '%5';
-- SCAN student

An index is sorted, so it can find everything starting with a prefix. It cannot help when you do not know the beginning. LIKE 'abc%' uses the index; LIKE '%abc' cannot.

The same reasoning covers date functions — WHERE YEAR(created_at) = 2026 scans, while a range comparison on created_at seeks.

Reading a fuller plan

Real plans are trees, read from the innermost or most-indented outward — that is execution order, not reading order.

What to look for, roughly in priority:

• Full scans on large tables. Fine on a small lookup table, a problem on a large one.
• The row estimate versus reality. PostgreSQL's EXPLAIN ANALYZE shows both estimated and actual rows. A large mismatch means the statistics are stale, and the optimiser is choosing based on wrong information. Updating statistics can fix a slow query with no other change.
• Join order and type. A nested loop over a large unindexed table is a common cause of a query that was fast in development and slow in production.
• Sorts and temporary tables. MySQL's Using filesort and Using temporary often indicate an ORDER BY or GROUP BY that an index could satisfy directly.

EXPLAIN alone only plans; EXPLAIN ANALYZE actually runs the query and reports real timings. Use ANALYZE when you can — but not on an UPDATE or DELETE in production, because it executes them.

A workflow for a slow query

1. Reproduce with realistic data. A query over 100 rows is fast whatever the plan. Performance problems only appear at scale, which is why they surface in production.
2. Run EXPLAIN and find the largest scan.
3. Check the WHERE and JOIN columns are indexed and that nothing wraps them in a function.
4. Check you are not selecting more than you need. SELECT * on a wide table transfers columns you discard, and prevents index-only scans.
5. Re-run EXPLAIN and confirm the plan changed. This step is the one people skip — adding an index and assuming it helped is how databases accumulate unused indexes that slow every write.

Confirming the plan changed is the whole reason to use EXPLAIN rather than guessing, and it takes seconds.

Things worth knowing

• The optimiser can be right to ignore an index. If a query matches most of the table, scanning is genuinely cheaper than seeking the index and then fetching each row. An unused index is not automatically a bug.
• Statistics drive the decision. After a large data change, run ANALYZE so the optimiser's estimates reflect reality.
• Plans differ between environments. A plan from your laptop's small dataset tells you little about production.
• Covering indexes — an index containing every column a query needs lets the database answer without touching the table at all, which is the biggest single win available for hot read queries.

See database indexing explained for choosing which indexes to create in the first place.

Frequently Asked Questions