<!--

$Header: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v 1.31 2003/08/31 17:32:19 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v 1.32 2003/09/11 17:31:45 momjian Exp $

-->

<chapter id="performance-tips">

<productname>PostgreSQL</productname> planner will switch from exhaustive

search to a <firstterm>genetic</firstterm> probabilistic search

through a limited number of possibilities. (The switch-over threshold is

set by the <varname>geqo_threshold</varname> run-time

set by the <varname>GEQO_THRESHOLD</varname> run-time

parameter.)

The genetic search takes less time, but it won't

necessarily find the best possible plan.

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.22 2003/09/03 22:17:07 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.23 2003/09/1117:31:45 momjian Exp $

-->

<chapter id="plpgsql">

allow users to allow users to define set-returning functions

that do not have this limitation. Currently, the point at

which data begins being written to disk is controlled by the

<varname>sort_mem</> configuration variable. Administrators

<varname>SORT_MEM</> configuration variable. Administrators

who have sufficient memory to store larger result sets in

memory should consider increasing this parameter.

</para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/analyze.sgml,v 1.14 2003/09/09 18:28:52 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/analyze.sgml,v 1.15 2003/09/11 17:31:45 momjian Exp $

PostgreSQL documentation

-->

<title>Description</title>

<para>

<command>ANALYZE</command> collects statistics about the contents of

tables in the database, and stores the results in

the systemtable <literal>pg_statistic</literal>. Subsequently,

the queryplanner usesthe statistics to help determine the most efficient

<command>ANALYZE</command> collects statistics about the contents

oftables in the database, and stores the results in the system

table <literal>pg_statistic</literal>. Subsequently, the query

planner usesthese statistics to help determine the most efficient

execution plans for queries.

</para>

</para>

<para>

Unlike <command>VACUUM FULL</command>,

<command>ANALYZE</command> requires

only a read lock on the target table, so it can run in parallel with

other activity on the table.

Unlike <command>VACUUM FULL</command>, <command>ANALYZE</command>

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

parallel with other activity on the table.

</para>

<para>

For large tables, <command>ANALYZE</command> 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 <command>ANALYZE</command> is run, even if the actual table contents

did not change. This may result in small changes in the planner's

estimated costs shown by <command>EXPLAIN</command>.

The statistics collected by <command>ANALYZE</command> 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 may be omitted if

<command>ANALYZE</command> 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 <xref

linkend="maintenance">.

</para>

<para>

The collected statistics 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 may be omitted if

<command>ANALYZE</command> 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 <xref linkend="maintenance">.

For large tables, <command>ANALYZE</command> 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 <command>ANALYZE</command> is run,

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

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

<command>EXPLAIN</command>. In rare situations, this

non-determinism will cause the query optimizer to choose a

different query plan between runs of <command>ANALYZE</command>. To

avoid this, raise the amount of statistics collected by

<command>ANALYZE</command>, as described below.

</para>

<para>

The extent of analysis can be controlled by adjusting the

<literal>default_statistics_target</> parameter variable, or on a

column-by-column basis by setting the per-column

statistics target with <command>ALTER TABLE ... ALTER COLUMN ... SET

STATISTICS</command> (see

<xref linkend="sql-altertable" endterm="sql-altertable-title">). 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 10, but this can be adjusted up or down to trade off

accuracy of planner estimates against the time taken for

<command>ANALYZE</command> and the amount of space occupied

in <literal>pg_statistic</literal>.

In particular, setting the statistics target to zero disables collection of

statistics for that column. It may be useful to do that for columns that

are never used as part of the <literal>WHERE</>, <literal>GROUP BY</>, or <literal>ORDER BY</> clauses of

queries, since the planner will have no use for statistics on such columns.

<varname>DEFAULT_STATISTICS_TARGET</varname> parameter variable, or

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

target with <command>ALTER TABLE ... ALTER COLUMN ... SET

STATISTICS</command> (see <xref linkend="sql-altertable"

endterm="sql-altertable-title">). 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 10, but this can be adjusted up or down to trade off accuracy of

planner estimates against the time taken for

<command>ANALYZE</command> and the amount of space occupied in

<literal>pg_statistic</literal>. In particular, setting the

statistics target to zero disables collection of statistics for

that column. It may be useful to do that for columns that are

never used as part of the <literal>WHERE</>, <literal>GROUP BY</>,

or <literal>ORDER BY</> clauses of queries, since the planner will

have no use for statistics on such columns.

</para>

<para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_user.sgml,v 1.28 2003/09/09 18:28:53 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_user.sgml,v 1.29 2003/09/11 17:31:45 momjian Exp $

PostgreSQL documentation

-->

These key words control whether the password is stored

encrypted in the system catalogs. (If neither is specified,

the default behavior is determined by the configuration

parameter <varname>password_encryption</varname>.) If the

parameter <varname>PASSWORD_ENCRYPTION</varname>.) If the

presented password string is already in MD5-encrypted format,

then it is stored encrypted as-is, regardless of whether

<literal>ENCRYPTED</> or <literal>UNENCRYPTED</> is specified

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/postmaster.sgml,v 1.37 2003/08/31 17:32:24 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/postmaster.sgml,v 1.38 2003/09/11 17:31:45 momjian Exp $

PostgreSQL documentation

-->

<listitem>

<para>

Default value of the <literal>datestyle</literal> run-time

Default value of the <varname>DATESTYLE</varname> run-time

parameter. (The use of this environment variable is deprecated.)

</para>

</listitem>

<prompt>$</prompt> <userinput>postmaster -c sort_mem=1234</userinput>

<prompt>$</prompt> <userinput>postmaster --sort-mem=1234</userinput>

</screen>

Either form overrides whatever setting might exist for <literal>sort_mem</>

Either form overrides whatever setting might exist for <varname>SORT_MEM</>

in <filename>postgresql.conf</>. Notice that underscores in parameter

names can be written as either underscore or dash on the command line.

</para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/reset.sgml,v 1.22 2003/09/09 18:28:53 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/reset.sgml,v 1.23 2003/09/11 17:31:45 momjian Exp $

PostgreSQL documentation

-->

<title>Examples</title>

<para>

Set <varname>datestyle</> to its default value:

Set <varname>DATESTYLE</> to its default value:

<screen>

RESET datestyle;

</screen>

</para>

<para>

Set <varname>geqo</> to its default value:

Set <varname>GEQO</> to its default value:

<screen>

RESET geqo;

</screen>