The database statistics objects data and pg_stats view
The ANALYZE command updates the pg_statistic and pg_class system catalogs, which are used by the Query Planner to determine the cost of the chosen execution plan. The table rows processed by the reservoir sampling store their column metrics in pg_stats to represent the table statistical properties:
Null_Frac: A value between 0 and 1 representing the proportion of NULL values present in the sampled table set.
n_distinct: The number of unique, non-null column values found in the sampled rows.
Average Width: The average physical length (in bytes) of the column's values.
Correlation: A value (between -1 and +1) that represents the physical correlation between the logical order of the rows and their physical location on disk, with 0 being random.
most_common_vals: An array that holds the Most Common Values (MCV) that appear in the rows. most_common_freqs: The parallel array that holds the fractional frequency (proportion) for each corresponding MCV value.
Histogram Distribution: The distribution of the values that aren't part of the MCV set, which informs the planner about the data spread of the column values.
-- The ANALYZE updates the system catalog statistical information
CREATE TABLE tavole ( codice integer, padding text );
INSERT INTO tavole (codice, padding)
SELECT floor(random() * 100000) + 1, md5(random()::text)
FROM generate_series(1, 100000) AS i;
ANALYZE tavole;
-- pg_stats is the user-friendly interface to the pg_statistic system catalog.
-- Both the most_common and the histogram_bounds are arrays
SELECT
tablename, attname AS column_name,
null_frac, n_distinct, correlation,
most_common_vals, most_common_freqs, histogram_bounds
FROM pg_stats WHERE tablename = 'tavole' AND attname = 'codice';— IMMAGGINE output query
Database statistics utilize specific system columns depending on the data type of the column being analyzed, like most_common_vals and most_common_elems.
Different pg_stats columns for VALUES and ELEMENTS data types
Statistics for values, as scalar data types (like integers and text), are based on how frequently the entire data cell appears in the column. The statistics for elements, as complex data types (like ARRAY or JSONB), track how often the internal items appear inside the data structures across the table rows.
The pg_stats view includes specific columns reserved for multidimensional data types, like the most_common_elem_freqs and elem_count_histogram. These columns appear in the view's output structure, but remain empty (NULL) if the analyzed table column doesn't contain a complex data type.
The n_distinct metadata value varies based on its mathematical sign. A positive value returns the number of unique column values in the table statistics, while a negative value represents the ratio of unique values to total rows (where -1 is 100%).
The query planner uses it to calculate cardinality, providing either a precise count of unique values or a stable percentage estimate as the table scales. It includes the total unique values in both the MCV list and the histogram.
An equi-height histogram contains a fixed number of values across all buckets, while the width defined by its boundaries varies. Each bucket represents an equal fraction of the table rows, accurately reflecting the data's density.
The ANALYZE command updates the metadata columns in the pg_class catalog:
reltuples: The estimated count of the live rows (tuples) in the table.
relpages: The estimated number of disk pages occupied by the table.
The pg_statistic data is stored in binary code. It's designed to be read by the query planner and requires an interpreter to be accessed. The pg_stats is not a system catalog but a system view, a virtual table created by a query. It formats the pg_statistic data into a readable structure and joins columns from other catalogs, like tablename and attname (column name).
The planner selectivity estimation for query operators
A single-value equality clause (=) calculates its selectivity by first checking the most_common_vals for the query value; if found, it returns the corresponding mcv frequency. The query planner estimates the non-MCV query value using the residual frequancy (using the formula 1 - the sum of all mcv frequencies), it then divides it by the number of remaining distinct values (subtracting the mcv values form the total n_distinct), assuming a uniform distribution. A scalar range (<, >) query analyzes the boundaries of the histogram buckets to detect which ones include the query values. The planner defines the specific range by using linear interpolation to "cut" the histogram bounds, in order to estimate the total frequency for the selected range.
— mini immage --
The frequency and selectivity can share the same mathematical value, but they represent different data sources and stages within the query planner process. The frequency is a raw value stored in the pg_statistic metadata columns. It represents the physical proportion of specific values within the table rows. The selectivity defines the fraction of rows selected by the query clause. The query planner calculates this value based on the frequency, but the result changes depending on the specific query conditions being estimated. The probability is the conceptual term we use to interpret the selectivity value.
1
Calculating the execution plan cost from the query clauses
The query planner compares all paths in the plan tree, calculating the cost of each physical operation based on its selectivity, correlation, and average row size. The total cost of an execution plan is determined by data volume and I/O efficiency. The planner calculates the volume by multiplying selectivity row count by the avg_width. The correlation (ranging from -1 to 1) represents the I/O cost: high absolute values indicate ordered data suitable for index scans, while values near zero indicate scattered data that requires Bitmap or Sequential scans to avoid the random I/O cost.
The SELECT columns define the output payload and influence the query plan cost with their avg_width value, while the WHERE clauses define the estimation process for the columns selectivities. The planner retrieves the selectivies for multiple query conditions (equality or scalar) from the MCV list and the histogram bins as non-overlapping data sources. It multiples the selctivities values for 'AND' conditions while it sums it for 'OR'.
— IMMA --
The query planner uses the conditional independence assumption when evaluating plans involving multiple columns. It multiplies the individual selectivity of each column, assuming they are unrelated. Extended statistics estimate the functional dependencies between correlated column values, which provides the ANALYZE process with additional data for a more accurate execution plan.
1
The statistical object column metadata and pg_stats view
The CREATE STATISTICS command builds a statistical object for its specified table columns. It's not part of the standard pg_stats view; instead it consists of multi-party entry distributed across two primary system catalogs pg_statistic_ext and pg_statistic_ext_data.
The extended statistics metadata differ from the pg_statistics. They cover multivariate functional dependencies and use them to enhance the standard query planner estimation process:
Initial Estimation: The planner generates an initial estimate using standard pg_stats metadata.
Stat Lookup: It checks the pg_statistics_ext catalog for the specific columns included in the query.
Override: If matching columns are found, the planner overrides the initial estimate to produce a more accurate execution plan.
The planner can still partially apply the extended statistics for some matching columns while treating the remaining ones as independent.
The CREATE STATISTICS command requires running ANALYZE to populate the pg_statistics_ext_data catalog with the actual processed information.
--query image --
We need specific queries to extract the frequencies for specific MCV entries from columns part of the same pg_stats.
The tuple conditions are a set of column-value checks representing a single, isolated logical unit. The unnest() set returning function expands the MCV entries into a virtual table, which we reference in the SELECT using the m() table alias. We apply an implicit LATERAL JOIN using the comma (,) syntax, which iterates over each entry to generate rows for the virtual table (m). The WHERE clause filters the resulting dataset using its tuple conditions.
We create a scalable query that separates the search data from the logical structure. The lookup table (v) uses the VALUES clause to define its dynamic dataset instead of hardcoding multiple conditions into a WHERE clause. The JOIN ... ON ... conditions apply to different tables in their query positions; they apply to pg_stats (s) to specify the columns arguments for the unnest(), and to the virtual table (m) created by the unnest set-returning function, to include only the MCV entries that match the lookup table.
The Predicate Pushdown is the query optimitation that alters the sequence of the execution plan. it forces the database to evaluate JOIN conditions earlier, filtering the data before the expensive unnest() step occurs. The query no longer relies on the WHERE clause to filter data after the unnest() expansion, It uses its specific tuples conditions to directly restrict the virtual lookup table (v) search space to only include the requested data.
The query planner is data-blind when calculating the total selectivity of the query's condition. Both pg_stats and pg_stats_ext rely on directly matching raw query values against MCV lists or histogram buckets. The planner can't process math operations, function calls, or type casting within a query condition, as it would be too expensive to transform the values to match the pg_stats metadata. It instead falls back on generic selectivity estimates, such as 0.005 for equality and 0.33 for scalar operations.
— It can work for single colun exprs --
Statistical objects for columns expressions
The CREATE STATISTICS command can generate functional statistics for columns expressions. The ANALYZE applies the expression to every table row and maps the results to a virtual column, which provides the planner with the actual data distribution and removes the need for guesses.
The functional statistics are stored separately from multivariate dependencies. They don't include metadata columns like as n_distinct and dependencies, which are reserved for multi-column relationships; instead, they store their own MCV and histogram values in the pg_stats_ext_exprs catalog.
— IMMAGGINE
We add the IF NOT EXISTS clause to the CREATE STATISTICS command to prevent errors if an object with that name already exists. The clause triggers a silent skip based solely on the name, regardless of the columns or expressions used in the definition.
The Extended statistics object for multi-columns metadata
The CREATE STATISTICS command creates multivariate dependencies when applied to multiple columns. The statistical object, stored in pg_statistic_ext, acts as a flag. It instructs the ANALYZE command to calculate additional statistics for those specific columns. The ANALYZE process maintains a single scan of the table data from the disk. It adds a simultaneous second track that analyzes the relationships between column combinations rather than treating them as separate and independent.
The pg_statistic_ext system catalog contains the columns definition of the statistics that appear in the pg_stats_ext view.
OID: It's an unique identifier of the statistics object within the database stxrelid: The OID value of the pg_class table described by the statistics object. stxname: It records the object's name in the CREATE STATISTICS command. stxnamespace: It identifies the schema (namespace) where the object resides. stxowner: The OID of the user managing the access. stxkeys: It's an array that contains the columns positions attributes of the statistics object as indexes. stxkind: A string that defines the different types of analysis enabled. stxexprs: It stores the expression used in functional statistics objects. stxstattarget: It defines the detail level of extended statistics compared to the individual columns they are composed of. A value of -1 inherits the highest statistics_target from its component columns, while a value of 0 disables statistics collection for the object. A positive integer sets an explicit limit on the number of Multivariate MCV entries, regardless of individual column settings.
The pg_statistic_ext_data system catalog contains the statistical results of the extended statistical objects that appear in teh pg_stats_ext view.
stxoid: The pg_statistic_ext OID value that originated the data.
stxdinherit: A boolean value (default false) indicating the scope of the statistics, it's true for statistics that apply to an entire inheritance tree (the table plus all its children/partitions).
stxdndistinct: It stores a serialized map of N-distinct coefficents for each unique column combination. It uses positive integers for the unique values and negative integers as a ratio relative to the total rows.
stxdependencies: It stores the functional dependency score for all the current column values combinations.
stxdmcv: It stores the MCV list, which tracks the most common columns combination values. It uses an internal binary format to store combinations that include multiple data types, which requires a set-returning function like pg_mcv_list_items() to be accesses.
stxdexpr: It's an array of pg_statistic objects. It stores the complete statistics set for the objects defined on columns expressions, using a serialized format.
We can use the pg_mcv_list_items() function to acccess the pg_statistics_ext_data mcv values in a virtual table.
The pg_mcv_list_items() inspection function on pg_statistics_ext_data
The inspection functions convert binary pg_statistics_ext_data into a structured, multi-row format. It provides more detailed, readable data than the standard pg_stats_ext view.
The LATERAL JOIN allows the inspection function to unnest filtered binary data that matches specific WHERE conditions. It references data dynamically from preceding tables, unlike a standard JOIN which connects static data between independent columns.
The function unnests a single row into multiple table rows to create a detailed dataset. The cardinality expansion transforms binary data into vertical rows of structured output. We apply this method to complex data types to create a visible output for analysis.
The pg_stats_ext view presents the binary content of pg_statistic_ext_data in a readable format. Its columns distinguish between the statistics object’s identification metadata and the processed statistical payload.
schemaname / tablename: The logical schema and the specific table name referenced by the statistics object. statistics_schemaname / statistics_owner: The schema and the database user who owns the statistics object. statistics_name: It provides the unique object name assigned during the CREATE STATISTICS process. attnames: This array lists the specific table column names included in the statistics object definition. exprs: It contains the SQL expressions used for functional or expression-based statistics. inherited: A boolean value indicating if the statistics include data from child tables or partitions. kinds: This char[] array lists the enabled statistic types: n-distinct (d), functional dependencies (f), most-common-values (m), or expressions (e).
The extended statistics don't include histograms. They provide multi-column metadata, like dependencies, mcv, and n_distinct. Multi-dimensional histograms aren't supported because they are too complex to generate during the ANALYZE process; extended statistics aren't designed to estimate selectivity for range operations, (like < or >).
n_distinct: It returns the estimated number of distinct value combinations for the specified columns, expressed as either a fixed integer or a ratio.
dependencies: It lists the column attribute index numbers and a score between 0 and 1 that represents the level of correlation between its columns.
most_common_vals / most_common_val_nulls: These arrays contain the most frequent combinations of values for the specified columns and identify the NULL combinations.
most_common_freqs / most_common_base_freqs: These columns report the actual frequency of each common value combination and the expected frequency assumed if the columns were independent. The query planner compares these values to detect real-world correlations.
The stxkind array column identifies the different types of statistical processes run during ANALYZE. It acts as a 'multi-tool' map, indicating which payload columns are populated within the pg_stats_ext view.
Each statistic type has a specific role: functional dependencies estimate selectivity for equality operators, n_distinct calculates cardinality for GROUP BY and ORDER BY clauses, and MCV is used for operations within query conditions.
The extended statistics object types (d, m, n).
The functional dependency (stxkind 'd') measures the logical correlation between the columns defined in a statistics object. It ranges from 0 to 1 and is derived from the data sample collected during ANALYZE. It's a value-agnostic score that tracks the global dependency level between columns, rather than specific value combinations like an MCV. It represents a functional rule that measures how consistently a value in the first column determines the value in the second.
The dependency score factors in dataset 'noise', which represents value pairs that contradict the logical dependencies. The planner uses the score to weight its total selectivity estimate for the query condition, accounting for both correlated and coincidental table rows.
The total multi-column selectivity formula uses the dependency score to weight the functional dependency against the independent 'noise'. It uses the single-column selectivities from the pg_stats mcv column.
The query planner uses the dependency formula only if the MCV statistics are excluded from the extended statistics definition. The planner ignores the dependency estimation if the specific query value combinations appear in the MCV list.
The functional dependency value describes the structure, not the content. Its formula, unlike MCV, doesn't depend on the query values combination. It instead acts as a mathematical generalization applied to the existing single-column statistics. It eliminates the need to store a large list of common combinations, allowing the planner to estimate selectivity for less common values that do not appear in the MCV statistics.
The multivariate MCV statistics (stxkind "m") provide the query planner with a list of specific column value combinations and their combined selectivities. The ANALYZE command scans the sampled data for unique value combinations and ranks them by frequency. It creates the most_common_vals, most_common_freqs, and most_common_base_freqs columns within the pg_stats_ext view.
It lists the most common column value combinations, ignoring the 'noise' of rare or irregular pairs that don't form a significant part of the dataset. It provides a precise cardinality estimate.
The multivariate n_distinct statistics (stxtype 'n') provide the query planner with the total number of unique combinations for the columns defined in the statistic object. It is a summary statistic that differs from MCV because it doesn't list exact column-value combinations and counts them regardless of their frequency. It prevents the planner from relyng on the independence assumption during GROUP BY, DISTINCT, and JOIN queries, though it does not affect filtering in WHERE clauses.
The single-column statistics include an n_distinct pg_stats column. The query planner uses these values for the independence assumption in multi-column queries, where it multiplies the individual n_distinct values to estimate the total number of unique combinations. The ANALYZE command doesn't automatically fill the multi-column n_distinct column because it can expensive to calculate for actual independent columns. It relies on the user to specify the statistic type when they identify a correlation.
The different aggregation strategies for GROUP BY queries
The GROUP BY clause uses table column attribute values. It's not limited to the ones found in the n_distinct JSON field in pg_stats_ext, as the planner can achieve a partial optimization even with columns outside the statistics. The n_distinct extended statistics allow the query planner to avoid relying on the independence assumption when calculating the total number of unique column value combinations for the table's rows.
The query planner uses statistics to define the aggregation strategy for the GROUP BY clause. It calculates the required memory by multiplying the estimated number of unique groups by the row width of the involved columns. This calculation determines if the Hash Aggregate or the Group Aggregate is used for the query execution.
The Hash Aggregate strategy is designed for query plans where the estimated number of unique groups (n_distinct) is small enough to fit within the work_mem. The hash table is a temporary structure that resides in local memory and is active only for the duration of the query. It stores the counts for all unique column combinations found during the scan, but it requires an additional ORDER BY clause as it doesnt sort the data for the query output.
The Group Aggregate strategy sorts and groups data directly from the table rows. It is designed for situations where the estimated number of n_distinct groups is too large to fit within work_mem. The process is divided into two operations: an initial Sort of the sequentially read table rows, followed by a Grouping step which sorts the data for the query output.
The total number of table rows doesn't affect the memory usage of the GROUP BY operation; it only affects the execution speed. The hash table is reserved for the unique combination values found during the table scan. The work_mem usage details are returned in the query output.
The hash table stores key-value pairs with the hash codes and the count value in the table rows. The hashing is a one-way process, which requires the table to store the actual column values for each entry. This is necessary to verify the key's identity and resolve hash collisions (where different values produce the same hash). The amount of work_mem occupied depends on the size of these stored combinations. If the table size exceeds the estimated memory limit, it creates a 'spill to disk' that slows down the operation.
Using the Cadensas' formula to estimate the n_distinct on WHERE clauses
The Cardenas' Formula describes how the query planner estimates the number of unique groups that will remain after a WHERE clause. This estimation is necessary to determine a more precise work_mem allocation. The formula uses both the n_distinct statistics and the number of rows returned by the WHERE condition.
1
1
The Cardenas's formula doesn't include any factor to track functionally correlated columns; instead, it relies on the n_distinct value provided by user-created extended statistics. The query planner can also utilize the MCV list for multi-column statistics. It uses the WHERE clause to filter the MCV combinations and identify exactly which ones will remain. It then subtracts these instances from the n_distant, using the Cardenas's formula for the more rare values. The sum of the matching MCVs and the estimated rare values is used by the query executor to allocate the work_mem space for the hash table.
The reason GROUP BY requires extra data structures (like hash tables) compared to the WHERE clause is their different relationship with the data. The HashAggregate node stores the data state in memory to track unique combinations, while the WHERE stream filters the rows and pushes them directly to the output node. Both operations rely on statistics to estimate the query execution plan and the required work_mem respectively.
The HashAggregate node is similar to a EXCLUDE GiST constraint in that both manage column uniqueness, but they differ in their role and storage. The GiST index is a permanent structure on disk used to enforce uniqueness, while the HashAggregate builds a temporary hash table in memory. The hash table doesn't exclude values, it uses the work_mem space to group and count unique combinations before sending them to the output.
The Postgresql statistics target
The statistics target property is a single value (by default 100) that controls the statistics size (the number of histogram bins and MCV slots) and defines the resolution of the ANALYZE data columns. An increased target increases the rows required for the sampling set and calculates more complex correlations. It slows the ANALYZE command used to collect the general table statistics, but it doesn't affect the EXPLAIN ANALYZE commands applied to queries, as they only utilize the relevant statistics for their query output. A small statistics target for a much larger table risks creating false statistics, where small samples indicate non-existent correlations.
We modify the statistics target for specific columns using the ALTER TABLE ... ALTER COLUMN ... command, which overrides the default target value. The ANALYZE process operates table-wide and uses the highest target between its columns for the sample size, which will include all other columns with a lower statistical target. This allows it to read the disk data only once while calculating the single columns' pg_stats based on their individual targets. The multi-column statistics objects don't inherit targets from their underlying columns; they require a separate configuration using ALTER STATISTICS ... SET STATISTICS ... .
The statistics target acts as a proportional value representing both the sampling size and storage capacity, as a large sampling paired with small storage capacity would waste read time, while a small sample size with large storage would simply result in empty space. The target acts as a selectivity threshold for statistics like histograms and MCVs, as it modifies the frequency required to add more value combinations and increases the number of histogram bounds. It only affects columns that physically list their specific combinations, like MCVs; it doesn't limit the value returned by calculated estimates, like n_distinct, that represent a count where a cap would provide incorrect information to the query planner.
The target determines how differently the statistics columns can be used to represent the same table data; a low target produces a small less precise MCV list and a large histogram needed to represent the remaining data, while a high target generates a large MCV list that includes all unique value combinations, removing the need for a histogram.
We access the columns statistics target in the pg_attribute system catalog.
Last updated