|
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 >Index Access Method Interface Definition</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="Internals" HREF="internals.html"><LINK REL="PREVIOUS" TITLE="Further Reading" HREF="geqo-biblio.html"><LINK REL="NEXT" TITLE="Index Access Method Functions" HREF="index-functions.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="geqo-biblio.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="geqo.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="gist.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="index-functions.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="CHAPTER" ><H1 ><A NAME="INDEXAM" ></A >Chapter 48. Index Access Method Interface Definition</H1 ><DIV CLASS="TOC" ><DL ><DT ><B >Table of Contents</B ></DT ><DT >48.1. <A HREF="indexam.html#INDEX-CATALOG" >Catalog Entries for Indexes</A ></DT ><DT >48.2. <A HREF="index-functions.html" >Index Access Method Functions</A ></DT ><DT >48.3. <A HREF="index-scanning.html" >Index Scanning</A ></DT ><DT >48.4. <A HREF="index-locking.html" >Index Locking Considerations</A ></DT ><DT >48.5. <A HREF="index-unique-checks.html" >Index Uniqueness Checks</A ></DT ><DT >48.6. <A HREF="index-cost-estimation.html" >Index Cost Estimation Functions</A ></DT ></DL ></DIV ><P > This chapter defines the interface between the core <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > system and <I CLASS="FIRSTTERM" >index access methods</I >, which manage individual index types. The core system knows nothing about indexes beyond what is specified here, so it is possible to develop entirely new index types by writing add-on code. </P ><P > All indexes in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > are what are known technically as <I CLASS="FIRSTTERM" >secondary indexes</I >; that is, the index is physically separate from the table file that it describes. Each index is stored as its own physical <I CLASS="FIRSTTERM" >relation</I > and so is described by an entry in the <TT CLASS="STRUCTNAME" >pg_class</TT > catalog. The contents of an index are entirely under the control of its index access method. In practice, all index access methods divide indexes into standard-size pages so that they can use the regular storage manager and buffer manager to access the index contents. (All the existing index access methods furthermore use the standard page layout described in <A HREF="storage-page-layout.html" >Section 50.3</A >, and they all use the same format for index tuple headers; but these decisions are not forced on an access method.) </P ><P > An index is effectively a mapping from some data key values to <I CLASS="FIRSTTERM" >tuple identifiers</I >, or <ACRONYM CLASS="ACRONYM" >TIDs</ACRONYM >, of row versions (tuples) in the index's parent table. A TID consists of a block number and an item number within that block (see <A HREF="storage-page-layout.html" >Section 50.3</A >). This is sufficient information to fetch a particular row version from the table. Indexes are not directly aware that under MVCC, there may be multiple extant versions of the same logical row; to an index, each tuple is an independent object that needs its own index entry. Thus, an update of a row always creates all-new index entries for the row, even if the key values did not change. Index entries for dead tuples are reclaimed (by vacuuming) when the dead tuples themselves are reclaimed. </P ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="INDEX-CATALOG" >48.1. Catalog Entries for Indexes</A ></H1 ><P > Each index access method is described by a row in the <TT CLASS="STRUCTNAME" >pg_am</TT > system catalog (see <A HREF="catalog-pg-am.html" >Section 42.3</A >). The principal contents of a <TT CLASS="STRUCTNAME" >pg_am</TT > row are references to <A HREF="catalog-pg-proc.html" ><TT CLASS="STRUCTNAME" >pg_proc</TT ></A > entries that identify the index access functions supplied by the access method. The APIs for these functions are defined later in this chapter. In addition, the <TT CLASS="STRUCTNAME" >pg_am</TT > row specifies a few fixed properties of the access method, such as whether it can support multicolumn indexes. There is not currently any special support for creating or deleting <TT CLASS="STRUCTNAME" >pg_am</TT > entries; anyone able to write a new access method is expected to be competent to insert an appropriate row for themselves. </P ><P > To be useful, an index access method must also have one or more <I CLASS="FIRSTTERM" >operator classes</I > defined in <A HREF="catalog-pg-opclass.html" ><TT CLASS="STRUCTNAME" >pg_opclass</TT ></A >, <A HREF="catalog-pg-amop.html" ><TT CLASS="STRUCTNAME" >pg_amop</TT ></A >, and <A HREF="catalog-pg-amproc.html" ><TT CLASS="STRUCTNAME" >pg_amproc</TT ></A >. These entries allow the planner to determine what kinds of query qualifications can be used with indexes of this access method. Operator classes are described in <A HREF="xindex.html" >Section 32.14</A >, which is prerequisite material for reading this chapter. </P ><P > An individual index is defined by a <A HREF="catalog-pg-class.html" ><TT CLASS="STRUCTNAME" >pg_class</TT ></A > entry that describes it as a physical relation, plus a <A HREF="catalog-pg-index.html" ><TT CLASS="STRUCTNAME" >pg_index</TT ></A > entry that shows the logical content of the index — that is, the set of index columns it has and the semantics of those columns, as captured by the associated operator classes. The index columns (key values) can be either simple columns of the underlying table or expressions over the table rows. The index access method normally has no interest in where the index key values come from (it is always handed precomputed key values) but it will be very interested in the operator class information in <TT CLASS="STRUCTNAME" >pg_index</TT >. Both of these catalog entries can be accessed as part of the <TT CLASS="STRUCTNAME" >Relation</TT > data structure that is passed to all operations on the index. </P ><P > Some of the flag columns of <TT CLASS="STRUCTNAME" >pg_am</TT > have nonobvious implications. The requirements of <TT CLASS="STRUCTFIELD" >amcanunique</TT > are discussed in <A HREF="index-unique-checks.html" >Section 48.5</A >, and those of <TT CLASS="STRUCTFIELD" >amconcurrent</TT > in <A HREF="index-locking.html" >Section 48.4</A >. The <TT CLASS="STRUCTFIELD" >amcanmulticol</TT > flag asserts that the access method supports multicolumn indexes, while <TT CLASS="STRUCTFIELD" >amoptionalkey</TT > asserts that it allows scans where no indexable restriction clause is given for the first index column. When <TT CLASS="STRUCTFIELD" >amcanmulticol</TT > is false, <TT CLASS="STRUCTFIELD" >amoptionalkey</TT > essentially says whether the access method allows full-index scans without any restriction clause. Access methods that support multiple index columns <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >must</I ></SPAN > support scans that omit restrictions on any or all of the columns after the first; however they are permitted to require some restriction to appear for the first index column, and this is signaled by setting <TT CLASS="STRUCTFIELD" >amoptionalkey</TT > false. <TT CLASS="STRUCTFIELD" >amindexnulls</TT > asserts that index entries are created for NULL key values. Since most indexable operators are strict and hence cannot return TRUE for NULL inputs, it is at first sight attractive to not store index entries for null values: they could never be returned by an index scan anyway. However, this argument fails when an index scan has no restriction clause for a given index column. In practice this means that indexes that have <TT CLASS="STRUCTFIELD" >amoptionalkey</TT > true must index nulls, since the planner might decide to use such an index with no scan keys at all. A related restriction is that an index access method that supports multiple index columns <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >must</I ></SPAN > support indexing null values in columns after the first, because the planner will assume the index can be used for queries that do not restrict these columns. For example, consider an index on (a,b) and a query with <TT CLASS="LITERAL" >WHERE a = 4</TT >. The system will assume the index can be used to scan for rows with <TT CLASS="LITERAL" >a = 4</TT >, which is wrong if the index omits rows where <TT CLASS="LITERAL" >b</TT > is null. It is, however, OK to omit rows where the first indexed column is null. (GiST currently does so.) Thus, <TT CLASS="STRUCTFIELD" >amindexnulls</TT > should be set true only if the index access method indexes all rows, including arbitrary combinations of null values. </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="geqo-biblio.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="index-functions.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Further Reading</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="internals.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Index Access Method Functions</TD ></TR ></TABLE ></DIV ></BODY ></HTML >