Performance: Indexes and Query Plans

Find the queries worth fixing

Enable pg_stat_statements (in postgresql.conf: shared_preload_libraries = 'pg_stat_statements', then restart and CREATE EXTENSION pg_stat_statements;).

SELECT round(total_exec_time) AS total_ms, calls,
       round(mean_exec_time, 1) AS mean_ms, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time: a 5 ms query run a million times costs more than a 2-second report run once a day.

Also log slow queries: log_min_duration_statement = 500 (milliseconds).

Reading a plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Limit  (actual time=180.2..180.3 rows=20)
  ->  Sort  (actual time=180.2..180.2 rows=20)
        ->  Seq Scan on orders  (actual time=0.03..175.9 rows=1850 loops=1)
              Filter: (customer_id = 42)
              Rows Removed by Filter: 1998150

A Seq Scan that removes two million rows to keep 1,850 is the clue. Add an index matching the filter and the sort:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

The new plan uses an Index Scan and finishes in under a millisecond. CONCURRENTLY builds the index without locking writes, which is essential in production.

Index types

CREATE UNIQUE INDEX ON users (lower(email));
CREATE INDEX ON access_requests (created_at) WHERE status = 'pending';

Column order in multi-column indexes

An index on (customer_id, created_at) helps queries filtering on customer_id, or on both. It does not help a query filtering only on created_at. Put equality columns first, then range or sort columns.

Indexes are not free

Each index slows down writes and uses disk. Find unused ones:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

The N+1 problem

The most common ORM performance bug: one query for a list, then one more query per row. Fifty projects means fifty-one queries. In SQLAlchemy, load related rows in one go with selectinload() or joinedload(). Look for it whenever a page's query count grows with its row count.

Check yourself

• Why sort pg_stat_statements by total time?
• What does CONCURRENTLY change when creating an index?
• Which index type fits a JSONB column?