Database indexes and query plans

What it means

A query plan describes how the database intends to carry out a query. An index makes another access path available: for a condition it supports, the database may locate candidate rows through that structure rather than examine the table sequentially. Availability does not make that path worthwhile for every query.

The scene isolates one tradeoff: a fixed scan cost versus startup plus repeated per-match work. Its invented costs let you examine why a choice changes without pretending to reproduce PostgreSQL's optimizer or count the rows a real plan would examine.

Try it

Simulation: change Matching rows from 25 to 5000 and inspect the updated result and arithmetic. Then select Compare with baseline to display the baseline and current outcomes. Editing another field clears that comparison; Reset returns to the starting inputs.

The initial example and explanation remain readable without JavaScript. The controls require JavaScript and perform no external action.

Index path has lower modeled cost. Sequential: 10000 units. Index: 20 + 25 × 4 = 120 units. Selectivity is 0.25%. Only 25 per-match charges are included in the index expression, so its startup plus match cost is much smaller than the modeled full-table scan.

Keep Table rows = 10000, Index startup cost (illustrative units) = 20 and Cost per index match (illustrative units) = 4 unchanged. Only Matching rows changes.

Reset restores Table rows = 10000, Matching rows = 25, Index startup cost = 20 and Cost per index match = 4. It clears input errors and the baseline comparison. It preserves your independent choices to open or close FAQ answers and About this model.

How it works

The model separates work that depends on table size from work that depends on the number of matches:

• Sequential cost = Table rows × 1 illustrative unit.
• Index cost = Index startup cost + Matching rows × Cost per index match.

Startup is paid once in this expression. The per-match component grows with each additional supplied match. With table size held fixed, increasing Matching rows leaves the sequential cost unchanged but increases the index cost. Eventually, that repeated work can outweigh the scan cost under the chosen assumptions.

Selectivity means Matching rows divided by Table rows, expressed as a percentage. It describes how much of this synthetic table matches; it does not decide the result by itself. Changing startup or per-match cost changes the comparison even at the same selectivity. A real planner has more information and more possible paths than these two formulas represent.

Worked example

This is a synthetic baseline, not a captured database observation.

The supplied 25 matches represent 0.25% of the table. They contribute 25 × 4 = 100 units to the index expression; adding startup gives 20 + 100 = 120 units. The sequential expression charges one unit for each of 10000 table rows, totaling 10000 units. The index path therefore has lower modeled cost.

The important intermediate quantity is the per-match component. It explains how the result can reverse when more rows match, without changing the table size or inventing a timing measurement.

What changes when the condition changes

Change Matching rows to 5000 while keeping Table rows = 10000, startup = 20 and cost per match = 4.

Sequential path has lower modeled cost. Sequential: 10000 units. Index: 20 + 5000 × 4 = 20020 units. Selectivity is 50.00%.

The sequential expression stays unchanged because the table size has not changed. The index path's per-match component grows from 100 to 20000 units, making its total exceed the scan cost. This reversal follows from the supplied costs—not a universal rule that indexes stop helping at a particular percentage.

In practice

For PostgreSQL 18, begin in an approved test environment with permission to inspect the relevant database. Confirm the environment, query text, schema and relevant data distribution before requesting a plan. Keep the initial inspection scoped to plain EXPLAIN for one approved SELECT, without ANALYZE. Expect access-path names, estimated row counts and estimated costs—not measured runtime. Check that the displayed plan corresponds to the intended query and objects.

STOP if you cannot establish the environment, authorization or statement scope. EXPLAIN ANALYZE actually executes the statement; do not use it as a casual next step, especially for writes or an expensive workload. Execution observations require a separately assessed workload and safeguards.

This page does not direct you to create or drop an index, change planner settings or modify data. A lower synthetic cost does not justify such a change. Any later index proposal needs its own scope, expected benefit, write/storage assessment, verification and rollback plan.

Limits and unknowns

This is a local Simulation with no database connection or external request. It does not inspect your SQL, determine index eligibility, estimate your data distribution or measure execution. Matching rows is a supplied teaching input; it is not a report of rows or index entries actually examined. The fastest real path remains unknown from these calculations alone.

Equal costs are a valid outcome, not a forced winner. For example, Table rows = 10, Matching rows = 5, startup = 0 and cost per match = 2 gives 10 units for each path.

Invalid inputs are different from equal costs. Eleven matching rows cannot belong to this ten-row table example: the model reports that matching rows exceed the table, and no calculated outcome applies. Correct invalid values or use Reset rather than interpreting an earlier result as current.

What to carry forward

Treat an index as an available access path, not a promise that the planner will use it. The useful question is which work it avoids and which work it adds. For a real query, start with an authorized plan inspection; use the estimates to frame a performance question, not to declare a measured speedup.