analyze man page on Scientific

Man page or keyword search:  
man Server   26626 pages
apropos Keyword Search (all sections)
Output format
Scientific logo
[printable version]

ANALYZE(7)			 SQL Commands			    ANALYZE(7)

       ANALYZE - collect statistics about a database

       ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

       ANALYZE	collects  statistics about the contents of tables in the data‐
       base, and stores the results in the pg_statistic system catalog. Subse‐
       quently,	 the query planner uses these statistics to help determine the
       most efficient execution plans for queries.

       With no parameter, ANALYZE examines every table in  the	current	 data‐
       base. With a parameter, ANALYZE examines only that table. It is further
       possible to give a list of column names, in which case only the statis‐
       tics for those columns are collected.

	      Enables display of progress messages.

       table  The name (possibly schema-qualified) of a specific table to ana‐
	      lyze. Defaults to all tables in the current database.

       column The name of a specific column to analyze. Defaults to  all  col‐

       When  VERBOSE is specified, ANALYZE emits progress messages to indicate
       which table is currently being processed. Various statistics about  the
       tables are printed as well.

       In  the	default	 PostgreSQL  configuration, in the documentation 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  ANALYZE  periodically,  or  just
       after making major changes in the contents of a table. Accurate statis‐
       tics will help the planner to choose the most appropriate  query	 plan,
       and thereby improve the speed of query processing. A common strategy is
       to run VACUUM [vacuum(7)] and ANALYZE once a  day  during  a  low-usage
       time of day.

       ANALYZE requires only a read lock on the target table, so it can run in
       parallel with other activity on the table.

       The statistics collected by ANALYZE 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	ANALYZE	 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 informa‐
       tion about the statistics in in the documentation.

       For large tables, ANALYZE 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  statis‐
       tics  are  only approximate, and will change slightly each time ANALYZE
       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
       EXPLAIN [explain(7)].  In rare situations,  this	 non-determinism  will
       cause  the  planner's choices of query plans to change after ANALYZE is
       run.  To avoid this, raise the amount of statistics collected  by  ANA‐
       LYZE, as described below.

       The  extent of analysis can be controlled by adjusting the default_sta‐
       tistics_target configuration variable, or on a  column-by-column	 basis
       by  setting the per-column statistics target with ALTER TABLE ... ALTER
       COLUMN ... SET STATISTICS (see ALTER TABLE [alter_table(7)]). The  tar‐
       get  value  sets the maximum number of entries in the most-common-value
       list and the maximum number of bins in the histogram. The default  tar‐
       get  value  is  100,  but  this can be adjusted up or down to trade off
       accuracy of planner estimates against the time taken  for  ANALYZE  and
       the  amount  of	space occupied in pg_statistic. 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 WHERE, GROUP BY, or ORDER BY	 clauses  of  queries,
       since the planner will have no use for statistics on such columns.

       The  largest  statistics target among the columns being analyzed deter‐
       mines 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 ANALYZE.

       There is no ANALYZE statement in the SQL standard.

       VACUUM [vacuum(7)], vacuumdb [vacuumdb(1)], in  the  documentation,  in
       the documentation

SQL - Language Statements	  2013-10-08			    ANALYZE(7)

List of man pages available for Scientific

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
Vote for polarhome
Free Shell Accounts :: the biggest list on the net