Re: Query not using the indexes properly.

On Thu, Oct 1, 2009 at 10:21 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:

>
> Interesting. I would have thought the order of the fields would not
> matter. I don't have to rewrite the query do I?
>
>
No. For multi-column indices, however, postgres can, starting at the
leftmost in the index, use as many columns as match equality comparisons
plus one column using an inequality comparison.

From our fine manual, section 11.3:

"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."

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com