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
