Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
關註 1
收藏 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
分支: master
分支列表 標籤列表
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/sql-analyze.html

170 line
14KB
原始文件 永久連結 Blame 文件歷史 

  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>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 V1.79.1" /><link rel="prev" href="sql-alterview.html" title="ALTER VIEW" /><link rel="next" href="sql-begin.html" title="BEGIN" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-begin.html" title="BEGIN">Next</a></td></tr></table><hr></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">

  3. ANALYZE [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ] [ <em class="replaceable"><code>table_and_columns</code></em> [, ...] ]

  4. ANALYZE [ VERBOSE ] [ <em class="replaceable"><code>table_and_columns</code></em> [, ...] ]

  5. 

  6. <span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span>

  7. 

  8.     VERBOSE [ <em class="replaceable"><code>boolean</code></em> ]

  9.     SKIP_LOCKED [ <em class="replaceable"><code>boolean</code></em> ]

  10. 

  11. <span class="phrase">and <em class="replaceable"><code>table_and_columns</code></em> is:</span>

  12. 

  13.     <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ]

  14. </pre></div><div class="refsect1" id="id-1.9.3.46.5"><h2>Description</h2><p>

  15.    <code class="command">ANALYZE</code> collects statistics about the contents

  16.    of tables in the database, and stores the results in the <a class="link" href="catalog-pg-statistic.html" title="51.50. pg_statistic"><code class="structname">pg_statistic</code></a>

  17.    system catalog.  Subsequently, the query planner uses these

  18.    statistics to help determine the most efficient execution plans for

  19.    queries.

  20.   </p><p>

  21.    Without a <em class="replaceable"><code>table_and_columns</code></em>

  22.    list, <code class="command">ANALYZE</code> processes every table and materialized view

  23.    in the current database that the current user has permission to analyze.

  24.    With a list, <code class="command">ANALYZE</code> processes only those table(s).

  25.    It is further possible to give a list of column names for a table,

  26.    in which case only the statistics for those columns are collected.

  27.   </p><p>

  28.    When the option list is surrounded by parentheses, the options can be

  29.    written in any order.  The parenthesized syntax was added in

  30.    <span class="productname">PostgreSQL</span> 11;  the unparenthesized syntax

  31.    is deprecated.

  32.   </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>

  33.       Enables display of progress messages.

  34.      </p></dd><dt><span class="term"><code class="literal">SKIP_LOCKED</code></span></dt><dd><p>

  35.       Specifies that <code class="command">ANALYZE</code> should not wait for any

  36.       conflicting locks to be released when beginning work on a relation:

  37.       if a relation cannot be locked immediately without waiting, the relation

  38.       is skipped.  Note that even with this option, <code class="command">ANALYZE</code>

  39.       may still block when opening the relation's indexes or when acquiring

  40.       sample rows from partitions, table inheritance children, and some

  41.       types of foreign tables.  Also, while <code class="command">ANALYZE</code>

  42.       ordinarily processes all partitions of specified partitioned tables,

  43.       this option will cause <code class="command">ANALYZE</code> to skip all

  44.       partitions if there is a conflicting lock on the partitioned table.

  45.      </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p>

  46.       Specifies whether the selected option should be turned on or off.

  47.       You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or

  48.       <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>,

  49.       <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it.  The

  50.       <em class="replaceable"><code>boolean</code></em> value can also

  51.       be omitted, in which case <code class="literal">TRUE</code> is assumed.

  52.      </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>

  53.       The name (possibly schema-qualified) of a specific table to

  54.       analyze.  If omitted, all regular tables, partitioned tables, and

  55.       materialized views in the current database are analyzed (but not

  56.       foreign tables).  If the specified table is a partitioned table, both the

  57.       inheritance statistics of the partitioned table as a whole and

  58.       statistics of the individual partitions are updated.

  59.      </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>

  60.       The name of a specific column to analyze. Defaults to all columns.

  61.      </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.46.7"><h2>Outputs</h2><p>

  62.     When <code class="literal">VERBOSE</code> is specified, <code class="command">ANALYZE</code> emits

  63.     progress messages to indicate which table is currently being

  64.     processed.  Various statistics about the tables are printed as well.

  65.    </p></div><div class="refsect1" id="id-1.9.3.46.8"><h2>Notes</h2><p>

  66.    To analyze a table, one must ordinarily be the table's owner or a

  67.    superuser.  However, database owners are allowed to

  68.    analyze all tables in their databases, except shared catalogs.

  69.    (The restriction for shared catalogs means that a true database-wide

  70.    <code class="command">ANALYZE</code> can only be performed by a superuser.)

  71.    <code class="command">ANALYZE</code> will skip over any tables that the calling user

  72.    does not have permission to analyze.

  73.   </p><p>

  74.    Foreign tables are analyzed only when explicitly selected.  Not all

  75.    foreign data wrappers support <code class="command">ANALYZE</code>.  If the table's

  76.    wrapper does not support <code class="command">ANALYZE</code>, the command prints a

  77.    warning and does nothing.

  78.   </p><p>

  79.    In the default <span class="productname">PostgreSQL</span> configuration,

  80.    the autovacuum daemon (see <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a>)

  81.    takes care of automatic analyzing of tables when they are first loaded

  82.    with data, and as they change throughout regular operation.

  83.    When autovacuum is disabled,

  84.    it is a good idea to run <code class="command">ANALYZE</code> periodically, or

  85.    just after making major changes in the contents of a table.  Accurate

  86.    statistics will help the planner to choose the most appropriate query

  87.    plan, and thereby improve the speed of query processing.  A common

  88.    strategy for read-mostly databases is to run <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>

  89.    and <code class="command">ANALYZE</code> once a day during a low-usage time of day.

  90.    (This will not be sufficient if there is heavy update activity.)

  91.   </p><p>

  92.    <code class="command">ANALYZE</code>

  93.    requires only a read lock on the target table, so it can run in

  94.    parallel with other activity on the table.

  95.   </p><p>

  96.    The statistics collected by <code class="command">ANALYZE</code> usually

  97.    include a list of some of the most common values in each column and

  98.    a histogram showing the approximate data distribution in each

  99.    column.  One or both of these can be omitted if

  100.    <code class="command">ANALYZE</code> deems them uninteresting (for example,

  101.    in a unique-key column, there are no common values) or if the

  102.    column data type does not support the appropriate operators.  There

  103.    is more information about the statistics in <a class="xref" href="maintenance.html" title="Chapter 24. Routine Database Maintenance Tasks">Chapter 24</a>.

  104.   </p><p>

  105.    For large tables, <code class="command">ANALYZE</code> takes a random sample

  106.    of the table contents, rather than examining every row.  This

  107.    allows even very large tables to be analyzed in a small amount of

  108.    time.  Note, however, that the statistics are only approximate, and

  109.    will change slightly each time <code class="command">ANALYZE</code> is run,

  110.    even if the actual table contents did not change.  This might result

  111.    in small changes in the planner's estimated costs shown by

  112.    <a class="xref" href="sql-explain.html" title="EXPLAIN"><span class="refentrytitle">EXPLAIN</span></a>.

  113.    In rare situations, this non-determinism will cause the planner's

  114.    choices of query plans to change after <code class="command">ANALYZE</code> is run.

  115.    To avoid this, raise the amount of statistics collected by

  116.    <code class="command">ANALYZE</code>, as described below.

  117.   </p><p>

  118.    The extent of analysis can be controlled by adjusting the

  119.    <a class="xref" href="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET">default_statistics_target</a> configuration variable, or

  120.    on a column-by-column basis by setting the per-column statistics

  121.    target with <code class="command">ALTER TABLE ... ALTER COLUMN ... SET

  122.    STATISTICS</code> (see <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>).

  123.    The target value sets the

  124.    maximum number of entries in the most-common-value list and the

  125.    maximum number of bins in the histogram.  The default target value

  126.    is 100, but this can be adjusted up or down to trade off accuracy of

  127.    planner estimates against the time taken for

  128.    <code class="command">ANALYZE</code> and the amount of space occupied in

  129.    <code class="literal">pg_statistic</code>.  In particular, setting the

  130.    statistics target to zero disables collection of statistics for

  131.    that column.  It might be useful to do that for columns that are

  132.    never used as part of the <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>,

  133.    or <code class="literal">ORDER BY</code> clauses of queries, since the planner will

  134.    have no use for statistics on such columns.

  135.   </p><p>

  136.    The largest statistics target among the columns being analyzed determines

  137.    the number of table rows sampled to prepare the statistics.  Increasing

  138.    the target causes a proportional increase in the time and space needed

  139.    to do <code class="command">ANALYZE</code>.

  140.   </p><p>

  141.    One of the values estimated by <code class="command">ANALYZE</code> is the number of

  142.    distinct values that appear in each column.  Because only a subset of the

  143.    rows are examined, this estimate can sometimes be quite inaccurate, even

  144.    with the largest possible statistics target.  If this inaccuracy leads to

  145.    bad query plans, a more accurate value can be determined manually and then

  146.    installed with

  147.    <code class="command">ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)</code>

  148.    (see <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>).

  149.   </p><p>

  150.     If the table being analyzed has one or more children,

  151.     <code class="command">ANALYZE</code> will gather statistics twice: once on the

  152.     rows of the parent table only, and a second time on the rows of the

  153.     parent table with all of its children.  This second set of statistics

  154.     is needed when planning queries that traverse the entire inheritance

  155.     tree.  The autovacuum daemon, however, will only consider inserts or

  156.     updates on the parent table itself when deciding whether to trigger an

  157.     automatic analyze for that table.  If that table is rarely inserted into

  158.     or updated, the inheritance statistics will not be up to date unless you

  159.     run <code class="command">ANALYZE</code> manually.

  160.   </p><p>

  161.     If any of the child tables are foreign tables whose foreign data wrappers

  162.     do not support <code class="command">ANALYZE</code>, those child tables are ignored while

  163.     gathering inheritance statistics.

  164.   </p><p>

  165.     If the table being analyzed is completely empty, <code class="command">ANALYZE</code>

  166.     will not record new statistics for that table.  Any existing statistics

  167.     will be retained.

  168.   </p></div><div class="refsect1" id="id-1.9.3.46.9"><h2>Compatibility</h2><p>

  169.    There is no <code class="command">ANALYZE</code> statement in the SQL standard.

  170.   </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="19.4.4. Cost-based Vacuum Delay">Section 19.4.4</a>, <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-begin.html">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">Home</a></td><td width="40%" align="right" valign="top"> BEGIN</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1