PostgreSQL Chose a Sequential Scan: What to Check First

Article · Measurement

The endpoint asks for five orders. PostgreSQL reads thousands of rows and sorts them. An index exists, but the plan says Seq Scan.

That is enough reason to investigate. It is not enough reason to force an index. The existing index might find matching rows without supplying their order. A large customer might own most of the table. The estimate might even be right.

Let's follow one query through two plans. The Rust fixture uses SQLx 0.8.6 and PostgreSQL 18.6. It keeps the query, bound tenant and data fixed while adding an index that can supply the requested order.

Start with the query the application sends

The fixture asks for one tenant's newest unpaid orders:

const ORDERS: &str = "SELECT id FROM limit_plan_review.orders
    WHERE tenant_id = $1 AND status = 'unpaid'
    ORDER BY created_at DESC, id DESC LIMIT 5";

The ID breaks ties when two orders have the same timestamp. Without a complete ordering, two valid executions could disagree about which rows belong in the first five.

The tenant is a bound parameter. alpha is a fixed test value here. In an application, derive that scope from the authenticated request. An execution-plan example does not establish the application's authorization boundary.

Use the actual parameter types and representative values when investigating a service. A cast, predicate or sort direction can change the available plan. A query pasted into a ticket may differ from the one the driver sends.

The helper captures the plan through the same Rust connection:

async fn order_plan(connection: &mut PgConnection, tenant: &str) -> Result<Value> {
    let explain = format!("EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON) {ORDERS}");
    let Json(plan): Json<Value> = sqlx::query_scalar(&explain)
        .bind(tenant)
        .fetch_one(connection)
        .await?;
    Ok(plan)
}

EXPLAIN ANALYZE executes the statement. Here that means a bounded read in a disposable database. For a query that is expensive or changes data, first decide whether executing it is acceptable. Plain EXPLAIN shows estimates without running the statement.

SQLx's json feature enables the typed result used here. JSON makes the plan easy for the fixture to inspect. The PostgreSQL EXPLAIN guide describes the plan tree and its fields.

Follow the rows below LIMIT

The table contains 20,000 orders. Alpha owns 19,000; beta owns 1,000. An existing index covers (tenant_id, status). It does not supply created_at DESC, id DESC.

The fixture requires the baseline to contain a sequential scan and a sort. It checks the nodes rather than assuming that writing a query with LIMIT 5 made the database read five rows.

The recorded execution gives this comparison:

These counts belong to the synthetic dataset. They are not a production speedup or disk-I/O measurement.

Read the scan and its parent together. The sequential scan emits 19,000 matching rows and removes 1,000. With one loop, that accounts for all 20,000 table rows. The sort returns five rows to LIMIT, but it receives 19,000 candidates before it can choose them.

A small sort result is therefore not evidence of small input work. The index ordering documentation explains why a matching B-tree can change this particular pattern.

Actual Rows describes a node's output. With multiple loops, the displayed value is an average per execution. Keep the loop count and full tree with the result. A cropped line from the top of the plan cannot explain the work underneath it.

Check estimates before prescribing ANALYZE

Compare estimated and actual rows at the earliest node where they diverge. A large difference can make statistics worth investigating. A sequential scan by itself does not show that statistics are stale.

For this fixture, ANALYZE runs after the data is inserted. The baseline estimate of 19,000 qualifying rows matches the observed scan output. Most rows belong to alpha. Finding that tenant through the existing filter index still leaves many rows to read and sort. The experiment does not reproduce stale statistics, and it does not show that refreshing statistics repairs every slow query.

For a real table, inspect last_analyze, last_autoanalyze and n_mod_since_analyze in pg_stat_user_tables. Consider a targeted statistics refresh after a large change, then capture the same query again. Keep the old plan so you can distinguish an estimate improvement from a reduction in actual work.

Also read buffer counters carefully. A shared-buffer hit is not a disk read. A read into shared buffers can come from the operating system's cache. Parent counters include child work, so adding every node can count the same activity more than once. The EXPLAIN reference defines those counters.

Add the index that supplies the missing order

The candidate index includes the equality filters followed by the ordering columns:

sqlx::query(
    "CREATE INDEX orders_tenant_status_created_idx
     ON limit_plan_review.orders
        (tenant_id, status, created_at DESC, id DESC)",
)
.execute(&mut *tx)
.await?;

The fixture then runs the same bound query and captures another plan. It requires the named ordered index, no separate sort, and the same five IDs. Both comparisons happen in one Repeatable Read transaction over the unchanged fixture data. No planner method is disabled to manufacture the repair.

Matching the result matters. A faster query that dropped the tenant predicate, changed the sort or skipped valid orders would answer a different question.

The recorded node is called Index Only Scan, but it still reports five heap fetches for visibility checks. That name does not promise that no heap access happened.

This is an index to investigate for this query. It is not a general prescription for every orders table. An index occupies storage and adds work to writes. Check existing indexes and other common queries before keeping another one. The index write-cost investigation follows that tradeoff separately.

The ordinary CREATE INDEX above is used on an isolated fixture. Building an index on a busy service needs a deployment plan of its own. This experiment does not test that rollout.

Try the values your users actually send

Alpha owns almost the whole fixture table. A small tenant, an empty result or a different status can produce another plan. Repeat the investigation with representative inputs and the application's prepared-statement behavior. Keep the SQL, values, database version, settings and complete plans together.

If the query finishes quickly but the endpoint still waits, the Rust request investigation separates pool acquisition, database waits and response work. A plan cannot explain time before the query reaches PostgreSQL.

The useful conclusion is specific: the first plan had to sort many eligible rows to return five. The new index supplied their order and let this scan stop after five. That is evidence for this repair, with this query and data. It leaves the next investigation much clearer than treating Seq Scan as an error message.