53.51. pg_statistic

53.51. `pg_statistic`
Prev	Up	Chapter 53. System Catalogs	Home	Next

53.51. `pg_statistic` #

+ The catalog pg_statistic stores + statistical data about the contents of the database. Entries are + created by ANALYZE + and subsequently used by the query planner. Note that all the + statistical data is inherently approximate, even assuming that it + is up-to-date. +

+ Normally there is one entry, with stainherit = + false, for each table column that has been analyzed. + If the table has inheritance children or partitions, a second entry with + stainherit = true is also created. This row + represents the column's statistics over the inheritance tree, i.e., + statistics for the data you'd see with + SELECT column FROM table*, + whereas the stainherit = false row represents + the results of + SELECT column FROM ONLY table. +

+ pg_statistic also stores statistical data about + the values of index expressions. These are described as if they were + actual data columns; in particular, starelid + references the index. No entry is made for an ordinary non-expression + index column, however, since it would be redundant with the entry + for the underlying table column. Currently, entries for index expressions + always have stainherit = false. +

+ Since different kinds of statistics might be appropriate for different + kinds of data, pg_statistic is designed not + to assume very much about what sort of statistics it stores. Only + extremely general statistics (such as nullness) are given dedicated + columns in pg_statistic. Everything else + is stored in “slots”, which are groups of associated columns + whose content is identified by a code number in one of the slot's columns. + For more information see + src/include/catalog/pg_statistic.h. +

+ pg_statistic should not be readable by the + public, since even statistical information about a table's contents + might be considered sensitive. (Example: minimum and maximum values + of a salary column might be quite interesting.) + pg_stats + is a publicly readable view on + pg_statistic that only exposes information + about those tables that are readable by the current user. +

Table 53.51. pg_statistic Columns

+ Column Type + + + Description +
+ `starelid` `oid` + (references `pg_class`.`oid`) + + + The table or index that the described column belongs to +
+ `staattnum` `int2` + (references `pg_attribute`.`attnum`) + + + The number of the described column +
+ `stainherit` `bool` + + + If true, the stats include values from child tables, not just the + values in the specified relation +
+ `stanullfrac` `float4` + + + The fraction of the column's entries that are null +
+ `stawidth` `int4` + + + The average stored width, in bytes, of nonnull entries +
+ `stadistinct` `float4` + + + The number of distinct nonnull data values in the column. + A value greater than zero is the actual number of distinct values. + A value less than zero is the negative of a multiplier for the number + of rows in the table; for example, a column in which about 80% of the + values are nonnull and each nonnull value appears about twice on + average could be represented by `stadistinct` = -0.4. + A zero value means the number of distinct values is unknown. +
+ `stakindN` `int2` + + + A code number indicating the kind of statistics stored in the + `N`th “slot” of the + `pg_statistic` row. +
+ `staopN` `oid` + (references `pg_operator`.`oid`) + + + An operator used to derive the statistics stored in the + `N`th “slot”. For example, a + histogram slot would show the `<` operator + that defines the sort order of the data. + Zero if the statistics kind does not require an operator. +
+ `stacollN` `oid` + (references `pg_collation`.`oid`) + + + The collation used to derive the statistics stored in the + `N`th “slot”. For example, a + histogram slot for a collatable column would show the collation that + defines the sort order of the data. Zero for noncollatable data. +
+ `stanumbersN` `float4[]` + + + Numerical statistics of the appropriate kind for the + `N`th “slot”, or null if the slot + kind does not involve numerical values +
+ `stavaluesN` `anyarray` + + + Column data values of the appropriate kind for the + `N`th “slot”, or null if the slot + kind does not store any data values. Each array's element + values are actually of the specific column's data type, or a related + type such as an array's element type, so there is no way to define + these columns' type more specifically than `anyarray`. +

+ Column Type +

+ Description +

+ starelid oid + (references pg_class.oid) +

+ The table or index that the described column belongs to +

+ staattnum int2 + (references pg_attribute.attnum) +

+ The number of the described column +

+ stainherit bool +

+ If true, the stats include values from child tables, not just the + values in the specified relation +

+ stanullfrac float4 +

+ The fraction of the column's entries that are null +

+ stawidth int4 +

+ The average stored width, in bytes, of nonnull entries +

+ stadistinct float4 +

+ The number of distinct nonnull data values in the column. + A value greater than zero is the actual number of distinct values. + A value less than zero is the negative of a multiplier for the number + of rows in the table; for example, a column in which about 80% of the + values are nonnull and each nonnull value appears about twice on + average could be represented by stadistinct = -0.4. + A zero value means the number of distinct values is unknown. +

+ stakindN int2 +

+ A code number indicating the kind of statistics stored in the + Nth “slot” of the + pg_statistic row. +

+ staopN oid + (references pg_operator.oid) +

+ An operator used to derive the statistics stored in the + Nth “slot”. For example, a + histogram slot would show the < operator + that defines the sort order of the data. + Zero if the statistics kind does not require an operator. +

+ stacollN oid + (references pg_collation.oid) +

+ The collation used to derive the statistics stored in the + Nth “slot”. For example, a + histogram slot for a collatable column would show the collation that + defines the sort order of the data. Zero for noncollatable data. +

+ stanumbersN float4[] +

+ Numerical statistics of the appropriate kind for the + Nth “slot”, or null if the slot + kind does not involve numerical values +

+ stavaluesN anyarray +

+ Column data values of the appropriate kind for the + Nth “slot”, or null if the slot + kind does not store any data values. Each array's element + values are actually of the specific column's data type, or a related + type such as an array's element type, so there is no way to define + these columns' type more specifically than anyarray. +

Prev	Up	Next
53.50. `pg_shseclabel`	Home	53.52. `pg_statistic_ext`

53.51. pg_statistic #

53.51. `pg_statistic` #