codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>53.51. pg_statistic</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="catalog-pg-shseclabel.html" title="53.50. pg_shseclabel" /><link rel="next" href="catalog-pg-statistic-ext.html" title="53.52. pg_statistic_ext" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">53.51. <code class="structname">pg_statistic</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="catalog-pg-shseclabel.html" title="53.50. pg_shseclabel">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="catalogs.html" title="Chapter 53. System Catalogs">Up</a></td><th width="60%" align="center">Chapter 53. System Catalogs</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="catalog-pg-statistic-ext.html" title="53.52. pg_statistic_ext">Next</a></td></tr></table><hr /></div><div class="sect1" id="CATALOG-PG-STATISTIC"><div class="titlepage"><div><div><h2 class="title" style="clear: both">53.51. <code class="structname">pg_statistic</code> <a href="#CATALOG-PG-STATISTIC" class="id_link">#</a></h2></div></div></div><a id="id-1.10.4.53.2" class="indexterm"></a><p>3 The catalog <code class="structname">pg_statistic</code> stores4 statistical data about the contents of the database. Entries are5 created by <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a>6 and subsequently used by the query planner. Note that all the7 statistical data is inherently approximate, even assuming that it8 is up-to-date.9 </p><p>10 Normally there is one entry, with <code class="structfield">stainherit</code> =11 <code class="literal">false</code>, for each table column that has been analyzed.12 If the table has inheritance children or partitions, a second entry with13 <code class="structfield">stainherit</code> = <code class="literal">true</code> is also created. This row14 represents the column's statistics over the inheritance tree, i.e.,15 statistics for the data you'd see with16 <code class="literal">SELECT <em class="replaceable"><code>column</code></em> FROM <em class="replaceable"><code>table</code></em>*</code>,17 whereas the <code class="structfield">stainherit</code> = <code class="literal">false</code> row represents18 the results of19 <code class="literal">SELECT <em class="replaceable"><code>column</code></em> FROM ONLY <em class="replaceable"><code>table</code></em></code>.20 </p><p>21 <code class="structname">pg_statistic</code> also stores statistical data about22 the values of index expressions. These are described as if they were23 actual data columns; in particular, <code class="structfield">starelid</code>24 references the index. No entry is made for an ordinary non-expression25 index column, however, since it would be redundant with the entry26 for the underlying table column. Currently, entries for index expressions27 always have <code class="structfield">stainherit</code> = <code class="literal">false</code>.28 </p><p>29 Since different kinds of statistics might be appropriate for different30 kinds of data, <code class="structname">pg_statistic</code> is designed not31 to assume very much about what sort of statistics it stores. Only32 extremely general statistics (such as nullness) are given dedicated33 columns in <code class="structname">pg_statistic</code>. Everything else34 is stored in <span class="quote">“<span class="quote">slots</span>”</span>, which are groups of associated columns35 whose content is identified by a code number in one of the slot's columns.36 For more information see37 <code class="filename">src/include/catalog/pg_statistic.h</code>.38 </p><p>39 <code class="structname">pg_statistic</code> should not be readable by the40 public, since even statistical information about a table's contents41 might be considered sensitive. (Example: minimum and maximum values42 of a salary column might be quite interesting.)43 <a class="link" href="view-pg-stats.html" title="54.27. pg_stats"><code class="structname">pg_stats</code></a>44 is a publicly readable view on45 <code class="structname">pg_statistic</code> that only exposes information46 about those tables that are readable by the current user.47 </p><div class="table" id="id-1.10.4.53.8"><p class="title"><strong>Table 53.51. <code class="structname">pg_statistic</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_statistic Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">48 Column Type49 </p>50 <p>51 Description52 </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">53 <code class="structfield">starelid</code> <code class="type">oid</code>54 (references <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">oid</code>)55 </p>56 <p>57 The table or index that the described column belongs to58 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">59 <code class="structfield">staattnum</code> <code class="type">int2</code>60 (references <a class="link" href="catalog-pg-attribute.html" title="53.7. pg_attribute"><code class="structname">pg_attribute</code></a>.<code class="structfield">attnum</code>)61 </p>62 <p>63 The number of the described column64 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">65 <code class="structfield">stainherit</code> <code class="type">bool</code>66 </p>67 <p>68 If true, the stats include values from child tables, not just the69 values in the specified relation70 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">71 <code class="structfield">stanullfrac</code> <code class="type">float4</code>72 </p>73 <p>74 The fraction of the column's entries that are null75 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">76 <code class="structfield">stawidth</code> <code class="type">int4</code>77 </p>78 <p>79 The average stored width, in bytes, of nonnull entries80 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">81 <code class="structfield">stadistinct</code> <code class="type">float4</code>82 </p>83 <p>84 The number of distinct nonnull data values in the column.85 A value greater than zero is the actual number of distinct values.86 A value less than zero is the negative of a multiplier for the number87 of rows in the table; for example, a column in which about 80% of the88 values are nonnull and each nonnull value appears about twice on89 average could be represented by <code class="structfield">stadistinct</code> = -0.4.90 A zero value means the number of distinct values is unknown.91 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">92 <code class="structfield">stakind<em class="replaceable"><code>N</code></em></code> <code class="type">int2</code>93 </p>94 <p>95 A code number indicating the kind of statistics stored in the96 <em class="replaceable"><code>N</code></em>th <span class="quote">“<span class="quote">slot</span>”</span> of the97 <code class="structname">pg_statistic</code> row.98 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">99 <code class="structfield">staop<em class="replaceable"><code>N</code></em></code> <code class="type">oid</code>100 (references <a class="link" href="catalog-pg-operator.html" title="53.34. pg_operator"><code class="structname">pg_operator</code></a>.<code class="structfield">oid</code>)101 </p>102 <p>103 An operator used to derive the statistics stored in the104 <em class="replaceable"><code>N</code></em>th <span class="quote">“<span class="quote">slot</span>”</span>. For example, a105 histogram slot would show the <code class="literal"><</code> operator106 that defines the sort order of the data.107 Zero if the statistics kind does not require an operator.108 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">109 <code class="structfield">stacoll<em class="replaceable"><code>N</code></em></code> <code class="type">oid</code>110 (references <a class="link" href="catalog-pg-collation.html" title="53.12. pg_collation"><code class="structname">pg_collation</code></a>.<code class="structfield">oid</code>)111 </p>112 <p>113 The collation used to derive the statistics stored in the114 <em class="replaceable"><code>N</code></em>th <span class="quote">“<span class="quote">slot</span>”</span>. For example, a115 histogram slot for a collatable column would show the collation that116 defines the sort order of the data. Zero for noncollatable data.117 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">118 <code class="structfield">stanumbers<em class="replaceable"><code>N</code></em></code> <code class="type">float4[]</code>119 </p>120 <p>121 Numerical statistics of the appropriate kind for the122 <em class="replaceable"><code>N</code></em>th <span class="quote">“<span class="quote">slot</span>”</span>, or null if the slot123 kind does not involve numerical values124 </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">125 <code class="structfield">stavalues<em class="replaceable"><code>N</code></em></code> <code class="type">anyarray</code>126 </p>127 <p>128 Column data values of the appropriate kind for the129 <em class="replaceable"><code>N</code></em>th <span class="quote">“<span class="quote">slot</span>”</span>, or null if the slot130 kind does not store any data values. Each array's element131 values are actually of the specific column's data type, or a related132 type such as an array's element type, so there is no way to define133 these columns' type more specifically than <code class="type">anyarray</code>.134 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="catalog-pg-shseclabel.html" title="53.50. pg_shseclabel">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="catalogs.html" title="Chapter 53. System Catalogs">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="catalog-pg-statistic-ext.html" title="53.52. pg_statistic_ext">Next</a></td></tr><tr><td width="40%" align="left" valign="top">53.50. <code class="structname">pg_shseclabel</code> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 53.52. <code class="structname">pg_statistic_ext</code></td></tr></table></div></body></html>