Optimising queries with EXPLAIN

In short: A slow database query can almost always be explained: the database reads too many rows. With EXPLAIN you see how it proceeds and can intervene specifically.

Reading EXPLAIN

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY date DESC LIMIT 10;

What matters is the type of access:

• Seq scan (full table scan): the whole table is read. Slow for large tables.
• Index scan / index only scan: an index leads directly to the rows, see SQL index.
• Rows estimated and actual, time and sorts. Large differences between estimate and reality point to outdated statistics (ANALYZE).

Common measures

1. Create a suitable index, here CREATE INDEX ON orders (customer_id, date DESC);. For multi-column indexes the order counts.
2. Fetch only needed columns instead of SELECT *.
3. Avoid functions on the column: WHERE lower(email) = ... does not use a normal index (function indexes exist for that).
4. LIMIT and pagination instead of loading everything; for large page numbers “after the last value” (keyset) is better than OFFSET.
5. N+1 problem: instead of sending a query per row, fetch at once with a JOIN or IN (...).
6. Too many indexes slow down writing and cost space.

Procedure

First measure (log slow queries, EXPLAIN ANALYZE), then change, then measure again. Check with test data of production size: with 100 rows all plans are fast.

See also: Database transactions and ACID, Caching strategies