From f905d65ee35b3f84b6d4433a5198af0e2e7bd090 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 7 May 2001 00:43:27 +0000 Subject: Rewrite of planner statistics-gathering code. ANALYZE is now available as a separate statement (though it can still be invoked as part of VACUUM, too). pg_statistic redesigned to be more flexible about what statistics are stored. ANALYZE now collects a list of several of the most common values, not just one, plus a histogram (not just the min and max values). Random sampling is used to make the process reasonably fast even on very large tables. The number of values and histogram bins collected is now user-settable via an ALTER TABLE command. There is more still to do; the new stats are not being used everywhere they could be in the planner. But the remaining changes for this project should be localized, and the behavior is already better than before. A not-very-related change is that sorting now makes use of btree comparison routines if it can find one, rather than invoking '<' twice. --- doc/src/sgml/catalogs.sgml | 183 ++++++++++++++++++++++++++----- doc/src/sgml/indices.sgml | 4 +- doc/src/sgml/ref/allfiles.sgml | 3 +- doc/src/sgml/ref/alter_table.sgml | 26 +++-- doc/src/sgml/ref/analyze.sgml | 219 ++++++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/vacuum.sgml | 49 ++++----- doc/src/sgml/reference.sgml | 3 +- doc/src/sgml/xoper.sgml | 4 +- 8 files changed, 421 insertions(+), 70 deletions(-) create mode 100644 doc/src/sgml/ref/analyze.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 1738a5bf1d4..01885a5095b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ @@ -16,7 +16,7 @@ PostgreSQL's system catalogs are regular tables. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. - Normally one never has to change the system catalogs by hand, there + Normally one should not change the system catalogs by hand, there are always SQL commands to do that. (For example, CREATE DATABASE inserts a row into the pg_database catalog -- and actually @@ -185,7 +185,7 @@ pg_aggregate stores information about aggregate functions. An aggregate function is a function that - operates on a set of values (typically one column from each the row + operates on a set of values (typically one column from each row that matches a query condition) and returns a single value computed from all these values. Typical aggregate functions are sum, count, and @@ -233,7 +233,7 @@ aggbasetype oid pg_type.oid - The type on which this function operates when invoked from SQL + The input datatype for this aggregate function aggtranstype @@ -269,7 +269,7 @@ An aggregate function is identified through name - and argument type. Hence aggname and aggname + and argument type. Hence aggname and aggbasetype are the composite primary key. @@ -311,11 +311,8 @@ adnum int2 - - - The number of the column; see - pg_attribute.pg_attnum - + pg_attribute.attnum + The number of the column @@ -390,20 +387,18 @@ - attdispersion - float4 + attstattarget + int4 - attdispersion is the dispersion - statistic of the column (0.0 to 1.0), or zero if the statistic - has not been calculated, or -1.0 if VACUUM - found that the column contains no duplicate entries (in which - case the dispersion should be taken as - 1.0/numberOfRows for the current table size). - The -1.0 hack is useful because the number of rows may be - updated more often than - attdispersion is. We assume that the - column will retain its no-duplicate-entry property. + attstattarget controls the level of detail + of statistics accumulated for this column by + ANALYZE. + A zero value indicates that no statistics should be collected. + The exact meaning of positive values is datatype-dependent. + For scalar datatypes, attstattarget + is both the target number of most common values + to collect, and the target number of histogram bins to create. @@ -430,10 +425,12 @@ - attnelems + attndims int4 - Number of dimensions, if the column is an array + + Number of dimensions, if the column is an array; otherwise 0. + @@ -610,18 +607,22 @@ Size of the on-disk representation of this table in pages (size - BLCKSZ). This is only an approximate value - which is calculated during vacuum. + BLCKSZ). + This is only an estimate used by the planner. + It is updated by VACUUM, + ANALYZE, and CREATE INDEX. reltuples - int4 + float4 - Number of tuples in the table. This is only an estimate used - by the planner, updated by VACUUM. + Number of tuples in the table. + This is only an estimate used by the planner. + It is updated by VACUUM, + ANALYZE, and CREATE INDEX. @@ -1671,6 +1672,130 @@ +
+ pg_statistic + + + pg_statistic stores statistical data about + the contents of the database. Entries are created by + ANALYZE and subsequently used by the query planner. + There is one entry for each table column that has been analyzed. + Note that all the statistical data is inherently approximate, + even assuming that it is up-to-date. + + + + Since different kinds of statistics may be appropriate for different + kinds of data, pg_statistic is designed not + to assume very much about what sort of statistics it stores. Only + extremely general statistics (such as NULL-ness) are given dedicated + columns in pg_statistic. Everything else + is stored in "slots", which are groups of associated columns whose + content is identified by a code number in one of the slot's columns. + For more information see + src/include/catalog/pg_statistic.h. + + + + pg_statistic Columns + + + + + Name + Type + References + Description + + + + + + starelid + oid + pg_class.oid + The table that the described column belongs to + + + + staattnum + int2 + pg_attribute.attnum + The number of the described column + + + + stanullfrac + float4 + + The fraction of the column's entries that are NULL + + + + stawidth + int4 + + The average stored width, in bytes, of non-NULL entries + + + + stadistinct + float4 + + The number of distinct non-NULL data values in the column. + A value greater than zero is the actual number of distinct values. + A value less than zero is the negative of a fraction of the number + of rows in the table (for example, a column in which values appear about + twice on the average could be represented by stadistinct = -0.5). + A zero value means the number of distinct values is unknown. + + + + + stakindN + int2 + + A code number indicating the kind of statistics stored in the Nth + "slot" of the pg_statistic row. + + + + + staopN + oid + pg_operator.oid + An operator used to derive the statistics stored in the + Nth "slot". For example, a histogram slot would show the "<" + operator that defines the sort order of the data. + + + + + stanumbersN + float4[] + + Numerical statistics of the appropriate kind for the Nth + "slot", or NULL if the slot kind does not involve numerical values. + + + + + stavaluesN + text[] + + Column data values of the appropriate kind for the Nth + "slot", or NULL if the slot kind does not store any data values. + For datatype independence, all column data values are converted + to external textual form and stored as TEXT datums. + + + + +
+ +
+ +
pg_type diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 32ecd9e6695..42cab244ab8 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1,4 +1,4 @@ - + Indices @@ -71,7 +71,7 @@ CREATE INDEX test1_id_index ON test1 (id); Once the index is created, no further intervention is required: the system will use the index when it thinks it would be more efficient than a sequential table scan. But you may have to run the - VACUUM ANALYZE command regularly to update + ANALYZE command regularly to update statistics to allow the query planner to make educated decisions. Also read for information about how to find out whether an index is used and when and why the diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 0088896131e..dea65e98f2e 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -40,6 +40,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 4c258c81650..21fc8c2ebdb 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ @@ -29,7 +29,9 @@ ALTER TABLE [ ONLY ] table [ * ] ALTER TABLE [ ONLY ] table [ * ] ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT } -ALTER TABLE table [ * ] +ALTER TABLE [ ONLY ] table [ * ] + ALTER [ COLUMN ] column SET STATISTICS integer +ALTER TABLE [ ONLY ] table [ * ] RENAME [ COLUMN ] column TO newcolumn ALTER TABLE table @@ -159,9 +161,14 @@ ALTER TABLE table ALTER TABLE changes the definition of an existing table. The ADD COLUMN form adds a new column to the table using the same syntax as . The ALTER COLUMN form - allows you to set or remove the default for the column. Note that defaults - only apply to newly inserted rows. + endterm="SQL-CREATETABLE-title">. + The ALTER COLUMN SET/DROP DEFAULT forms + allow you to set or remove the default for the column. Note that defaults + only apply to subsequent INSERT commands; they do not + cause rows already in the table to change. + The ALTER COLUMN SET STATISTICS form allows you to + set the statistics-gathering target for subsequent + operations. The RENAME clause causes the name of a table or column to change without changing any of the data contained in the affected table. Thus, the table or column will @@ -170,7 +177,7 @@ ALTER TABLE table The ADD table constraint definition clause adds a new constraint to the table using the same syntax as . - The OWNER clause chnages the owner of the table to the user + The OWNER clause changes the owner of the table to the user new user. @@ -190,10 +197,11 @@ ALTER TABLE table - In the current implementation, default and constraint clauses for the + In the current implementation of ADD COLUMN, + default and constraint clauses for the new column will be ignored. You can use the SET DEFAULT form of ALTER TABLE to set the default later. - (You will also have to update the already existing rows to the + (You may also want to update the already existing rows to the new default value, using .) @@ -210,7 +218,7 @@ ALTER TABLE table You must own the table in order to change it. - Renaming any part of the schema of a system + Changing any part of the schema of a system catalog is not permitted. The PostgreSQL User's Guide has further information on inheritance. diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml new file mode 100644 index 00000000000..57d3213d614 --- /dev/null +++ b/doc/src/sgml/ref/analyze.sgml @@ -0,0 +1,219 @@ + + + + + + ANALYZE + + SQL - Language Statements + + + + ANALYZE + + + Collect statistics about a Postgres database + + + + + 2001-05-04 + + +ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ] + + + + + 2001-05-04 + + + Inputs + + + + + + VERBOSE + + + Enables display of progress messages. + + + + + table + + + The name of a specific table to analyze. Defaults to all tables. + + + + + column + + + The name of a specific column to analyze. Defaults to all columns. + + + + + + + + + + 2001-05-04 + + + Outputs + + + + + + +ANALYZE + + + + The command is complete. + + + + + + + + + + + + 2001-05-04 + + + Description + + + ANALYZE collects statistics about the contents of + Postgres tables, and stores the results in + the system table pg_statistic. Subsequently, + the query planner uses the statistics to help determine the most efficient + execution plans for queries. + + + + With no parameter, ANALYZE examines every table in the + current database. With a parameter, ANALYZE examines + only that table. It is further possible to give a list of column names, + in which case only the statistics for those columns are updated. + + + + + 2001-05-04 + + + Notes + + + + It is a good idea to run ANALYZE 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 is to run VACUUM and ANALYZE + once a day during a low-usage time of day. + + + + Unlike , + ANALYZE requires + only a read lock on the target table, so it can run in parallel with + other activity on the table. + + + + 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 statistics are only approximate, and will change slightly each + time ANALYZE 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 EXPLAIN. + + + + 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 + ANALYZE deems them uninteresting (for example, in + a unique-key column, there are no common values) or if the column + datatype does not support the appropriate operators. + + + + The extent of analysis can be controlled by adjusting the per-column + statistics target with ALTER TABLE ALTER COLUMN SET + STATISTICS (see + ). 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 + 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 may 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 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 ANALYZE. + + + + + + + + Compatibility + + + + + 2001-05-04 + + + SQL92 + + + There is no ANALYZE statement in SQL92. + + + + + + diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 51cb8a9ffda..cbb182466ea 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -1,5 +1,5 @@ @@ -15,15 +15,15 @@ Postgres documentation VACUUM - Clean and analyze a Postgres database + Clean and optionally analyze a Postgres database - 1999-07-20 + 2001-05-04 -VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] +VACUUM [ VERBOSE ] [ table ] VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] @@ -49,7 +49,7 @@ VACUUM [ VERBOSE ] ANALYZE [ table ANALYZE - Updates column statistics used by the optimizer to + Updates statistics used by the optimizer to determine the most efficient way to execute a query. @@ -90,7 +90,7 @@ VACUUM [ VERBOSE ] ANALYZE [ table - The command has been accepted and the database is being cleaned. + The command is complete. @@ -144,28 +144,26 @@ NOTICE: Index index: Pages 28; Description - VACUUM serves two purposes in - Postgres as both a means to reclaim storage and - also a means to collect information for the optimizer. + VACUUM reclaims storage occupied by deleted tuples. + In normal Postgres operation, tuples that + are DELETEd or obsoleted by UPDATE are not physically removed from + their table; they remain present until a VACUUM is + done. Therefore it's necessary to do VACUUM + periodically, especially on frequently-updated tables. - VACUUM opens every table in the database, - cleans out records from rolled back transactions, and updates statistics in the - system catalogs. The statistics maintained include the number of - tuples and number of pages stored in all tables. - - - - - VACUUM ANALYZE collects statistics representing the - dispersion of the data in each column. - This information is valuable when several query execution paths are possible. + With no parameter, VACUUM processes every table in the + current database. With a parameter, VACUUM processes + only that table. - Running VACUUM - periodically will increase the speed of the database in processing user queries. + VACUUM ANALYZE performs a VACUUM + and then an ANALYZE for each selected table. This + is a handy combination form for routine maintenance scripts. See + + for more details about its processing. @@ -175,16 +173,15 @@ NOTICE: Index index: Pages 28; Notes - - The open database is the target for VACUUM. - + We recommend that active production databases be VACUUM-ed nightly, in order to remove expired rows. After copying a large table into Postgres or after deleting a large number of records, it may be a good idea to issue a VACUUM - ANALYZE query. This will update the system catalogs with + ANALYZE command for the affected table. This will update the + system catalogs with the results of all recent changes, and allow the Postgres query optimizer to make better choices in planning user queries. diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index b92ee0868d0..9a977a6515c 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,5 +1,5 @@ @@ -26,6 +26,7 @@ PostgreSQL Reference Manual &alterGroup; &alterTable; &alterUser; + &analyze; &begin; &checkpoint; &close; diff --git a/doc/src/sgml/xoper.sgml b/doc/src/sgml/xoper.sgml index d38e78a4e1a..57d8bb79c28 100644 --- a/doc/src/sgml/xoper.sgml +++ b/doc/src/sgml/xoper.sgml @@ -1,5 +1,5 @@ @@ -244,7 +244,7 @@ SELECT (a + b) AS c FROM test_complex; only a small fraction. '<' will accept a fraction that depends on where the given constant falls in the range of values for that table column (which, it just so happens, is information collected by - VACUUM ANALYZE and made available to the selectivity estimator). + ANALYZE and made available to the selectivity estimator). '<=' will accept a slightly larger fraction than '<' for the same comparison constant, but they're close enough to not be worth distinguishing, especially since we're not likely to do better than a -- cgit v1.2.3