diff options
Diffstat (limited to 'doc/src/sgml/indices.sgml')
| -rw-r--r-- | doc/src/sgml/indices.sgml | 36 |
1 files changed, 18 insertions, 18 deletions
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index e40724df177..974e1415f65 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.77 2009/04/27 16:27:35 momjian Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.78 2009/06/17 21:58:49 tgl Exp $ --> <chapter id="indexes"> <title id="indexes-title">Indexes</title> @@ -36,7 +36,7 @@ SELECT content FROM test1 WHERE id = <replaceable>constant</replaceable>; matching entries. If there are many rows in <structname>test1</structname> and only a few rows (perhaps zero or one) that would be returned by such a query, this is clearly an - inefficient method. But if the system maintains an + inefficient method. But if the system has been instructed to maintain an index on the <structfield>id</structfield> column, it can use a more efficient method for locating matching rows. For instance, it might only have to walk a few levels deep into a search tree. @@ -73,7 +73,7 @@ CREATE INDEX test1_id_index ON test1 (id); <para> Once an index is created, no further intervention is required: the system will update the index when the table is modified, and it will - use the index in queries when it thinks it would be more efficient + use the index in queries when it thinks doing so would be more efficient than a sequential table scan. But you might have to run the <command>ANALYZE</command> command regularly to update statistics to allow the query planner to make educated decisions. @@ -294,7 +294,7 @@ CREATE TABLE test2 ( <programlisting> SELECT name FROM test2 WHERE major = <replaceable>constant</replaceable> AND minor = <replaceable>constant</replaceable>; </programlisting> - then it might be appropriate to define an index on columns + then it might be appropriate to define an index on the columns <structfield>major</structfield> and <structfield>minor</structfield> together, e.g.: <programlisting> @@ -384,16 +384,16 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor); <para> The planner will consider satisfying an <literal>ORDER BY</> specification - by either scanning an available index that matches the specification, + either by scanning an available index that matches the specification, or by scanning the table in physical order and doing an explicit sort. For a query that requires scanning a large fraction of the - table, the explicit sort is likely to be faster than using an index + table, an explicit sort is likely to be faster than using an index because it requires - less disk I/O due to a sequential access pattern. Indexes are + less disk I/O due to following a sequential access pattern. Indexes are more useful when only a few rows need be fetched. An important special case is <literal>ORDER BY</> in combination with <literal>LIMIT</> <replaceable>n</>: an explicit sort will have to process - all data to identify the first <replaceable>n</> rows, but if there is + all the data to identify the first <replaceable>n</> rows, but if there is an index matching the <literal>ORDER BY</>, the first <replaceable>n</> rows can be retrieved directly, without scanning the remainder at all. </para> @@ -433,14 +433,14 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST); <literal>ORDER BY x DESC, y DESC</> if we scan backward. But it might be that the application frequently needs to use <literal>ORDER BY x ASC, y DESC</>. There is no way to get that - ordering from a simpler index, but it is possible if the index is defined + ordering from a plain index, but it is possible if the index is defined as <literal>(x ASC, y DESC)</> or <literal>(x DESC, y ASC)</>. </para> <para> Obviously, indexes with non-default sort orderings are a fairly specialized feature, but sometimes they can produce tremendous - speedups for certain queries. Whether it's worth creating such an + speedups for certain queries. Whether it's worth maintaining such an index depends on how often you use queries that require a special sort ordering. </para> @@ -584,9 +584,9 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla </indexterm> <para> - An index column need not be just a column of an underlying table, + An index column need not be just a column of the underlying table, but can be a function or scalar expression computed from one or - more columns of a table. This feature is useful to obtain fast + more columns of the table. This feature is useful to obtain fast access to tables based on the results of computations. </para> @@ -666,8 +666,8 @@ CREATE INDEX people_names ON people ((first_name || ' ' || last_name)); values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the - index. A partial index reduces the size of the index, which speeds - up queries that use the index. It will also speed up many table + index at all. This reduces the size of the index, which will speed + up those queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases. <xref linkend="indexes-partial-ex1"> shows a possible application of this idea. @@ -701,7 +701,7 @@ CREATE TABLE access_log ( such as this: <programlisting> CREATE INDEX access_log_client_ip_ix ON access_log (client_ip) -WHERE NOT (client_ip > inet '192.168.100.0' AND +WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255'); </programlisting> </para> @@ -724,14 +724,14 @@ WHERE client_ip = inet '192.168.100.23'; <para> Observe that this kind of partial index requires that the common values be predetermined, so such partial indexes are best used for - data distribution that do not change. The indexes can be recreated + data distributions that do not change. The indexes can be recreated occasionally to adjust for new data distributions, but this adds - maintenance overhead. + maintenance effort. </para> </example> <para> - Another possible use for partial indexes is to exclude values from the + Another possible use for a partial index is to exclude values from the index that the typical query workload is not interested in; this is shown in <xref linkend="indexes-partial-ex2">. This results in the same |