1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
16
HREF="sql-commands.html"><LINK
19
HREF="sql-alterview.html"><LINK
22
HREF="sql-begin.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
57
HREF="sql-alterview.html"
67
HREF="sql-alterview.html"
111
>ANALYZE -- collect statistics about a database</DIV
113
CLASS="REFSYNOPSISDIV"
121
>ANALYZE [ VERBOSE ] [ <TT
144
> collects statistics about the contents
145
of tables in the database, and stores the results in the <A
146
HREF="catalog-pg-statistic.html"
152
system catalog. Subsequently, the query planner uses these
153
statistics to help determine the most efficient execution plans for
157
> With no parameter, <TT
160
> examines every table in the
161
current database. With a parameter, <TT
165
only that table. It is further possible to give a list of column names,
166
in which case only the statistics for those columns are collected.
188
> Enables display of progress messages.
200
> The name (possibly schema-qualified) of a specific table to
201
analyze. Defaults to all tables in the current database.
213
> The name of a specific column to analyze. Defaults to all columns.
234
progress messages to indicate which table is currently being
235
processed. Various statistics about the tables are printed as well.
246
> In the default <SPAN
250
the autovacuum daemon (see <A
251
HREF="routine-vacuuming.html#AUTOVACUUM"
254
takes care of automatic analyzing of tables when they are first loaded
255
with data, and as they change throughout regular operation.
256
When autovacuum is disabled,
257
it is a good idea to run <TT
261
just after making major changes in the contents of a table. Accurate
262
statistics will help the planner to choose the most appropriate query
263
plan, and thereby improve the speed of query processing. A common
264
strategy is to run <A
265
HREF="sql-vacuum.html"
271
> once a day during a low-usage time of day.
278
requires only a read lock on the target table, so it can run in
279
parallel with other activity on the table.
282
> The statistics collected by <TT
286
include a list of some of the most common values in each column and
287
a histogram showing the approximate data distribution in each
288
column. One or both of these can be omitted if
292
> deems them uninteresting (for example,
293
in a unique-key column, there are no common values) or if the
294
column data type does not support the appropriate operators. There
295
is more information about the statistics in <A
296
HREF="maintenance.html"
301
> For large tables, <TT
304
> takes a random sample
305
of the table contents, rather than examining every row. This
306
allows even very large tables to be analyzed in a small amount of
307
time. Note, however, that the statistics are only approximate, and
308
will change slightly each time <TT
312
even if the actual table contents did not change. This might result
313
in small changes in the planner's estimated costs shown by
315
HREF="sql-explain.html"
318
In rare situations, this non-determinism will cause the planner's
319
choices of query plans to change after <TT
323
To avoid this, raise the amount of statistics collected by
327
>, as described below.
330
> The extent of analysis can be controlled by adjusting the
332
HREF="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET"
333
>default_statistics_target</A
334
> configuration variable, or
335
on a column-by-column basis by setting the per-column statistics
338
>ALTER TABLE ... ALTER COLUMN ... SET
341
HREF="sql-altertable.html"
344
The target value sets the
345
maximum number of entries in the most-common-value list and the
346
maximum number of bins in the histogram. The default target value
347
is 100, but this can be adjusted up or down to trade off accuracy of
348
planner estimates against the time taken for
352
> and the amount of space occupied in
356
>. In particular, setting the
357
statistics target to zero disables collection of statistics for
358
that column. It might be useful to do that for columns that are
359
never used as part of the <TT
369
> clauses of queries, since the planner will
370
have no use for statistics on such columns.
373
> The largest statistics target among the columns being analyzed determines
374
the number of table rows sampled to prepare the statistics. Increasing
375
the target causes a proportional increase in the time and space needed
382
> One of the values estimated by <TT
386
distinct values that appear in each column. Because only a subset of the
387
rows are examined, this estimate can sometimes be quite inaccurate, even
388
with the largest possible statistics target. If this inaccuracy leads to
389
bad query plans, a more accurate value can be determined manually and then
393
>ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)</TT
396
HREF="sql-altertable.html"
401
> If the table being analyzed has one or more children,
405
> will gather statistics twice: once on the
406
rows of the parent table only, and a second time on the rows of the
407
parent table with all of its children. The autovacuum daemon, however,
408
will only consider inserts or updates on the parent table when deciding
409
whether to trigger an automatic analyze. If that table is rarely
410
inserted into or updated, the inheritance statistics will not be up to date
428
> statement in the SQL standard.
439
HREF="sql-vacuum.html"
442
HREF="app-vacuumdb.html"
448
HREF="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST"
451
HREF="routine-vacuuming.html#AUTOVACUUM"
459
SUMMARY="Footer navigation table"
470
HREF="sql-alterview.html"
488
HREF="sql-begin.html"
504
HREF="sql-commands.html"
b'\\ No newline at end of file'