|
Server : Apache/2.2.2 (Fedora) System : Linux App1.pathumtani.go.th 2.6.20-1.2320.fc5smp #1 SMP Tue Jun 12 19:40:16 EDT 2007 i686 User : apache ( 48) PHP Version : 5.2.9 Disable Function : NONE Directory : /proc/self/root/usr/share/doc/postgresql-8.1.9/html/ |
Upload File : |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>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.1.9 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="The SQL Language"
HREF="sql.html"><LINK
REL="PREVIOUS"
TITLE="UNION, CASE, and Related Constructs"
HREF="typeconv-union-case.html"><LINK
REL="NEXT"
TITLE="Index Types"
HREF="indexes-types.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="2007-04-20T04:40:08"></HEAD
><BODY
CLASS="CHAPTER"
><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.1.9 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="typeconv-union-case.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="typeconv.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="mvcc.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="indexes-types.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="INDEXES"
></A
>Chapter 11. Indexes</H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
>11.1. <A
HREF="indexes.html#INDEXES-INTRO"
>Introduction</A
></DT
><DT
>11.2. <A
HREF="indexes-types.html"
>Index Types</A
></DT
><DT
>11.3. <A
HREF="indexes-multicolumn.html"
>Multicolumn Indexes</A
></DT
><DT
>11.4. <A
HREF="indexes-bitmap-scans.html"
>Combining Multiple Indexes</A
></DT
><DT
>11.5. <A
HREF="indexes-unique.html"
>Unique Indexes</A
></DT
><DT
>11.6. <A
HREF="indexes-expressional.html"
>Indexes on Expressions</A
></DT
><DT
>11.7. <A
HREF="indexes-partial.html"
>Partial Indexes</A
></DT
><DT
>11.8. <A
HREF="indexes-opclass.html"
>Operator Classes</A
></DT
><DT
>11.9. <A
HREF="indexes-examine.html"
>Examining Index Usage</A
></DT
></DL
></DIV
><A
NAME="AEN14924"
></A
><P
> Indexes are a common way to enhance database performance. An index
allows the database server to find and retrieve specific rows much
faster than it could do without an index. But indexes also add
overhead to the database system as a whole, so they should be used
sensibly.
</P
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="INDEXES-INTRO"
>11.1. Introduction</A
></H1
><P
> Suppose we have a table similar to this:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE test1 (
id integer,
content varchar
);</PRE
><P>
and the application requires a lot of queries of the form
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT content FROM test1 WHERE id = <TT
CLASS="REPLACEABLE"
><I
>constant</I
></TT
>;</PRE
><P>
With no advance preparation, the system would have to scan the entire
<TT
CLASS="STRUCTNAME"
>test1</TT
> table, row by row, to find all
matching entries. If there are a lot of rows in
<TT
CLASS="STRUCTNAME"
>test1</TT
> and only a few rows (perhaps only zero
or one) that would be returned by such a query, then this is clearly an
inefficient method. But if the system has been instructed to maintain an
index on the <TT
CLASS="STRUCTFIELD"
>id</TT
> column, then 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.
</P
><P
> A similar approach is used in most books of non-fiction: terms and
concepts that are frequently looked up by readers are collected in
an alphabetic index at the end of the book. The interested reader
can scan the index relatively quickly and flip to the appropriate
page(s), rather than having to read the entire book to find the
material of interest. Just as it is the task of the author to
anticipate the items that the readers are likely to look up,
it is the task of the database programmer to foresee which indexes
will be of advantage.
</P
><P
> The following command would be used to create the index on the
<TT
CLASS="STRUCTFIELD"
>id</TT
> column, as discussed:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE INDEX test1_id_index ON test1 (id);</PRE
><P>
The name <TT
CLASS="STRUCTNAME"
>test1_id_index</TT
> can be chosen
freely, but you should pick something that enables you to remember
later what the index was for.
</P
><P
> To remove an index, use the <TT
CLASS="COMMAND"
>DROP INDEX</TT
> command.
Indexes can be added to and removed from tables at any time.
</P
><P
> 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 this would be more efficient
than a sequential table scan. But you may have to run the
<TT
CLASS="COMMAND"
>ANALYZE</TT
> command regularly to update
statistics to allow the query planner to make educated decisions.
See <A
HREF="performance-tips.html"
>Chapter 13</A
> for information about
how to find out whether an index is used and when and why the
planner may choose <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>not</I
></SPAN
> to use an index.
</P
><P
> Indexes can also benefit <TT
CLASS="COMMAND"
>UPDATE</TT
> and
<TT
CLASS="COMMAND"
>DELETE</TT
> commands with search conditions.
Indexes can moreover be used in join searches. Thus,
an index defined on a column that is part of a join condition can
significantly speed up queries with joins.
</P
><P
> After an index is created, the system has to keep it synchronized with the
table. This adds overhead to data manipulation operations.
Therefore indexes that are seldom or never used in queries
should be removed.
</P
></DIV
></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="typeconv-union-case.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-types.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><TT
CLASS="LITERAL"
>UNION</TT
>, <TT
CLASS="LITERAL"
>CASE</TT
>, and Related Constructs</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Index Types</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>