codekingpro/portable-devtools
115k
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 => 5": 1.000000, "5 => 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>