Query cardinality estimation
Query cardinality estimation predicts the number of rows produced by a query or an intermediate query-plan operation.
PostgreSQL EXPLAIN rows describes a plan node's estimated output, not all tuples examined, heap fetches, bytes read or elapsed time. EXPLAIN ANALYZE executes the statement; for repeatedly executed nodes, actual rows are reported as a per-execution average, so inspect loops too. [1]
Statistics may be sampled or out of date. Estimates can also be inaccurate because a model misses relationships between columns. PostgreSQL extended statistics address supported cases; dependency statistics do not generally handle range predicates or incompatible constant combinations. They are not an exact-count guarantee. [2]
For a simple filter, estimated output is estimated input multiplied by estimated predicate selectivity. Distinct-value estimation answers another question: MySQL SHOW INDEX Cardinality is an estimate of distinct index values, not this query's output. [3] [4]
An estimate is not proof that an index is complete or that every stored payload byte was read and verified. Inspect the executed access path and the query's requested values separately. [5]
Relations
Citations
- PostgreSQL 18: Using EXPLAIN, output rows and repeated nodes
- PostgreSQL 18: Statistics Used by the Planner, dependency limitations
- PostgreSQL 18: Row Estimation Examples
- MySQL 8.4: SHOW INDEX, Cardinality estimates distinct values
- Hesela: estimated rows versus read coverage
Sources & caveats
- Primary-source synthesis scoped to PostgreSQL 18 and MySQL 8.4 documentation. The linked counting model uses constructed rows; it is not a PostgreSQL/MySQL planner run or a performance measurement.