From e48b9b557003de55068e47ecf87af6860365abbe Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 4 Nov 2004 19:08:42 +0000 Subject: [PATCH] Minor documentation updates from Simon Riggs. --- doc/src/sgml/ref/reindex.sgml | 30 +++++++++++++++++++++--------- doc/src/sgml/runtime.sgml | 31 +++++++++++++++++++++---------- src/backend/utils/misc/guc.c | 4 ++-- 3 files changed, 44 insertions(+), 21 deletions(-) diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 98b6e3b69d..13bb9f5098 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -1,5 +1,5 @@ @@ -29,7 +29,7 @@ REINDEX { DATABASE | TABLE | INDEX } name REINDEX rebuilds an index based on the data - stored in the table, replacing the old copy of the index. There are + stored in the index's table, replacing the old copy of the index. There are two main reasons to use REINDEX: @@ -98,6 +98,8 @@ REINDEX { DATABASE | TABLE | INDEX } name The name of the specific database, table, or index to be reindexed. Table and index names may be schema-qualified. + Presently, REINDEX DATABASE can only reindex the current + database, so its parameter must match the current database's name. @@ -119,13 +121,7 @@ REINDEX { DATABASE | TABLE | INDEX } name If you suspect corruption of an index on a user table, you can simply rebuild that index, or all indexes on the table, using - REINDEX INDEX or REINDEX - TABLE. Another approach to dealing with a corrupted - user-table index is just to drop and recreate it. This may in fact - be preferable if you would like to maintain some semblance of - normal operation on the table meanwhile. REINDEX - acquires exclusive lock on the table, while CREATE - INDEX only locks out writes not reads of the table. + REINDEX INDEX or REINDEX TABLE. @@ -187,6 +183,22 @@ REINDEX { DATABASE | TABLE | INDEX } name errors.) + + REINDEX is similar to a drop and recreate of the index + in that the index contents are rebuilt from scratch. However, the locking + considerations are rather different. REINDEX locks out writes + but not reads of the index's parent table. It also takes an exclusive lock + on the specific index being processed, which will block reads that attempt + to use that index. In contrast, DROP INDEX momentarily takes + exclusive lock on the parent table, blocking both writes and reads. The + subsequent CREATE INDEX locks out writes but not reads; since + the index is not there, no read will attempt to use it, meaning that there + will be no blocking but reads may be forced into expensive sequential + scans. Another important point is that the drop/create approach + invalidates any cached query plans that use the index, while + REINDEX does not. + + Prior to PostgreSQL 7.4, REINDEX TABLE did not automatically process TOAST tables, and so those had diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index b4a84616f5..be2ced298d 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -583,7 +583,7 @@ SET ENABLE_SEQSCAN TO OFF; Specifies the main server configuration file - (customarily called postgresql.conf). + (customarily called postgresql.conf). This option can only be set on the postmaster command line. @@ -615,8 +615,8 @@ SET ENABLE_SEQSCAN TO OFF; Specifies that the postmaster should create an - additional process-id (PID) file for use by server administration - programs. + additional process-id (PID) file for use by server administration + programs. This option can only be set at server start. @@ -1666,9 +1666,20 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Win32 Sets the planner's assumption about the effective size of the - disk cache (that is, the portion of the kernel's disk cache - that will be used for PostgreSQL - data files). This is measured in disk pages, which are + disk cache that is available to a single index scan. This is + factored into estimates of the cost of using an index; a higher + value makes it more likely index scans will be used, a lower + value makes it more likely sequential scans will be used. When + setting this parameter you should consider both + PostgreSQL's shared buffers and the + portion of the kernel's disk cache that will be used for + PostgreSQL data files. Also, take into + account the expected number of concurrent queries using different + indexes, since they will have to share the available space. + This parameter has no effect on the size of shared memory + allocated by PostgreSQL, nor does it reserve kernel disk cache; + it is used only for estimation purposes. + The value is measured in disk pages, which are normally 8192 bytes each. The default is 1000. @@ -1678,7 +1689,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Win32 random_page_cost (floating point) - Sets the query planner's estimate of the cost of a + Sets the planner's estimate of the cost of a nonsequentially fetched disk page. This is measured as a multiple of the cost of a sequential page fetch. A higher value makes it more likely a sequential scan will be used, a @@ -1692,7 +1703,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Win32 cpu_tuple_cost (floating point) - Sets the query planner's estimate of the cost of processing + Sets the planner's estimate of the cost of processing each row during a query. This is measured as a fraction of the cost of a sequential page fetch. The default is 0.01. @@ -1703,7 +1714,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Win32 cpu_index_tuple_cost (floating point) - Sets the query planner's estimate of the cost of processing + Sets the planner's estimate of the cost of processing each index row during an index scan. This is measured as a fraction of the cost of a sequential page fetch. The default is 0.001. diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 366ef77cf6..c742ace1b5 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -10,7 +10,7 @@ * Written by Peter Eisentraut . * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.246 2004/10/22 19:48:19 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.247 2004/11/04 19:08:42 tgl Exp $ * *-------------------------------------------------------------------- */ @@ -1344,7 +1344,7 @@ static struct config_real ConfigureNamesReal[] = "pages, which are normally 8 kB each.") }, &effective_cache_size, - DEFAULT_EFFECTIVE_CACHE_SIZE, 0, DBL_MAX, NULL, NULL + DEFAULT_EFFECTIVE_CACHE_SIZE, 1, DBL_MAX, NULL, NULL }, { {"random_page_cost", PGC_USERSET, QUERY_TUNING_COST, -- 2.40.0