From 2ce4b4cda17827296515d100b557470f93c60708 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 26 Mar 2000 19:47:17 +0000 Subject: [PATCH] Update obsolete statement that indexes can have only 7 columns. Reorganize description of index features for more clarity. --- doc/src/sgml/indices.sgml | 116 +++++++++++++++++++++++--------------- 1 file changed, 70 insertions(+), 46 deletions(-) diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 420ee00733..8dc410f263 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -2,13 +2,21 @@ Indices and Keys - Indexes are primarily used to enhance database + Indexes are commonly used to enhance database performance. They should be defined on table columns (or class - attributes) which are used as qualifications in repetative queries. + attributes) which are used as qualifications in repetitive queries. Inappropriate use will result in slower performance, since update and insertion times are increased in the presence of indices. + + Indexes may also be used to enforce uniqueness of a table's primary key. + When an index is declared UNIQUE, multiple table rows with identical + index entries won't be allowed. + For this purpose, the goal is ensuring data consistency, not improving + performance, so the above caution about inappropriate use doesn't apply. + + Two forms of indices may be defined: @@ -17,25 +25,20 @@ For a value index, the key fields for the - index are specified as column names; a column may also have - an associated operator class. An operator class is used - to specify the operators to be used for a particular - index. For example, a btree index on four-byte integers - would use the int4_ops class; - this operator class includes - comparison functions for four-byte integers. The default - operator class is the appropriate operator class for that - field type. + index are specified as column names; multiple columns + can be specified if the index access method supports + multi-column indexes. For a functional index, an index is defined - on the result of a user-defined function applied + on the result of a function applied to one or more attributes of a single class. - These functional indices - can be used to obtain fast access to data + This is a single-column index (namely, the function result) + even if the function uses more than one input field. + Functional indices can be used to obtain fast access to data based on operators that would normally require some transformation to apply them to the base data. @@ -45,8 +48,8 @@ Postgres provides btree, rtree and hash access methods for - secondary indices. The btree access method is an implementation of - the Lehman-Yao high-concurrency btrees. The rtree access method + indices. The btree access method is an implementation of + Lehman-Yao high-concurrency btrees. The rtree access method implements standard rtrees using Guttman's quadratic split algorithm. The hash access method is an implementation of Litwin's linear hashing. We mention the algorithms used solely to indicate that all @@ -56,8 +59,9 @@ - The Postgres query optimizer will consider using btree indices in a scan - whenever an indexed attribute is involved in a comparison using one of: + The Postgres + query optimizer will consider using a btree index whenever + an indexed attribute is involved in a comparison using one of: < @@ -68,19 +72,6 @@ - - Both box classes support indices on the box data - type in Postgres. - The difference between them is that bigbox_ops - scales box coordinates down, to avoid floating point exceptions from - doing multiplication, addition, and subtraction on very large - floating-point coordinates. If the field on which your rectangles lie - is about 20,000 units square or larger, you should use - bigbox_ops. - The poly_ops operator class supports rtree - indices on polygon data. - - The Postgres query optimizer will consider using an rtree index whenever @@ -105,27 +96,54 @@ - Currently, only the BTREE access method supports multi-column - indexes. Up to 7 keys may be specified. + Currently, only the btree access method supports multi-column + indexes. Up to 16 keys may be specified by default (this limit + can be altered when building Postgres). - Use - to remove an index. - + An operator class can be specified for each + column of an index. The operator class identifies the operators to + be used by the index for that column. For example, a btree index on + four-byte integers would use the int4_ops class; + this operator class includes comparison functions for four-byte + integers. In practice the default operator class for the field's + datatype is usually sufficient. The main point of having operator classes + is that for some datatypes, there could be more than one meaningful + ordering. For an index on such a datatype, we could select which + ordering we wanted by selecting the proper operator class. There + are also some operator classes with special purposes: - - The int24_ops - operator class is useful for constructing indices on int2 data, and - doing comparisons against int4 data in query qualifications. - Similarly, int42_ops - support indices on int4 data that is to be compared against int2 data - in queries. + + + + The operator classes box_ops and + bigbox_ops both support rtree indices on the + box datatype. + The difference between them is that bigbox_ops + scales box coordinates down, to avoid floating point exceptions from + doing multiplication, addition, and subtraction on very large + floating-point coordinates. If the field on which your rectangles lie + is about 20,000 units square or larger, you should use + bigbox_ops. + + + + + + The int24_ops + operator class is useful for constructing indices on int2 data, and + doing comparisons against int4 data in query qualifications. + Similarly, int42_ops + support indices on int4 data that is to be compared against int2 data + in queries. + + + - The following select list returns all ops_names: + The following query shows all defined operator classes: SELECT am.amname AS acc_name, @@ -140,6 +158,12 @@ SELECT am.amname AS acc_name, + + Use + to remove an index. + + Keys @@ -193,7 +217,7 @@ Subject: Re: [QUESTIONS] PRIMARY KEY | UNIQUE So, the user selects the collection by its name. We therefore make sure, - withing the database, that names are unique. However, no other table in the + within the database, that names are unique. However, no other table in the database relates to the collections table by the collection Name. That would be very inefficient. -- 2.40.0