[Rt-devel] Redundant Postgres indexes

[Andrew Sullivan]

On Wed, Oct 06, 2004 at 05:48:37PM +0200, Andrew Snare wrote:
> the Postgres docs
> (<http://www.postgresql.org/docs/7.4/interactive/indexes-multicolumn.html>),
> a multicolumn index on (a,b,c) can also be used by the query planner as
> an index for a or (a,b). In addition, there's an implicit index on the
> primary key of any table. This leads to the following changes to
> schema.Pg.

The general rule is that any leftwards subset of the index is also
usable in the index.  So this is right, but you do have to be aware
of selectivity issues (sometimes a,b is selective enough to give you
an index scan but a from the index is not).

> I'm not sure what the performance impact is of maintaining these
> indexes; it's probably not that high.

Multi-column indexes are pretty expensive to maintain at write time,
actually.

-- 
Andrew Sullivan  | ajs at crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie