aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2001-07-16 05:07:00 +0000
committerTom Lane <tgl@sss.pgh.pa.us>2001-07-16 05:07:00 +0000
commitf31dc0ada731d89313dbca7ef5da91d674fc640c (patch)
treea9c8343c489be6f99c5a9dff58838f72cf7d5dc4 /doc/src
parent237e5dfa581503b2ab877c73eecde517d284563c (diff)
downloadpostgresql-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.sgml15
-rw-r--r--doc/src/sgml/ref/create_index.sgml75
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>