Reading EXPLAIN Output From Rust Queries and Adding Indexes

When an endpoint is slow, the question to answer first is whether the query is slow, and then why. PostgreSQL will tell you if you ask it the right way. This post follows one slow query from the Rust code to a fix. Versions: Rust 1.90 with edition 2024, SQLx 0.8, PostgreSQL 17. The plans below are illustrative of the shapes you will see; your numbers will differ.

Start from the query your code sends

let orders = sqlx::query_as::<_, Order>(
    "SELECT id, customer_id, total_cents, created_at
       FROM orders
      WHERE customer_id = $1 AND status = 'paid'
      ORDER BY created_at DESC
      LIMIT 20",
)
.bind(customer_id)
.fetch_all(&pool)
.await?;

Copy the SQL to psql with a real parameter value. Use a database with production-like volume: an empty dev table with ten rows will choose a different plan. To find which queries to examine, enable pg_stat_statements and sort by total time, or log statements slower than a threshold with log_min_duration_statement (profiling a slow endpoint).

Run EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_cents, created_at
  FROM orders
 WHERE customer_id = 4711 AND status = 'paid'
 ORDER BY created_at DESC
 LIMIT 20;

Limit  (cost=210450.12..210450.17 rows=20 width=28) (actual time=612.4..612.4 rows=20 loops=1)
  Buffers: shared hit=3210 read=88112
  ->  Sort  (cost=210450.12..210452.65 rows=1012 width=28) (actual time=612.4..612.4 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (cost=0.00..210420.00 rows=1012 width=28) (actual time=0.4..611.9 rows=974 loops=1)
              Filter: ((customer_id = 4711) AND (status = 'paid'::text))
              Rows Removed by Filter: 9999026
Planning Time: 0.2 ms
Execution Time: 612.5 ms

How to read it

• Read from the innermost line outward. The deepest node (Seq Scan) produces rows, the parent (Sort) consumes them.
• Seq Scan with Rows Removed by Filter: 9999026: the database read ten million rows to return 974. That is the problem.
• actual time versus cost: cost is the planner's unit estimate; actual time is milliseconds. Compare shapes, not the two scales.
• rows=1012 estimated versus rows=974 actual: close, so statistics are fine. A gap of 100x signals stale stats or correlated columns.
• Buffers: read=88112: pages fetched from disk (or the OS cache); large numbers mean heavy I/O.
• The sort is cheap here because LIMIT 20 allows a top-N heapsort; a sort of millions of rows spilling to disk would show Sort Method: external merge.

Add the index that matches the query

CREATE INDEX CONCURRENTLY orders_customer_paid_created_idx
    ON orders (customer_id, created_at DESC)
    WHERE status = 'paid';

The index follows the query's shape: equality on customer_id first, then created_at DESC so rows come out already sorted, and a partial predicate on status = 'paid' so only relevant rows are indexed, which keeps it small. CONCURRENTLY avoids blocking writes on a live table (zero downtime migrations). Run ANALYZE orders; and re-explain:

Limit  (actual time=0.05..0.08 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_paid_created_idx on orders  (actual time=0.05..0.07 rows=20 loops=1)
        Index Cond: (customer_id = 4711)
Execution Time: 0.1 ms

No sort node, no filter, 24 buffers: the index delivers 20 rows in order and the scan stops after reading them. The reduction from hundreds of milliseconds to a fraction of one is typical when an index fits the access path.

Rules of thumb for index design

Why an index might not be used

• The predicate does not match: WHERE date(created_at) = ... cannot use an index on created_at; rewrite as a range or add an expression index.
• Type mismatch: binding an i32 against a bigint column is fine, but text against a uuid column forces casts. Check with the compile-time checked query! macros.
• Low selectivity: if the filter matches 30 percent of the table, a sequential scan is cheaper.
• Stale statistics: run ANALYZE; autovacuum normally does it, but a bulk load needs a manual run.

The cost side

Each index is updated on every insert and update of the table and takes disk and memory. After a while check what is unused:

SELECT relname, indexrelname, idx_scan
  FROM pg_stat_user_indexes
 ORDER BY idx_scan ASC
 LIMIT 20;

Drop indexes with zero scans after a representative period, checking they are not needed for constraints. Fix the N+1 patterns first, since an index does not help a query you run a thousand times too often (N+1 queries).