Skip site navigation (1) Skip section navigation (2)

Re: Multicolumn index doc out of date?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-docs(at)postgresql(dot)org, Teodor Sigaev <teodor(at)sigaev(dot)ru>
Subject: Re: Multicolumn index doc out of date?
Date: 2005-09-12 19:31:04
Message-ID: 23966.1126553464@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-docs
Michael Fuhr <mike(at)fuhr(dot)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

In response to

Responses

pgsql-docs by date

Next:From: Bruce MomjianDate: 2005-09-16 19:41:11
Subject: Re: [HACKERS] statement_timeout logging
Previous:From: Neil ConwayDate: 2005-09-12 18:23:22
Subject: Re: [PATCHES] Clarifying Autovacuum docs in the release notes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group