diff options
Diffstat (limited to 'doc/src/sgml/html/sql-analyze.html')
| -rw-r--r-- | doc/src/sgml/html/sql-analyze.html | 187 |
1 files changed, 187 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-analyze.html b/doc/src/sgml/html/sql-analyze.html new file mode 100644 index 0000000..745addb --- /dev/null +++ b/doc/src/sgml/html/sql-analyze.html @@ -0,0 +1,187 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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>ANALYZE</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="sql-alterview.html" title="ALTER VIEW" /><link rel="next" href="sql-begin.html" title="BEGIN" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ANALYZE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterview.html" title="ALTER VIEW">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-begin.html" title="BEGIN">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ANALYZE"><div class="titlepage"></div><a id="id-1.9.3.46.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ANALYZE</span></h2><p>ANALYZE — collect statistics about a database</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +ANALYZE [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] [ <em class="replaceable"><code>table_and_columns</code></em> [, ...] ] +ANALYZE [ VERBOSE ] [ <em class="replaceable"><code>table_and_columns</code></em> [, ...] ] + +<span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span> + + VERBOSE [ <em class="replaceable"><code>boolean</code></em> ] + SKIP_LOCKED [ <em class="replaceable"><code>boolean</code></em> ] + +<span class="phrase">and <em class="replaceable"><code>table_and_columns</code></em> is:</span> + + <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] +</pre></div><div class="refsect1" id="id-1.9.3.46.5"><h2>Description</h2><p> + <code class="command">ANALYZE</code> collects statistics about the contents + of tables in the database, and stores the results in the <a class="link" href="catalog-pg-statistic.html" title="53.51. pg_statistic"><code class="structname">pg_statistic</code></a> + system catalog. Subsequently, the query planner uses these + statistics to help determine the most efficient execution plans for + queries. + </p><p> + Without a <em class="replaceable"><code>table_and_columns</code></em> + list, <code class="command">ANALYZE</code> processes every table and materialized view + in the current database that the current user has permission to analyze. + With a list, <code class="command">ANALYZE</code> processes only those table(s). + It is further possible to give a list of column names for a table, + in which case only the statistics for those columns are collected. + </p><p> + When the option list is surrounded by parentheses, the options can be + written in any order. The parenthesized syntax was added in + <span class="productname">PostgreSQL</span> 11; the unparenthesized syntax + is deprecated. + </p></div><div class="refsect1" id="id-1.9.3.46.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">VERBOSE</code></span></dt><dd><p> + Enables display of progress messages. + </p></dd><dt><span class="term"><code class="literal">SKIP_LOCKED</code></span></dt><dd><p> + Specifies that <code class="command">ANALYZE</code> should not wait for any + conflicting locks to be released when beginning work on a relation: + if a relation cannot be locked immediately without waiting, the relation + is skipped. Note that even with this option, <code class="command">ANALYZE</code> + may still block when opening the relation's indexes or when acquiring + sample rows from partitions, table inheritance children, and some + types of foreign tables. Also, while <code class="command">ANALYZE</code> + ordinarily processes all partitions of specified partitioned tables, + this option will cause <code class="command">ANALYZE</code> to skip all + partitions if there is a conflicting lock on the partitioned table. + </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p> + Specifies whether the selected option should be turned on or off. + You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or + <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>, + <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it. The + <em class="replaceable"><code>boolean</code></em> value can also + be omitted, in which case <code class="literal">TRUE</code> is assumed. + </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p> + The name (possibly schema-qualified) of a specific table to + analyze. If omitted, all regular tables, partitioned tables, and + materialized views in the current database are analyzed (but not + foreign tables). If the specified table is a partitioned table, both the + inheritance statistics of the partitioned table as a whole and + statistics of the individual partitions are updated. + </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p> + The name of a specific column to analyze. Defaults to all columns. + </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.46.7"><h2>Outputs</h2><p> + When <code class="literal">VERBOSE</code> is specified, <code class="command">ANALYZE</code> emits + progress messages to indicate which table is currently being + processed. Various statistics about the tables are printed as well. + </p></div><div class="refsect1" id="id-1.9.3.46.8"><h2>Notes</h2><p> + To analyze a table, one must ordinarily be the table's owner or a + superuser. However, database owners are allowed to + analyze all tables in their databases, except shared catalogs. + (The restriction for shared catalogs means that a true database-wide + <code class="command">ANALYZE</code> can only be performed by a superuser.) + <code class="command">ANALYZE</code> will skip over any tables that the calling user + does not have permission to analyze. + </p><p> + Foreign tables are analyzed only when explicitly selected. Not all + foreign data wrappers support <code class="command">ANALYZE</code>. If the table's + wrapper does not support <code class="command">ANALYZE</code>, the command prints a + warning and does nothing. + </p><p> + In the default <span class="productname">PostgreSQL</span> configuration, + the autovacuum daemon (see <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a>) + takes care of automatic analyzing of tables when they are first loaded + with data, and as they change throughout regular operation. + When autovacuum is disabled, + it is a good idea to run <code class="command">ANALYZE</code> periodically, or + just after making major changes in the contents of a table. Accurate + statistics will help the planner to choose the most appropriate query + plan, and thereby improve the speed of query processing. A common + strategy for read-mostly databases is to run <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a> + and <code class="command">ANALYZE</code> once a day during a low-usage time of day. + (This will not be sufficient if there is heavy update activity.) + </p><p> + <code class="command">ANALYZE</code> + requires only a read lock on the target table, so it can run in + parallel with other activity on the table. + </p><p> + The statistics collected by <code class="command">ANALYZE</code> usually + include a list of some of the most common values in each column and + a histogram showing the approximate data distribution in each + column. One or both of these can be omitted if + <code class="command">ANALYZE</code> deems them uninteresting (for example, + in a unique-key column, there are no common values) or if the + column data type does not support the appropriate operators. There + is more information about the statistics in <a class="xref" href="maintenance.html" title="Chapter 25. Routine Database Maintenance Tasks">Chapter 25</a>. + </p><p> + For large tables, <code class="command">ANALYZE</code> takes a random sample + of the table contents, rather than examining every row. This + allows even very large tables to be analyzed in a small amount of + time. Note, however, that the statistics are only approximate, and + will change slightly each time <code class="command">ANALYZE</code> is run, + even if the actual table contents did not change. This might result + in small changes in the planner's estimated costs shown by + <a class="link" href="sql-explain.html" title="EXPLAIN"><code class="command">EXPLAIN</code></a>. + In rare situations, this non-determinism will cause the planner's + choices of query plans to change after <code class="command">ANALYZE</code> is run. + To avoid this, raise the amount of statistics collected by + <code class="command">ANALYZE</code>, as described below. + </p><p> + The extent of analysis can be controlled by adjusting the + <a class="xref" href="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET">default_statistics_target</a> configuration variable, or + on a column-by-column basis by setting the per-column statistics + target with <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE ... ALTER COLUMN ... SET + STATISTICS</code></a>. + The target value sets the + maximum number of entries in the most-common-value list and the + maximum number of bins in the histogram. The default target value + is 100, but this can be adjusted up or down to trade off accuracy of + planner estimates against the time taken for + <code class="command">ANALYZE</code> and the amount of space occupied in + <code class="literal">pg_statistic</code>. In particular, setting the + statistics target to zero disables collection of statistics for + that column. It might be useful to do that for columns that are + never used as part of the <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, + or <code class="literal">ORDER BY</code> clauses of queries, since the planner will + have no use for statistics on such columns. + </p><p> + The largest statistics target among the columns being analyzed determines + the number of table rows sampled to prepare the statistics. Increasing + the target causes a proportional increase in the time and space needed + to do <code class="command">ANALYZE</code>. + </p><p> + One of the values estimated by <code class="command">ANALYZE</code> is the number of + distinct values that appear in each column. Because only a subset of the + rows are examined, this estimate can sometimes be quite inaccurate, even + with the largest possible statistics target. If this inaccuracy leads to + bad query plans, a more accurate value can be determined manually and then + installed with + <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)</code></a>. + </p><p> + If the table being analyzed has inheritance children, + <code class="command">ANALYZE</code> gathers two sets of statistics: one on the rows + of the parent table only, and a second including rows of both the parent + table and all of its children. This second set of statistics is needed when + planning queries that process the inheritance tree as a whole. The child + tables themselves are not individually analyzed in this case. + The autovacuum daemon, however, will only consider inserts or + updates on the parent table itself when deciding whether to trigger an + automatic analyze for that table. If that table is rarely inserted into + or updated, the inheritance statistics will not be up to date unless you + run <code class="command">ANALYZE</code> manually. + </p><p> + For partitioned tables, <code class="command">ANALYZE</code> gathers statistics by + sampling rows from all partitions; in addition, it will recurse into each + partition and update its statistics. Each leaf partition is analyzed only + once, even with multi-level partitioning. No statistics are collected for + only the parent table (without data from its partitions), because with + partitioning it's guaranteed to be empty. + </p><p> + The autovacuum daemon does not process partitioned tables, nor does it + process inheritance parents if only the children are ever modified. + It is usually necessary to periodically run a manual + <code class="command">ANALYZE</code> to keep the statistics of the table hierarchy + up to date. + </p><p> + If any child tables or partitions are foreign tables whose foreign + data wrappers do not support <code class="command">ANALYZE</code>, those tables are + ignored while gathering inheritance statistics. + </p><p> + If the table being analyzed is completely empty, <code class="command">ANALYZE</code> + will not record new statistics for that table. Any existing statistics + will be retained. + </p><p> + Each backend running <code class="command">ANALYZE</code> will report its progress + in the <code class="structname">pg_stat_progress_analyze</code> view. See + <a class="xref" href="progress-reporting.html#ANALYZE-PROGRESS-REPORTING" title="28.4.1. ANALYZE Progress Reporting">Section 28.4.1</a> for details. + </p></div><div class="refsect1" id="id-1.9.3.46.9"><h2>Compatibility</h2><p> + There is no <code class="command">ANALYZE</code> statement in the SQL standard. + </p></div><div class="refsect1" id="id-1.9.3.46.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>, <a class="xref" href="app-vacuumdb.html" title="vacuumdb"><span class="refentrytitle"><span class="application">vacuumdb</span></span></a>, <a class="xref" href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" title="20.4.4. Cost-based Vacuum Delay">Section 20.4.4</a>, <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a>, <a class="xref" href="progress-reporting.html#ANALYZE-PROGRESS-REPORTING" title="28.4.1. ANALYZE Progress Reporting">Section 28.4.1</a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterview.html" title="ALTER VIEW">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-begin.html" title="BEGIN">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER VIEW </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> BEGIN</td></tr></table></div></body></html>
\ No newline at end of file |