Author: This is from a reply to a question on the email list by Paul M. Aoki (<aoki@CS.Berkeley.EDU>) on 1998-08-11.
Note: Partial indexes are not currently supported by PostgreSQL, but they were once supported by its predecessor Postgres, and much of the code is still there. We hope to revive support for this feature someday.
A partial index is an index built over a subset of a table; the subset is defined by a predicate. Postgres supported partial indexes with arbitrary predicates. I believe IBM's DB2 for AS/400 supports partial indexes using single-clause predicates.
The main motivation for partial indexes 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 indexes isn't too bad. The hairy parts are index selection (which indexes do I build?) and query optimization (which indexes 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.