Author:This is from a reply to a question on the e-mail list by Paul M. Aoki on 1998-08-11.

A *partial index*
is an index built over a subset of a table; the subset is defined by
a predicate. Postgres
supported partial indices with arbitrary
predicates. I believe IBM's DB2
for AS/400 supports partial indices
using single-clause predicates.

The main motivation for partial indices is this:
if all of the queries you ask that can
profitably use an index fall into a certain range, why build an index
over the whole table and suffer the associated space/time costs?
(There are other reasons too; see
*Stonebraker, M, 1989b* for details.)

The machinery to build, update and query partial indices isn't too bad. The hairy parts are index selection (which indices do I build?) and query optimization (which indices do I use?); i.e., the parts that involve deciding what predicate(s) match the workload/query in some useful way. For those who are into database theory, the problems are basically analogous to the corresponding materialized view problems, albeit with different cost parameters and formulae. These are, in the general case, hard problems for the standard ordinal SQL types; they're super-hard problems with black-box extension types, because the selectivity estimation technology is so crude.

Check *Stonebraker, M, 1989b*,
*Olson, 1993*,
and
*Seshardri, 1995*
for more information.