Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 17
Cardinality, correlation, and adaptive evidence
Understand what histograms, column groups, sampling, partition statistics, and feedback can tell an optimizer.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
An optimizer substitutes summaries for reading all data during planning. Row counts, distinct values, null fractions, density, and histograms preserve selected properties of a distribution.
02
Frequency and bucketed histograms improve equality and range estimates when values are not uniform, but bind handling and endpoint representation determine whether that knowledge is usable.
03
Country and postal code, status and close date, or tenant and identifier often correlate. Extended statistics and Oracle column groups model facts that single-column summaries cannot.
04
Local statistics describe one partition; global statistics describe the whole object. Incremental maintenance and synopses avoid rescanning all historical data after loading one partition.
05
Dynamic sampling, cardinality feedback, statistics collectors, and advisors fill gaps at parse or after execution. They have memory, persistence, and version-specific behavior.
06
Pending statistics, history, restore points, and representative tests make statistics changes reversible. A gather job is a production change because it can change every dependent plan.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
pg_stats reveals the lossy model used for one column. Compare MCV frequency, histogram coverage, null fraction, and correlation with the predicate that was misestimated.
SELECT attname, null_frac, n_distinct, most_common_vals,
most_common_freqs, histogram_bounds, correlation
FROM pg_stats WHERE schemaname='public' AND tablename='orders';PostgreSQL extended statistics can record dependencies, multivariate distinct counts, and multicolumn most-common values. ANALYZE populates the object after creation.
CREATE STATISTICS orders_tenant_status
(dependencies, ndistinct, mcv)
ON tenant_id, status FROM orders;
ANALYZE orders;Pending statistics allow gathering and session-scoped testing before publication. Keep history retention sufficient to restore a known set.
EXEC dbms_stats.set_table_prefs(USER,'ORDERS','PUBLISH','FALSE');
EXEC dbms_stats.gather_table_stats(USER,'ORDERS');
ALTER SESSION SET optimizer_use_pending_statistics=TRUE;
-- Test, then DBMS_STATS.PUBLISH_PENDING_STATS.Runtime row-source statistics turn a vague bad plan into a cardinality problem at a specific operation. Gather them selectively because instrumentation has cost.
SELECT * FROM TABLE(dbms_xplan.display_cursor(
sql_id => :sql_id, cursor_child_no => :child_no,
format => 'ALLSTATS LAST +PREDICATE +PEEKED_BINDS'));08
Optional deep dives with the complete experiments and product-version context behind this guide.