Sophie: postgresql8.3-docs-8.3.6-2mdv2008.1 x86

postgresql8.3-docs-8.3.6-2mdv2008.1.x86_64.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Multicolumn Indexes</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.3.6 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Indexes"
HREF="indexes.html"><LINK
REL="PREVIOUS"
TITLE="Index Types"
HREF="indexes-types.html"><LINK
REL="NEXT"
TITLE="Indexes and ORDER BY"
HREF="indexes-ordering.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-02-03T04:34:16"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.3.6 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="indexes-types.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="indexes.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 11. Indexes</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="indexes.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="indexes-ordering.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="INDEXES-MULTICOLUMN"
>11.3. Multicolumn Indexes</A
></H1
><A
NAME="AEN17631"
></A
><P
>   An index can be defined on more than one column of a table.  For example, if
   you have a table of this form:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);</PRE
><P>
   (say, you keep your <TT
CLASS="FILENAME"
>/dev</TT
>
   directory in a database...) and you frequently make queries like:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT name FROM test2 WHERE major = <TT
CLASS="REPLACEABLE"
><I
>constant</I
></TT
> AND minor = <TT
CLASS="REPLACEABLE"
><I
>constant</I
></TT
>;</PRE
><P>
   then it might be appropriate to define an index on the columns
   <TT
CLASS="STRUCTFIELD"
>major</TT
> and
   <TT
CLASS="STRUCTFIELD"
>minor</TT
> together, e.g.:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE INDEX test2_mm_idx ON test2 (major, minor);</PRE
><P>
  </P
><P
>   Currently, only the B-tree and GiST index types support multicolumn
   indexes.  Up to 32 columns can be specified.  (This limit can be
   altered when building <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>; see the
   file <TT
CLASS="FILENAME"
>pg_config_manual.h</TT
>.)
  </P
><P
>   A multicolumn B-tree index can be used with query conditions that
   involve any subset of the index's columns, but the index is most
   efficient when there are constraints on the leading (leftmost) columns.
   The exact rule is that equality constraints on leading columns, plus
   any inequality constraints on the first column that does not have an
   equality constraint, will be used to limit the portion of the index
   that is scanned.  Constraints on columns to the right of these columns
   are checked in the index, so they save visits to the table proper, but
   they do not reduce the portion of the index that has to be scanned.
   For example, given an index on <TT
CLASS="LITERAL"
>(a, b, c)</TT
> and a
   query condition <TT
CLASS="LITERAL"
>WHERE a = 5 AND b &gt;= 42 AND c &lt; 77</TT
>,
   the index would have to be scanned from the first entry with
   <TT
CLASS="LITERAL"
>a</TT
> = 5 and <TT
CLASS="LITERAL"
>b</TT
> = 42 up through the last entry with
   <TT
CLASS="LITERAL"
>a</TT
> = 5.  Index entries with <TT
CLASS="LITERAL"
>c</TT
> &gt;= 77 would be
   skipped, but they'd still have to be scanned through.
   This index could in principle be used for queries that have constraints
   on <TT
CLASS="LITERAL"
>b</TT
> and/or <TT
CLASS="LITERAL"
>c</TT
> with no constraint on <TT
CLASS="LITERAL"
>a</TT
>
   &mdash; but the entire index would have to be scanned, so in most cases
   the planner would prefer a sequential table scan over using the index.
  </P
><P
>   A multicolumn GiST index can be used with query conditions that
   involve any subset of the index's columns. Conditions on additional 
   columns restrict the entries returned by the index, but the condition on 
   the first column is the most important one for determining how much of 
   the index needs to be scanned.  A GiST index will be relatively 
   ineffective if its first column has only a few distinct values, even if 
   there are many distinct values in additional columns.
  </P
><P
>   Of course, each column must be used with operators appropriate to the index
   type; clauses that involve other operators will not be considered.
  </P
><P
>   Multicolumn indexes should be used sparingly.  In most situations,
   an index on a single column is sufficient and saves space and time.
   Indexes with more than three columns are unlikely to be helpful
   unless the usage of the table is extremely stylized.  See also
   <A
HREF="indexes-bitmap-scans.html"
>Section 11.5</A
> for some discussion of the
   merits of different index setups.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="indexes-types.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="indexes-ordering.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Index Types</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="indexes.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Indexes and <TT
CLASS="LITERAL"
>ORDER BY</TT
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>