diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2001-07-16 05:07:00 +0000 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2001-07-16 05:07:00 +0000 |
commit | f31dc0ada731d89313dbca7ef5da91d674fc640c (patch) | |
tree | a9c8343c489be6f99c5a9dff58838f72cf7d5dc4 /doc/src | |
parent | 237e5dfa581503b2ab877c73eecde517d284563c (diff) | |
download | postgresql-f31dc0ada731d89313dbca7ef5da91d674fc640c.tar.gz postgresql-f31dc0ada731d89313dbca7ef5da91d674fc640c.zip |
Partial indexes work again, courtesy of Martijn van Oosterhout.
Note: I didn't force an initdb, figuring that one today was enough.
However, there is a new function in pg_proc.h, and pg_dump won't be
able to dump partial indexes until you add that function.
Diffstat (limited to 'doc/src')
-rw-r--r-- | doc/src/sgml/indices.sgml | 15 | ||||
-rw-r--r-- | doc/src/sgml/ref/create_index.sgml | 75 |
2 files changed, 63 insertions, 27 deletions
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index abda7fd77fe..d81ce775173 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1,4 +1,4 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v 1.19 2001/05/30 04:01:11 momjian Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v 1.20 2001/07/16 05:06:57 tgl Exp $ --> <chapter id="indexes"> <title id="indexes-title">Indexes</title> @@ -603,22 +603,11 @@ CREATE MEMSTORE ON <replaceable>table</replaceable> COLUMNS <replaceable>cols</r </para> </note> - <note> - <title>Note</title> - <para> - Partial indexes are not currently supported by - <productname>PostgreSQL</productname>, but they were once supported - by its predecessor <productname>Postgres</productname>, and much - of the code is still there. We hope to revive support for this - feature someday. - </para> - </note> - <para> A <firstterm>partial index</firstterm> is an index built over a subset of a table; the subset is defined by a predicate. <productname>Postgres</productname> - supported partial indexes with arbitrary + supports partial indexes with arbitrary predicates. I believe IBM's <productname>DB2</productname> for AS/400 supports partial indexes using single-clause predicates. diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index bc72fb1993a..e5194c255c6 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.19 2001/05/17 21:50:18 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.20 2001/07/16 05:06:57 tgl Exp $ Postgres documentation --> @@ -20,13 +20,15 @@ Postgres documentation </refnamediv> <refsynopsisdiv> <refsynopsisdivinfo> - <date>1999-07-20</date> + <date>2001-07-15</date> </refsynopsisdivinfo> <synopsis> CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable> - [ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable> ] [, ...] ) + [ USING <replaceable class="parameter">acc_method</replaceable> ] ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable> ] [, ...] ) + [ WHERE <replaceable class="parameter">predicate</replaceable> ] CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable> - [ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) [ <replaceable class="parameter">ops_name</replaceable> ] ) + [ USING <replaceable class="parameter">acc_method</replaceable> ] ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) [ <replaceable class="parameter">ops_name</replaceable> ] ) + [ WHERE <replaceable class="parameter">predicate</replaceable> ] </synopsis> <refsect2 id="R2-SQL-CREATEINDEX-1"> @@ -71,12 +73,12 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> </varlistentry> <varlistentry> - <term><replaceable class="parameter">acc_name</replaceable></term> + <term><replaceable class="parameter">acc_method</replaceable></term> <listitem> <para> The name of the access method to be used for the index. The default access method is BTREE. - Postgres provides three access methods for indexes: + Postgres provides four access methods for indexes: <variablelist> <varlistentry> @@ -106,6 +108,15 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> </para> </listitem> </varlistentry> + + <varlistentry> + <term>GIST</term> + <listitem> + <para> + Generalized Index Search Trees. + </para> + </listitem> + </varlistentry> </variablelist> </para> </listitem> @@ -137,6 +148,15 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">predicate</replaceable></term> + <listitem> + <para> + Defines the constraint expression for a partial index. + </para> + </listitem> + </varlistentry> </variablelist> </para> </refsect2> @@ -216,7 +236,7 @@ ERROR: Cannot create index: 'index_name' already exists. </para> <para> - Postgres provides btree, rtree and hash access methods for + Postgres provides btree, rtree, hash, and GiST access methods for indexes. 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. @@ -228,6 +248,32 @@ ERROR: Cannot create index: 'index_name' already exists. </para> <para> + When the <command>WHERE</command> clause is present, a + <firstterm>partial index</firstterm> is created. + A partial index is an index that contains entries for only a portion of + a table, usually a portion that is somehow more interesting than the + rest of the table. For example, if you have a table that contains both + billed and unbilled orders where the unbilled orders take up a small + fraction of the total table and yet that is an often used section, you + can improve performance by creating an index on just that portion. + </para> + + <para> + The expression used in the <command>WHERE</command> clause may refer + only to columns of the underlying table (but it can use all columns, + not only the one(s) being indexed). Currently, the + <productname>PostgreSQL</productname> planner can only devise query + plans that make use of a partial index when the predicate is built from + <command>AND</command> and <command>OR</command> combinations of + elements of the form + <firstterm>column</firstterm> + <firstterm>operator</firstterm> + <firstterm>constant</firstterm>. + However, more general predicates may still be useful in conjunction + with UNIQUE indexes, to enforce uniqueness over a subset of a table. + </para> + + <para> Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title"> to remove an index. </para> @@ -278,9 +324,10 @@ ERROR: Cannot create index: 'index_name' already exists. </para> <para> - Currently, only the btree access method supports multi-column + Currently, only the btree and gist access methods support multi-column indexes. Up to 16 keys may be specified by default (this limit - can be altered when building Postgres). + can be altered when building Postgres). Only btree currently supports + unique indexes. </para> <para> @@ -307,9 +354,9 @@ ERROR: Cannot create index: 'index_name' already exists. The difference between them is that <literal>bigbox_ops</literal> 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 - <literal>bigbox_ops</literal>. + floating-point coordinates. (Note: this was true some time ago, + but currently the two operator classes both use floating point + and are effectively identical.) </para> </listitem> </itemizedlist> @@ -319,7 +366,7 @@ ERROR: Cannot create index: 'index_name' already exists. The following query shows all defined operator classes: <programlisting> -SELECT am.amname AS acc_name, +SELECT am.amname AS acc_method, opc.opcname AS ops_name, opr.oprname AS ops_comp FROM pg_am am, pg_amop amop, @@ -327,7 +374,7 @@ SELECT am.amname AS acc_name, WHERE amop.amopid = am.oid AND amop.amopclaid = opc.oid AND amop.amopopr = opr.oid - ORDER BY acc_name, ops_name, ops_comp + ORDER BY acc_method, ops_name, ops_comp </programlisting> </para> </refsect2> |