public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Michael Fuhr <[email protected]>
Cc: [email protected]
Cc: Teodor Sigaev <[email protected]>
Subject: Re: Multicolumn index doc out of date?
Date: Mon, 12 Sep 2005 15:31:04 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Michael Fuhr <[email protected]> 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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Multicolumn index doc out of date?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox