diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 70 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/perform.sgml | 20 | ||||
| -rw-r--r-- | doc/src/sgml/planstats.sgml | 4 |
4 files changed, 76 insertions, 24 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 36193d14910..ef4345524ad 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -297,7 +297,12 @@ <row> <entry><link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link></entry> - <entry>extended planner statistics</entry> + <entry>extended planner statistics (definition)</entry> + </row> + + <row> + <entry><link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link></entry> + <entry>extended planner statistics (built statistics)</entry> </row> <row> @@ -6506,7 +6511,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <para> The catalog <structname>pg_statistic_ext</structname> - holds extended planner statistics. + holds definitions of extended planner statistics. Each row in this catalog corresponds to a <firstterm>statistics object</firstterm> created with <xref linkend="sql-createstatistics"/>. </para> @@ -6581,8 +6586,57 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </entry> </row> + </tbody> + </tgroup> + </table> + + <para> + The <structfield>stxkind</structfield> field is filled at creation of the + statistics object, indicating which statistic type(s) are desired. The + statistics (once computed by <command>ANALYZE</command>) are stored in + <link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link> + catalog. + </para> + </sect1> + + <sect1 id="catalog-pg-statistic-ext-data"> + <title><structname>pg_statistic_ext_data</structname></title> + + <indexterm zone="catalog-pg-statistic-ext"> + <primary>pg_statistic_ext_data</primary> + </indexterm> + + <para> + The catalog <structname>pg_statistic_ext_data</structname> + holds data for extended planner statistics defined in <structname>pg_statistic_ext</structname>. + Each row in this catalog corresponds to a <firstterm>statistics object</firstterm> + created with <xref linkend="sql-createstatistics"/>. + </para> + + <table> + <title><structname>pg_statistic_ext_data</structname> Columns</title> + + <tgroup cols="4"> + <thead> + <row> + <entry>Name</entry> + <entry>Type</entry> + <entry>References</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + + <row> + <entry><structfield>stxoid</structfield></entry> + <entry><type>oid</type></entry> + <entry><literal><link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.oid</literal></entry> + <entry>Extended statistic containing the definition for this data.</entry> + </row> + <row> - <entry><structfield>stxndistinct</structfield></entry> + <entry><structfield>stxdndistinct</structfield></entry> <entry><type>pg_ndistinct</type></entry> <entry></entry> <entry> @@ -6591,7 +6645,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </row> <row> - <entry><structfield>stxdependencies</structfield></entry> + <entry><structfield>stxddependencies</structfield></entry> <entry><type>pg_dependencies</type></entry> <entry></entry> <entry> @@ -6601,7 +6655,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </row> <row> - <entry><structfield>stxmcv</structfield></entry> + <entry><structfield>stxdmcv</structfield></entry> <entry><type>pg_mcv_list</type></entry> <entry></entry> <entry> @@ -6614,12 +6668,6 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </tgroup> </table> - <para> - The <structfield>stxkind</structfield> field is filled at creation of the - statistics object, indicating which statistic type(s) are desired. - The fields after it are initially NULL and are filled only when the - corresponding statistic has been computed by <command>ANALYZE</command>. - </para> </sect1> <sect1 id="catalog-pg-subscription"> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index a072b976161..e9181338742 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -22427,12 +22427,12 @@ CREATE EVENT TRIGGER test_table_rewrite_oid The <function>pg_mcv_list_items</function> function can be used like this: <programlisting> -SELECT m.* FROM pg_statistic_ext, - pg_mcv_list_items(stxmcv) m WHERE stxname = 'stts'; +SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), + pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts'; </programlisting> Values of the <type>pg_mcv_list</type> can be obtained only from the - <literal>pg_statistic_ext.stxmcv</literal> column. + <literal>pg_statistic_ext_data.stxdmcv</literal> column. </para> </sect2> diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index a84be851593..8e165832b35 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -1076,6 +1076,10 @@ WHERE tablename = 'road'; <primary>pg_statistic_ext</primary> </indexterm> + <indexterm> + <primary>pg_statistic_ext_data</primary> + </indexterm> + <para> It is common to see slow queries running bad execution plans because multiple columns used in the query clauses are correlated. @@ -1104,7 +1108,7 @@ WHERE tablename = 'road'; interest in the statistics. Actual data collection is performed by <command>ANALYZE</command> (either a manual command, or background auto-analyze). The collected values can be examined in the - <link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link> + <link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link> catalog. </para> @@ -1172,10 +1176,10 @@ CREATE STATISTICS stts (dependencies) ON zip, city FROM zipcodes; ANALYZE zipcodes; -SELECT stxname, stxkeys, stxdependencies - FROM pg_statistic_ext +SELECT stxname, stxkeys, stxddependencies + FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts'; - stxname | stxkeys | stxdependencies + stxname | stxkeys | stxddependencies ---------+---------+------------------------------------------ stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130} (1 row) @@ -1262,8 +1266,8 @@ CREATE STATISTICS stts2 (ndistinct) ON zip, state, city FROM zipcodes; ANALYZE zipcodes; -SELECT stxkeys AS k, stxndistinct AS nd - FROM pg_statistic_ext +SELECT stxkeys AS k, stxdndistinct AS nd + FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts2'; -[ RECORD 1 ]-------------------------------------------------------- k | 1 2 5 @@ -1317,8 +1321,8 @@ CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes; ANALYZE zipcodes; -SELECT m.* FROM pg_statistic_ext, - pg_mcv_list_items(stxmcv) m WHERE stxname = 'stts3'; +SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), + pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3'; index | values | nulls | frequency | base_frequency -------+------------------------+-------+-----------+---------------- diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml index 4b1d3f4952e..a25ce152ac2 100644 --- a/doc/src/sgml/planstats.sgml +++ b/doc/src/sgml/planstats.sgml @@ -635,8 +635,8 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1; <function>pg_mcv_list_items</function> set-returning function. <programlisting> -SELECT m.* FROM pg_statistic_ext, - pg_mcv_list_items(stxmcv) m WHERE stxname = 'stts2'; +SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), + pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts2'; index | values | nulls | frequency | base_frequency -------+----------+-------+-----------+---------------- 0 | {0, 0} | {f,f} | 0.01 | 0.0001 |