Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
planner-stats.html336 linesDownload Raw Back to html
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>14.2. Statistics Used by the Planner</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="using-explain.html" title="14.1. Using EXPLAIN" /><link rel="next" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">14.2. Statistics Used by the Planner</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="using-explain.html" title="14.1. Using EXPLAIN">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="performance-tips.html" title="Chapter 14. Performance Tips">Up</a></td><th width="60%" align="center">Chapter 14. Performance Tips</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="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLANNER-STATS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">14.2. Statistics Used by the Planner <a href="#PLANNER-STATS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="planner-stats.html#PLANNER-STATS-SINGLE-COLUMN">14.2.1. Single-Column Statistics</a></span></dt><dt><span class="sect2"><a href="planner-stats.html#PLANNER-STATS-EXTENDED">14.2.2. Extended Statistics</a></span></dt></dl></div><a id="id-1.5.13.5.2" class="indexterm"></a><div class="sect2" id="PLANNER-STATS-SINGLE-COLUMN"><div class="titlepage"><div><div><h3 class="title">14.2.1. Single-Column Statistics <a href="#PLANNER-STATS-SINGLE-COLUMN" class="id_link">#</a></h3></div></div></div><p>3   As we saw in the previous section, the query planner needs to estimate4   the number of rows retrieved by a query in order to make good choices5   of query plans.  This section provides a quick look at the statistics6   that the system uses for these estimates.7  </p><p>8   One component of the statistics is the total number of entries in9   each table and index, as well as the number of disk blocks occupied10   by each table and index.  This information is kept in the table11   <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>,12   in the columns <code class="structfield">reltuples</code> and13   <code class="structfield">relpages</code>.  We can look at it with14   queries similar to this one:15 16</p><pre class="screen">17SELECT relname, relkind, reltuples, relpages18FROM pg_class19WHERE relname LIKE 'tenk1%';20 21       relname        | relkind | reltuples | relpages22----------------------+---------+-----------+----------23 tenk1                | r       |     10000 |      35824 tenk1_hundred        | i       |     10000 |       3025 tenk1_thous_tenthous | i       |     10000 |       3026 tenk1_unique1        | i       |     10000 |       3027 tenk1_unique2        | i       |     10000 |       3028(5 rows)29</pre><p>30 31   Here we can see that <code class="structname">tenk1</code> contains 1000032   rows, as do its indexes, but the indexes are (unsurprisingly) much33   smaller than the table.34  </p><p>35   For efficiency reasons, <code class="structfield">reltuples</code>36   and <code class="structfield">relpages</code> are not updated on-the-fly,37   and so they usually contain somewhat out-of-date values.38   They are updated by <code class="command">VACUUM</code>, <code class="command">ANALYZE</code>, and a39   few DDL commands such as <code class="command">CREATE INDEX</code>.  A <code class="command">VACUUM</code>40   or <code class="command">ANALYZE</code> operation that does not scan the entire table41   (which is commonly the case) will incrementally update the42   <code class="structfield">reltuples</code> count on the basis of the part43   of the table it did scan, resulting in an approximate value.44   In any case, the planner45   will scale the values it finds in <code class="structname">pg_class</code>46   to match the current physical table size, thus obtaining a closer47   approximation.48  </p><a id="id-1.5.13.5.3.5" class="indexterm"></a><p>49   Most queries retrieve only a fraction of the rows in a table, due50   to <code class="literal">WHERE</code> clauses that restrict the rows to be51   examined.  The planner thus needs to make an estimate of the52   <em class="firstterm">selectivity</em> of <code class="literal">WHERE</code> clauses, that is,53   the fraction of rows that match each condition in the54   <code class="literal">WHERE</code> clause.  The information used for this task is55   stored in the56   <a class="link" href="catalog-pg-statistic.html" title="53.51. pg_statistic"><code class="structname">pg_statistic</code></a>57   system catalog.  Entries in <code class="structname">pg_statistic</code>58   are updated by the <code class="command">ANALYZE</code> and <code class="command">VACUUM59   ANALYZE</code> commands, and are always approximate even when freshly60   updated.61  </p><a id="id-1.5.13.5.3.7" class="indexterm"></a><p>62   Rather than look at <code class="structname">pg_statistic</code> directly,63   it's better to look at its view64   <a class="link" href="view-pg-stats.html" title="54.27. pg_stats"><code class="structname">pg_stats</code></a>65   when examining the statistics manually.  <code class="structname">pg_stats</code>66   is designed to be more easily readable.  Furthermore,67   <code class="structname">pg_stats</code> is readable by all, whereas68   <code class="structname">pg_statistic</code> is only readable by a superuser.69   (This prevents unprivileged users from learning something about70   the contents of other people's tables from the statistics.  The71   <code class="structname">pg_stats</code> view is restricted to show only72   rows about tables that the current user can read.)73   For example, we might do:74 75</p><pre class="screen">76SELECT attname, inherited, n_distinct,77       array_to_string(most_common_vals, E'\n') as most_common_vals78FROM pg_stats79WHERE tablename = 'road';80 81 attname | inherited | n_distinct |          most_common_vals82---------+-----------+------------+------------------------------------83 name    | f         |  -0.363388 | I- 580                        Ramp+84         |           |            | I- 880                        Ramp+85         |           |            | Sp Railroad                       +86         |           |            | I- 580                            +87         |           |            | I- 680                        Ramp88 name    | t         |  -0.284859 | I- 880                        Ramp+89         |           |            | I- 580                        Ramp+90         |           |            | I- 680                        Ramp+91         |           |            | I- 580                            +92         |           |            | State Hwy 13                  Ramp93(2 rows)94</pre><p>95 96   Note that two rows are displayed for the same column, one corresponding97   to the complete inheritance hierarchy starting at the98   <code class="literal">road</code> table (<code class="literal">inherited</code>=<code class="literal">t</code>),99   and another one including only the <code class="literal">road</code> table itself100   (<code class="literal">inherited</code>=<code class="literal">f</code>).101  </p><p>102   The amount of information stored in <code class="structname">pg_statistic</code>103   by <code class="command">ANALYZE</code>, in particular the maximum number of entries in the104   <code class="structfield">most_common_vals</code> and <code class="structfield">histogram_bounds</code>105   arrays for each column, can be set on a106   column-by-column basis using the <code class="command">ALTER TABLE SET STATISTICS</code>107   command, or globally by setting the108   <a class="xref" href="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET">default_statistics_target</a> configuration variable.109   The default limit is presently 100 entries.  Raising the limit110   might allow more accurate planner estimates to be made, particularly for111   columns with irregular data distributions, at the price of consuming112   more space in <code class="structname">pg_statistic</code> and slightly more113   time to compute the estimates.  Conversely, a lower limit might be114   sufficient for columns with simple data distributions.115  </p><p>116   Further details about the planner's use of statistics can be found in117   <a class="xref" href="planner-stats-details.html" title="Chapter 76. How the Planner Uses Statistics">Chapter 76</a>.118  </p></div><div class="sect2" id="PLANNER-STATS-EXTENDED"><div class="titlepage"><div><div><h3 class="title">14.2.2. Extended Statistics <a href="#PLANNER-STATS-EXTENDED" class="id_link">#</a></h3></div></div></div><a id="id-1.5.13.5.4.2" class="indexterm"></a><a id="id-1.5.13.5.4.3" class="indexterm"></a><a id="id-1.5.13.5.4.4" class="indexterm"></a><a id="id-1.5.13.5.4.5" class="indexterm"></a><p>119    It is common to see slow queries running bad execution plans because120    multiple columns used in the query clauses are correlated.121    The planner normally assumes that multiple conditions122    are independent of each other,123    an assumption that does not hold when column values are correlated.124    Regular statistics, because of their per-individual-column nature,125    cannot capture any knowledge about cross-column correlation.126    However, <span class="productname">PostgreSQL</span> has the ability to compute127    <em class="firstterm">multivariate statistics</em>, which can capture128    such information.129   </p><p>130    Because the number of possible column combinations is very large,131    it's impractical to compute multivariate statistics automatically.132    Instead, <em class="firstterm">extended statistics objects</em>, more often133    called just <em class="firstterm">statistics objects</em>, can be created to instruct134    the server to obtain statistics across interesting sets of columns.135   </p><p>136    Statistics objects are created using the137    <a class="link" href="sql-createstatistics.html" title="CREATE STATISTICS"><code class="command">CREATE STATISTICS</code></a> command.138    Creation of such an object merely creates a catalog entry expressing139    interest in the statistics.  Actual data collection is performed140    by <code class="command">ANALYZE</code> (either a manual command, or background141    auto-analyze).  The collected values can be examined in the142    <a class="link" href="catalog-pg-statistic-ext-data.html" title="53.53. pg_statistic_ext_data"><code class="structname">pg_statistic_ext_data</code></a>143    catalog.144   </p><p>145    <code class="command">ANALYZE</code> computes extended statistics based on the same146    sample of table rows that it takes for computing regular single-column147    statistics.  Since the sample size is increased by increasing the148    statistics target for the table or any of its columns (as described in149    the previous section), a larger statistics target will normally result in150    more accurate extended statistics, as well as more time spent calculating151    them.152   </p><p>153    The following subsections describe the kinds of extended statistics154    that are currently supported.155   </p><div class="sect3" id="PLANNER-STATS-EXTENDED-FUNCTIONAL-DEPS"><div class="titlepage"><div><div><h4 class="title">14.2.2.1. Functional Dependencies <a href="#PLANNER-STATS-EXTENDED-FUNCTIONAL-DEPS" class="id_link">#</a></h4></div></div></div><p>156     The simplest kind of extended statistics tracks <em class="firstterm">functional157     dependencies</em>, a concept used in definitions of database normal forms.158     We say that column <code class="structfield">b</code> is functionally dependent on159     column <code class="structfield">a</code> if knowledge of the value of160     <code class="structfield">a</code> is sufficient to determine the value161     of <code class="structfield">b</code>, that is there are no two rows having the same value162     of <code class="structfield">a</code> but different values of <code class="structfield">b</code>.163     In a fully normalized database, functional dependencies should exist164     only on primary keys and superkeys. However, in practice many data sets165     are not fully normalized for various reasons; intentional166     denormalization for performance reasons is a common example.167     Even in a fully normalized database, there may be partial correlation168     between some columns, which can be expressed as partial functional169     dependency.170    </p><p>171     The existence of functional dependencies directly affects the accuracy172     of estimates in certain queries.  If a query contains conditions on173     both the independent and the dependent column(s), the174     conditions on the dependent columns do not further reduce the result175     size; but without knowledge of the functional dependency, the query176     planner will assume that the conditions are independent, resulting177     in underestimating the result size.178    </p><p>179     To inform the planner about functional dependencies, <code class="command">ANALYZE</code>180     can collect measurements of cross-column dependency. Assessing the181     degree of dependency between all sets of columns would be prohibitively182     expensive, so data collection is limited to those groups of columns183     appearing together in a statistics object defined with184     the <code class="literal">dependencies</code> option.  It is advisable to create185     <code class="literal">dependencies</code> statistics only for column groups that are186     strongly correlated, to avoid unnecessary overhead in both187     <code class="command">ANALYZE</code> and later query planning.188    </p><p>189     Here is an example of collecting functional-dependency statistics:190</p><pre class="programlisting">191CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;192 193ANALYZE zipcodes;194 195SELECT stxname, stxkeys, stxddependencies196  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)197  WHERE stxname = 'stts';198 stxname | stxkeys |             stxddependencies199---------+---------+------------------------------------------200 stts    | 1 5     | {"1 =&gt; 5": 1.000000, "5 =&gt; 1": 0.423130}201(1 row)202</pre><p>203     Here it can be seen that column 1 (zip code) fully determines column204     5 (city) so the coefficient is 1.0, while city only determines zip code205     about 42% of the time, meaning that there are many cities (58%) that are206     represented by more than a single ZIP code.207    </p><p>208     When computing the selectivity for a query involving functionally209     dependent columns, the planner adjusts the per-condition selectivity210     estimates using the dependency coefficients so as not to produce211     an underestimate.212    </p><div class="sect4" id="PLANNER-STATS-EXTENDED-FUNCTIONAL-DEPS-LIMITS"><div class="titlepage"><div><div><h5 class="title">14.2.2.1.1. Limitations of Functional Dependencies <a href="#PLANNER-STATS-EXTENDED-FUNCTIONAL-DEPS-LIMITS" class="id_link">#</a></h5></div></div></div><p>213      Functional dependencies are currently only applied when considering214      simple equality conditions that compare columns to constant values,215      and <code class="literal">IN</code> clauses with constant values.216      They are not used to improve estimates for equality conditions217      comparing two columns or comparing a column to an expression, nor for218      range clauses, <code class="literal">LIKE</code> or any other type of condition.219     </p><p>220      When estimating with functional dependencies, the planner assumes that221      conditions on the involved columns are compatible and hence redundant.222      If they are incompatible, the correct estimate would be zero rows, but223      that possibility is not considered.  For example, given a query like224</p><pre class="programlisting">225SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';226</pre><p>227      the planner will disregard the <code class="structfield">city</code> clause as not228      changing the selectivity, which is correct.  However, it will make229      the same assumption about230</p><pre class="programlisting">231SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';232</pre><p>233      even though there will really be zero rows satisfying this query.234      Functional dependency statistics do not provide enough information235      to conclude that, however.236     </p><p>237      In many practical situations, this assumption is usually satisfied;238      for example, there might be a GUI in the application that only allows239      selecting compatible city and ZIP code values to use in a query.240      But if that's not the case, functional dependencies may not be a viable241      option.242     </p></div></div><div class="sect3" id="PLANNER-STATS-EXTENDED-N-DISTINCT-COUNTS"><div class="titlepage"><div><div><h4 class="title">14.2.2.2. Multivariate N-Distinct Counts <a href="#PLANNER-STATS-EXTENDED-N-DISTINCT-COUNTS" class="id_link">#</a></h4></div></div></div><p>243     Single-column statistics store the number of distinct values in each244     column.  Estimates of the number of distinct values when combining more245     than one column (for example, for <code class="literal">GROUP BY a, b</code>) are246     frequently wrong when the planner only has single-column statistical247     data, causing it to select bad plans.248    </p><p>249     To improve such estimates, <code class="command">ANALYZE</code> can collect n-distinct250     statistics for groups of columns.  As before, it's impractical to do251     this for every possible column grouping, so data is collected only for252     those groups of columns appearing together in a statistics object253     defined with the <code class="literal">ndistinct</code> option.  Data will be collected254     for each possible combination of two or more columns from the set of255     listed columns.256    </p><p>257     Continuing the previous example, the n-distinct counts in a258     table of ZIP codes might look like the following:259</p><pre class="programlisting">260CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;261 262ANALYZE zipcodes;263 264SELECT stxkeys AS k, stxdndistinct AS nd265  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)266  WHERE stxname = 'stts2';267-[ RECORD 1 ]------------------------------------------------------​--268k  | 1 2 5269nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}270(1 row)271</pre><p>272     This indicates that there are three combinations of columns that273     have 33178 distinct values: ZIP code and state; ZIP code and city;274     and ZIP code, city and state (the fact that they are all equal is275     expected given that ZIP code alone is unique in this table).  On the276     other hand, the combination of city and state has only 27435 distinct277     values.278    </p><p>279     It's advisable to create <code class="literal">ndistinct</code> statistics objects only280     on combinations of columns that are actually used for grouping, and281     for which misestimation of the number of groups is resulting in bad282     plans.  Otherwise, the <code class="command">ANALYZE</code> cycles are just wasted.283    </p></div><div class="sect3" id="PLANNER-STATS-EXTENDED-MCV-LISTS"><div class="titlepage"><div><div><h4 class="title">14.2.2.3. Multivariate MCV Lists <a href="#PLANNER-STATS-EXTENDED-MCV-LISTS" class="id_link">#</a></h4></div></div></div><p>284     Another type of statistic stored for each column are most-common value285     lists.  This allows very accurate estimates for individual columns, but286     may result in significant misestimates for queries with conditions on287     multiple columns.288    </p><p>289     To improve such estimates, <code class="command">ANALYZE</code> can collect MCV290     lists on combinations of columns.  Similarly to functional dependencies291     and n-distinct coefficients, it's impractical to do this for every292     possible column grouping.  Even more so in this case, as the MCV list293     (unlike functional dependencies and n-distinct coefficients) does store294     the common column values.  So data is collected only for those groups295     of columns appearing together in a statistics object defined with the296     <code class="literal">mcv</code> option.297    </p><p>298     Continuing the previous example, the MCV list for a table of ZIP codes299     might look like the following (unlike for simpler types of statistics,300     a function is required for inspection of MCV contents):301 302</p><pre class="programlisting">303CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;304 305ANALYZE zipcodes;306 307SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),308                pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';309 310 index |         values         | nulls | frequency | base_frequency311-------+------------------------+-------+-----------+----------------312     0 | {Washington, DC}       | {f,f} |  0.003467 |        2.7e-05313     1 | {Apo, AE}              | {f,f} |  0.003067 |        1.9e-05314     2 | {Houston, TX}          | {f,f} |  0.002167 |       0.000133315     3 | {El Paso, TX}          | {f,f} |     0.002 |       0.000113316     4 | {New York, NY}         | {f,f} |  0.001967 |       0.000114317     5 | {Atlanta, GA}          | {f,f} |  0.001633 |        3.3e-05318     6 | {Sacramento, CA}       | {f,f} |  0.001433 |        7.8e-05319     7 | {Miami, FL}            | {f,f} |    0.0014 |          6e-05320     8 | {Dallas, TX}           | {f,f} |  0.001367 |        8.8e-05321     9 | {Chicago, IL}          | {f,f} |  0.001333 |        5.1e-05322   ...323(99 rows)324</pre><p>325     This indicates that the most common combination of city and state is326     Washington in DC, with actual frequency (in the sample) about 0.35%.327     The base frequency of the combination (as computed from the simple328     per-column frequencies) is only 0.0027%, resulting in two orders of329     magnitude under-estimates.330    </p><p>331     It's advisable to create <acronym class="acronym">MCV</acronym> statistics objects only332     on combinations of columns that are actually used in conditions together,333     and for which misestimation of the number of groups is resulting in bad334     plans.  Otherwise, the <code class="command">ANALYZE</code> and planning cycles335     are just wasted.336    </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="using-explain.html" title="14.1. Using EXPLAIN">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="performance-tips.html" title="Chapter 14. Performance Tips">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses">Next</a></td></tr><tr><td width="40%" align="left" valign="top">14.1. Using <code class="command">EXPLAIN</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"> 14.3. Controlling the Planner with Explicit <code class="literal">JOIN</code> Clauses</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai