9.31. Statistics Information Functions #

9.31.1. Inspecting MCV Lists

PostgreSQL provides a function to inspect complex statistics defined using the CREATE STATISTICS command.

9.31.1. Inspecting MCV Lists #

pg_mcv_list_items ( pg_mcv_list ) → setof record

pg_mcv_list_items returns a set of records describing all items stored in a multi-column MCV list. It returns the following columns:

NameTypeDescription
indexintegerindex of the item in the MCV list
valuestext[]values stored in the MCV item
nullsboolean[]flags identifying NULL values
frequencydouble precisionfrequency of this MCV item
base_frequencydouble precisionbase frequency of this MCV item

The pg_mcv_list_items function can be used like this:

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
                pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts';

Values of the pg_mcv_list type can be obtained only from the pg_statistic_ext_data.stxdmcv column.