<!-- $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>

</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.

<!--

$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

-->

</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">

</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 providesthree access methods for indexes:

Postgres providesfour access methods for indexes:

<variablelist>

<varlistentry>

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>GIST</term>

<listitem>

<para>

Generalized Index Search Trees.

</para>

</listitem>

</varlistentry>

</variablelist>

</para>

</listitem>

</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>

</para>

<para>

Postgres provides btree, rtreeandhash access methods for

Postgres provides btree, rtree, hash,andGiST 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.

access methods).

</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>

<para>

Currently, only the btree accessmethod supports multi-column

Currently, only the btreeand gistaccessmethods 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>

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

floating-point coordinates.(Note: this was true some time ago,

</para>

</listitem>

</itemizedlist>

The following query shows all defined operator classes:

<programlisting>

SELECT am.amname ASacc_name,

SELECT am.amname ASacc_method,

opc.opcname AS ops_name,

opr.oprname AS ops_comp

FROM pg_am am, pg_amop amop,

pg_opclass opc, pg_operator opr

WHERE amop.amopid = am.oid AND

amop.amopclaid = opc.oid AND

amop.amopopr = opr.oid

ORDER BYacc_name, ops_name, ops_comp

ORDER BYacc_method, ops_name, ops_comp

</programlisting>

</para>

</refsect2>

newind->il_info= (IndexInfo*)palloc(sizeof(IndexInfo));

memcpy(newind->il_info,indexInfo,sizeof(IndexInfo));

newind->il_info->ii_Predicate=copyObject(indexInfo->ii_Predicate);

newind->il_info->ii_Predicate= (List*)

copyObject(indexInfo->ii_Predicate);

newind->il_next=ILHead;

ILHead=newind;

if (indexInfo->ii_Predicate!=NULL)

if (indexInfo->ii_Predicate!=NIL)

{

predString=nodeToString(indexInfo->ii_Predicate);

predText=DatumGetTextP(DirectFunctionCall1(textin,

heap_freetuple(tuple);

}

UpdateIndexPredicate(Oidindexoid,Node*oldPred,Node*predicate)

{

Node*newPred;

char*predString;

text*predText;

Relationpg_index;

HeapTupletuple;

HeapTuplenewtup;

inti;

Datumvalues[Natts_pg_index];

charnulls[Natts_pg_index];

charreplace[Natts_pg_index];

newPred=NULL;

if (predicate!=NULL)

{

newPred= (Node*)make_orclause(lcons(make_andclause((List*)predicate),

lcons(make_andclause((List*)oldPred),

NIL)));

newPred= (Node*)cnfify((Expr*)newPred, true);

}

if (newPred!=NULL)

{

predString=nodeToString(newPred);

predText=DatumGetTextP(DirectFunctionCall1(textin,

CStringGetDatum(predString)));

pfree(predString);

}

predText=DatumGetTextP(DirectFunctionCall1(textin,

CStringGetDatum("")));

pg_index=heap_openr(IndexRelationName,RowExclusiveLock);

tuple=SearchSysCache(INDEXRELID,

ObjectIdGetDatum(indexoid),

0,0,0);

if (!HeapTupleIsValid(tuple))

elog(ERROR,"UpdateIndexPredicate: cache lookup failed for index %u",

indexoid);

for (i=0;i<Natts_pg_index;i++)

{

nulls[i]=heap_attisnull(tuple,i+1) ?'n' :' ';

replace[i]=' ';

values[i]= (Datum)NULL;

}

replace[Anum_pg_index_indpred-1]='r';

values[Anum_pg_index_indpred-1]=PointerGetDatum(predText);

newtup=heap_modifytuple(tuple,pg_index,values,nulls,replace);

simple_heap_update(pg_index,&newtup->t_self,newtup);

heap_freetuple(newtup);

ReleaseSysCache(tuple);

heap_close(pg_index,RowExclusiveLock);

pfree(predText);

}

pfree(predString);

}

ii->ii_Predicate=NULL;

ii->ii_Predicate=NIL;

ii->ii_Unique=indexStruct->indisunique;

Datumattdata[INDEX_MAX_KEYS];

charnulls[INDEX_MAX_KEYS];

doublereltuples;

Node*predicate=indexInfo->ii_Predicate;

List*predicate=indexInfo->ii_Predicate;

TupleTabletupleTable;

TupleTableSlot*slot;

ExprContext*econtext;

if (predicate!=NULL)

if (predicate!=NIL)

{

tupleTable=ExecCreateTupleTable(1);

slot=ExecAllocTableSlot(tupleTable);

if (predicate!=NULL)

if (predicate!=NIL)

{

if (!tupleIsAlive)

continue;

ExecStoreTuple(heapTuple,slot,InvalidBuffer, false);

if (!ExecQual((List*)predicate,econtext, false))

if (!ExecQual(predicate,econtext, false))

continue;

}

heap_endscan(scan);

if (predicate!=NULL)

if (predicate!=NIL)

ExecDropTupleTable(tupleTable, true);

FreeExprContext(econtext);

indexInfo->ii_NumIndexAttrs=1;

indexInfo->ii_NumKeyAttrs=1;

indexInfo->ii_KeyAttrNumbers[0]=1;

indexInfo->ii_Predicate=NULL;

indexInfo->ii_Predicate=NIL;

indexInfo->ii_FuncOid=InvalidOid;

indexInfo->ii_Unique= false;