To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-docs@postgresql.org, Teodor Sigaev <teodor@sigaev.ru>
Subject: Re: Multicolumn index doc out of date? 
In-reply-to: <20050912142647.GA34685@winnie.fuhr.org> 
References: <20050912142647.GA34685@winnie.fuhr.org>
Comments: In-reply-to Michael Fuhr <mike@fuhr.org>
	message dated "Mon, 12 Sep 2005 08:26:47 -0600"
Date: Mon, 12 Sep 2005 15:31:04 -0400
Message-ID: <23966.1126553464@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Michael Fuhr <mike@fuhr.org> writes:
> So isn't the following paragraph from "Multicolumn Indexes" out of
> date?

>   The query planner can use a multicolumn index for queries that
>   involve the leftmost column in the index definition plus any
>   number of columns listed to the right of it, without a gap.  For
>   example, an index on (a, b, c) can be used in queries involving
>   all of a, b, and c, or in queries involving both a and b, or in
>   queries involving only a, but not in other combinations.  (In a
>   query involving a and c the planner could choose to use the index
>   for a, while treating c like an ordinary unindexed column.)

Yeah, I had missed that part of the manual while doing the multicolumn
rules change.  I've replaced it with this:

:   A multicolumn B-tree index can be used with query conditions that
:   involve any subset of the index's columns, but the index is most
:   efficient when there are constraints on the leading (leftmost)
:   columns. The exact rule is that equality constraints on leading columns,
:   plus any inequality constraints on the first column that does not have
:   an equality constraint, will be used to limit the portion of the index
:   that is scanned. Constraints on columns to the right of these columns
:   are checked in the index, so they save visits to the table proper, but
:   they do not reduce the portion of the index that has to be scanned. For
:   example, given an index on (a, b, c) and a query condition WHERE a = 5
:   AND b >= 42 AND c < 77, the index would have to be scanned from the
:   first entry with a = 5 and b = 42 up through the last entry with a =
:   5. Index entries with c >= 77 would be skipped, but they'd still have to
:   be scanned through. This index could in principle be used for queries
:   that have constraints on b and/or c with no constraint on a --- but
:   the entire index would have to be scanned, so in most cases the planner
:   would prefer a sequential table scan over using the index.
:   
:   A multicolumn GiST index can only be used when there is a query
:   condition on its leading column. As with B-trees, conditions on
:   additional columns restrict the entries returned by the index, but do
:   not in themselves aid the index search.

I believe the above is accurate about btree, but I'm not so sure about
GiST --- Teodor, any comments?

			regards, tom lane