Measurement records

One schema for every number the benchmark harnesses report. A harness writes records (schema.py), check.py refuses a set that cannot be compared, report.py generates the tables from the records that pass. No figure of a document, a CHANGELOG line or a hand-off is typed: it comes from a record set and cites its run_id.

python3 -I bench/steps/records/check.py FILE...        one line for each problem, exit 1
python3 -I bench/steps/records/report.py [--tsv] FILE...   the tables of a set that passes

Who writes records

bench/bench cases --records FILE     every query case on the arms it can answer with: the default plan, a forced heap scan, the sift access method alone, the b-tree twin of a
                                     `btree` variant and each other variant as a sift arm named by it (an arm that no setting makes the plan of its name is listed as not forceable and
                                     left out); a `-- fresh: yes` case runs every repetition in a new backend. A load case has no plan to pick: its lanes (the sift table with its index,
                                     the heap with the b-tree or GIN twin, each variant) are forced arms, each repetition prepares its table again, the statements are timed and added, a
                                     second session counts the parallel workers. The file is written after every case (a run that stops keeps the cases before it) and checked on the
                                     way out; a case without records fails the run. An arm whose statement reaches `--timeout`, or that uses `--arm-budget` seconds (300) over its
                                     repetitions, is recorded as unavailable and named with its reason (RECORDS ARM TIMED OUT); the other arms and the cases after it are measured. The cluster of the cases is
                                     a test cluster (no autovacuum, no automatic merge), so its records say `maintenance` = `disabled`.
steps/records/collect.py             the library any other harness calls (`measure_statement`) with its subject, arms and environment.

The figures of the CHANGELOG

bench/claims.tsv lists the figures a CHANGELOG line states (a phrase of the line, the statement, size, arm, metric and value, a tolerance in percent). bench records claims RECORDS... (and bench cases --check --records FILE) fails a figure that the median of the records misses by more than its tolerance and prints both: the line and the run. A phrase that matches no line is stale and fails as well. The value is the one the line states, typed once there; the records are never typed. The phrase - is a figure that no CHANGELOG line states (a design note, a page; its note column says where): it is checked against the records alone, and its metric may be a build’s instr_leader_m, instr_workers_m, index_bytes, wal_bytes, segments, immutable_segments or buffer_segments.

What one row is

One statement, on one arm, warm or cold, one repetition. The arm is the structure that answered: heap (a sequential scan), btree, gin, sift or native (the normal plan), with its option (default, key_order=ts, …). Arms are chosen by forcing (enable_*, the index in the search path or the setting of the structure) on the same data, so they are comparable; the plan the planner chooses with nothing forced is measured as well (mode = default) and each forced arm says whether it is that plan (picked). The arm native is the normal plan (sift.enable_agg = off): not a structure to force, so it has the structure its plan names, and it is never picked. The structure is read from the EXPLAIN of the run and the arm must be it. Time is three columns: planning, execution (EXPLAIN ANALYZE) and the whole statement from the client. Instructions are those of the postmaster tree, workers included, and say their scope. Blocks are split by the kind of relation: heap, b-tree, gin, sift. Cold means the shared buffers of the relations were evicted; os_cache_dropped says whether the OS cache was emptied as well.

column type required meaning
run_id text yes the run that wrote the row: tip, UTC time and four hex digits; every number in a document cites one
tip text yes the commit of the build measured (12 hex digits)
harness text yes the program that measured, with the version of its records library: rows of two harnesses are not compared
dataset text yes the name of the data (generator and parameters)
rows integer yes rows of the table
keys integer distinct values of the key column
rows_per_key number rows / keys
heap_order text yes how the heap holds the key: arrival, sorted, hash or other
heap_corr number the correlation of the key with the heap, measured (pg_stats), not the order the generator meant
table_state text yes fresh_vacuumed, fresh_unvacuumed, churned_vacuumed or churned_unvacuumed
allvisible_pct number pages marked all-visible, in percent
churn_history text what was done to the table before the arms: upd=, ins=, del= (percent of the rows a round) and rounds= when the harness churned it with apply_churn, else the tuples inserted, updated and deleted since its statistics began (ins=, upd=, del=); empty for a fresh table
vacuums integer VACUUMs run on the table (pg_stat_user_tables vacuum_count) when the arms started
autovacuums integer autovacuums run on the table (autovacuum_count) when the arms started
settled 0 or 1 1 when the harness waited for the maintenance (VACUUM, the worker pass) to settle before the arms; needs a VACUUM or a pass on record
settled_s number seconds from the last VACUUM, autovacuum or sift maintenance pass of the table to the start of the arms
dead_pct number dead tuples in percent of live and dead (pg_stat_user_tables) at the start
tombstone_pct number dead rows in percent of the rows of the segments of the sift index (sift.col_segments) at the start, when the table has one
structures text yes the structures on the table, comma separated and sorted (btree,heap,sift): what was built, not what the arm used; constraint stands for the indexes of a primary key or unique constraint, the table’s definition, which a write pays for in every arm
statement text yes the name of the statement
sql_hash text yes hash of the statement text without its white space: one name is one text
variant text yes the table the arm ran on when a comparison has several (a heap in arrival order and one sorted by the key, an index built with other options): main when there is one; the planner picks within a variant
kind text yes read, maintenance (VACUUM, merge), load or build (CREATE INDEX of a sift index: the instructions of the leader and of the workers, the segments it wrote, the options it was built with)
protocol text yes what one repetition is: single, or the rounds it averages (rounds=12): a figure of one round is not the average of twelve
mode text yes forced (the arm was selected with settings), default (the planner chose) or unavailable (no setting makes the plan of this arm: one row without times, structure names what the plans were)
arm text yes heap, btree, gin, sift or native (the normal plan, sift.enable_agg off: its structure is whatever the plan names, and it is never picked); default rows carry the structure the plan named
arm_option text yes the option of the structure: default, key_order=ts, key_cut, …; a default row names the option of the forced arm that ran the plan the planner chose (default when none did)
picked 0 or 1 yes 1 when this arm ran the plan the planner chooses by default: the same plan_hash (schema 7 on), before it the same structure and option
structure text yes the structure the EXPLAIN of this run names: heap, btree, gin, sift or mixed
plan_hash text twelve hex digits of the shape of the plan that ran (node types, join types, relations and indexes; no cost, row count or time): rows with one hash ran one plan, so the pick of the planner among arms of one structure that differ in the join method is matched by it (a read, schema 7 on)
plan_shape text the shape the hash is of, as text (Hash Join[Inner](Seq Scan orders, Custom Scan SiftAgg orders_col)), cut at 1,000 characters
temp text yes warm or cold
rep integer yes the repetition, from 1
est_cost number the planner total cost of the plan that ran
plan_ms number planning time (EXPLAIN SUMMARY), apart from the execution
exec_ms number execution time (EXPLAIN ANALYZE, per-node timing on), planning not included; compares only plans of similar row flow (the clock reads of a node that moves many rows inflate it), wall_ms is the figure across join methods
wall_ms number yes the statement from the client: planning, execution and the transfer of the rows
instr_m number millions of user instructions of the statement
cycles_m number millions of user cycles
instr_scope text cluster (the postmaster tree: backend and parallel workers) or backend (the backend alone: only valid with no worker launched)
instr_n integer statements run in the window the instructions were counted over (a statement under 5 ms is run again and again in the one backend between two readings and the count divided: the number is of one run); 1 for a long one
instr_leader_m number build: millions of user instructions of the leader backend alone, counted in a run of its own after the cluster count (the same statement on the same prepared table)
instr_workers_m number build: instr_m - instr_leader_m, the instructions of the parallel workers (0 when none launched)
workers_requested integer yes parallel workers the plan asked for (a maintenance statement or a load has no plan: the workers it started)
workers_launched integer yes parallel workers that ran
heap_hit integer blocks of heap relations found in shared buffers
heap_read integer blocks of heap relations read
btree_hit integer b-tree index blocks found in shared buffers
btree_read integer b-tree index blocks read
gin_hit integer gin index blocks found in shared buffers
gin_read integer gin index blocks read
sift_hit integer sift index blocks found in shared buffers
sift_read integer sift index blocks read
other_hit integer blocks of other relations (toast, other index kinds) found in shared buffers
other_read integer blocks of other relations read
os_cache_dropped 0 or 1 cold rows: 1 when the OS page cache was emptied too, 0 when only the shared buffers were
table_bytes integer pg_table_size of the table after the run (heap, TOAST and free space map; a load names the schema, the sum of its tables)
index_bytes integer pg_relation_size of the indexes of the arm’s structure after the run (sift, b-tree or GIN; none for the heap arm): what a CREATE INDEX or a rewrite built
segments integer segments in the directory of the sift index of the arm after the run (all kinds: sift.col_segments, sift.segments); none for an arm without a sift index
immutable_segments integer of them the immutable ones (a build or merge wrote them)
buffer_segments integer of them the write buffers, open or sealed (rows the maintenance has not yet written as a segment)
segments_declared integer the segment count the harness says the index has (a case that names it, -- segments: N): a row whose count is another is refused
index_options text the options the sift index of the arm was built with (its reloptions, name=value, sorted, ; between), default when it has none
wal_bytes integer WAL written by the run
fpw 0 or 1 yes full_page_writes of the server
cpus number yes the CPU quota the server ran with (cpus)
noise 0 or 1 1 when the host CPU or IO pressure was above 10 points during the run: a flag, not a refusal (the load of the host is a fact about the run)
psi_cpu_start number host CPU pressure (some, avg10) at the start of the run
psi_cpu_end number host CPU pressure at its end
psi_io_start number host IO pressure at the start
psi_io_end number host IO pressure at its end
shared_buffers_mb number yes shared_buffers
work_mem_mb number yes work_mem
pg_version text yes the server version
settings text every setting that is not the default (name=value, sorted, ; between), the cluster and the session before the arm forced anything
settings_hash text yes ten hex digits of settings: two rows compared have the same one
maintenance text yes default, or disabled when a switch that turns the maintenance off is set (autovacuum, sift.col_auto_merge, sift.async_maintenance, a sift.test_* setting)

A harness that declares what the table of a write or a maintenance holds (Subject.structures, Arm.structures) is held to it: after the statement the library reads the table’s indexes, and a run whose table holds something else (a CREATE INDEX that failed, an index nobody listed) raises. An index that backs a primary key or a unique constraint is listed as constraint, whatever its access method: it is the table’s definition, every write pays for it, and the checker accepts it beside the arm’s structure when every write figure of the comparison has it (COMPANION DIFFERS). Any other index, a b-tree too, is a structure of the figure and refuses it (OTHER STRUCTURES). The tip of a record is a commit: a tree that is no work tree of its revision names it by hand.

The subject table of a partitioned table has no statistics, storage or access method of its own, so its facts are those of its leaf partitions together: tuples and dead tuples summed, vacuums and autovacuums the fewest of any leaf, settled_s the time since the latest pass of any leaf (empty while a leaf has had none), tombstone_pct over the leaves with a sift structure, table_bytes and index_bytes summed over the leaves. A view, or a partitioned table with no leaf, has no facts.

A build (kind build: a case with -- kind: build, col_build_default and col_build_date_key) is the CREATE INDEX of a sift index alone, the arms its options and worker settings. Each warm repetition counts the cluster (leader and workers, instr_m), then the leader alone in a run of its own on a table prepared again (instr_leader_m); instr_workers_m is the difference. Every record of a sift arm that has index_bytes carries the directory of its index after the run (segments, immutable_segments, buffer_segments, from sift.col_segments or sift.segments) and the options it was built with (index_options); a case that says -- segments: N is recorded with segments_declared and refused when the directory holds another count.

Versions of the schema

A file starts with #records-schema N (files before the mark are known by their header). Every version’s columns are frozen in history.py; columns are only added. A reader upgrades an older file in memory (schema.MIGRATIONS: a step from each version to the next, a new column gets its value from what the row says or “not recorded”) and the tools say so: note: FILE is schema 3, read as schema 5; bench records migrate FILE... writes the file again in the current schema and keeps the old one as FILE.schema3. A change of the columns is a new VERSION, its columns in history.py, a step in MIGRATIONS and a frozen sample in bench/test/records_samples; test_records.py (part of lint) fails until the three exist and when a frozen sample is edited.

What the checker refuses

A comparison is the rows of one statement on one dataset and size. Within it:

refusal rule
MIXED RUNS one run_id: the arms were measured together, on one build, under one load
MIXED STATE one table state, churn history (updates, inserts, deletes, rounds, VACUUMs and autovacuums on record, settled), protocol and key distribution within a comparison, dead tuples and tombstones within 5 points; one heap order within a variant (a variant is one table, so a heap in arrival order and one sorted by the key are two variants, each with its own default plan and picked flag)
MIXED SETTINGS one settings_hash (every setting that is not the default, the cluster and the session before an arm forces anything), shared_buffers, work_mem, version, cpu quota, full_page_writes and harness; the report header shows the hash and the list
TEST SETTINGS NOT LABELLED / LABELLED DISABLED WITHOUT A SWITCH a setting that turns the maintenance off (autovacuum, sift.col_auto_merge, sift.async_maintenance, any sift.test_*, as a test cluster’s configuration sets them) is refused unless the set says maintenance = disabled, which the report prints in capitals; the label without such a setting is refused too
TWO STATEMENTS one sql_hash for a statement name
HEAP ORDER NOT AS DECLARED sorted needs a measured correlation above 0.9, hash under 0.1
WRONG ARM a forced arm is the structure its EXPLAIN names (a mixed plan is no arm)
NO PICKED / PICKED FLAG the default plan is in the set and each arm says whether it is that plan; a default plan that mixes structures is a row of its own (arm mixed) that no forced arm equals; a maintenance or a load has no plan to pick: forced, picked 0 The arm native (the normal plan) is never picked, whatever its plan.
CHURN HISTORY MISSING / SETTLED WITHOUT A VACUUM a churned read says what was applied (collect.apply_churn returns it: percent of rows updated, inserted and deleted a round, rounds; else the tuples the statistics counted); settled needs a VACUUM, an autovacuum or a sift pass on record
INSTRUCTIONS MISSING a warm row has a count (perf failed or was not run: refused, not left empty)
INSTRUCTIONS WITHOUT SCOPE / WITHOUT WORKERS a count of the backend alone is refused when workers ran
WORKERS SHORT / QUOTA UNDER THE PROCESSES the workers the plan asked for ran, and the cpu quota holds them and the backend
PLANNING NOT SEPARATE a read has planning and execution apart
COLD WITHOUT os_cache_dropped a cold row says what was emptied
TOO FEW RUNS / DUPLICATE REPETITIONS at least 3 repetitions of each arm and temperature
(not a refusal) noisy host a run above 10 points of host pressure carries noise = 1 and a cell above it or with a CV over 10% is marked noisy; the report warns when the arms of a comparison were measured under pressure more than 10 points apart. The load of the host is a fact about the run, so it never refuses a file; different data, table state or settings do
OTHER STRUCTURES a maintenance or load figure is of a table that holds the arm’s structure alone (and constraint: the indexes a primary key or unique constraint needs, part of the table’s definition)
COMPANION DIFFERS the write figures of a comparison are not all of a table with a constraint index, or all of one without: the arms would differ in more than their structure
SEGMENTS MISSING a sift arm with index_bytes has no segments, immutable_segments, buffer_segments or index_options (rows of schema 6 and later; an older row is not refused)
SEGMENT KINDS immutable and write buffer segments are more than the segments of the directory
SEGMENTS CONTRADICT the count a case declares (segments_declared) is not the count of the directory
BUILD WITHOUT SPLIT / BUILD SPLIT / BUILD WORKERS a warm build row lacks instr_leader_m or instr_workers_m, their sum is not the cluster count (1%), or the cluster ran beyond the leader with no worker launched
BUILD WITHOUT index_bytes / wal_bytes a build row lacks the bytes of its index or its WAL
BUILD ON TEST SETTINGS a build measured on a cluster with a sift.test_* setting: the build is not the product’s
TIP UNKNOWN the tip of a record is not a commit (12 hexadecimal digits)
NO RECORDS an empty set proves nothing

The imprecisions this prevents

Each row is a way a reported number was wrong or had to be revised, and the column or rule that now makes it impossible to state.

what went wrong prevented by
a figure taken on sequential strings quoted beside a case that used random strings the dataset columns (keys, rows_per_key, heap_order, heap_corr) and one dataset in a comparison
the time of a CREATE INDEX quoted as the time of the merge statement the statement name and sql_hash, kind; a claim names its statement
gains of parallel workers hidden by a one-cpu quota cpus, workers_requested, workers_launched: QUOTA UNDER THE PROCESSES, WORKERS SHORT
instructions of the backend beside a time with workers instr_scope: INSTRUCTIONS WITHOUT WORKERS
execution time quoted as the whole statement, or the reverse plan_ms, exec_ms, wall_ms apart
a cold read that was cold in the shared buffers only temp, os_cache_dropped
WAL of a run with full page writes beside one without fpw in the settings that must agree
a fresh table beside a churned one that was not vacuumed table_state: MIXED STATE
the plan the planner picks quoted for an arm it did not run, or two arms that were the same plan mode, picked, structure: WRONG ARM, PICKED FLAG
a heap meant to be in no order that was clustered by the key heap_corr measured: HEAP ORDER NOT AS DECLARED
a maintenance figure of an arm whose table also kept another index structures: OTHER STRUCTURES
a write cost with the primary key of the table in it, beside one without structures: constraint, COMPANION DIFFERS
a record of code nobody can find again tip: TIP UNKNOWN
a “7 segments” index whose record holds no count segments, segments_declared: SEGMENTS MISSING, SEGMENTS CONTRADICT
a build cost that counts the leader only, or the workers by guess instr_leader_m, instr_workers_m: BUILD SPLIT
a baseline file of an older harness compared with a new run harness with the library version and one run_id
a number that held for one setting of a structure stated for all arm_option carries the options that changed the structure
one round quoted as the average of twelve protocol
a state measured on a cluster whose configuration turned the maintenance off (dead rows no VACUUM took, segments no merge joined), reported as the default’s settings, settings_hash, maintenance: TEST SETTINGS NOT LABELLED, MIXED SETTINGS
a time taken while the host was busy beside one taken while it was not psi_*, noise: the noisy mark and the warning of the report
a pass read from an empty output NO RECORDS, and the count of rows the tools print
a CHANGELOG figure that no longer matches the measurement the claims check against the records