Re: If an index is based on 3 columns will a query using two of the columns utilize the index?

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> On Mon, Sep 12, 2005 at 10:05:36AM -0400, Reid Thompson wrote:
>> Why is that? In order to use an index, does the query have to utilize
>> the 'first' element of the index?

> The "leftmost part." There's no way to scan an index if you don't know
> the key. On a btree index, the key is ordered, and the columns at the
> left are more significant than those at the right. If you don't provide
> a value for the leftmost (first) column, there's no way to start
> scanning the index because there's no starting point.

Actually, btree doesn't have any particular problem with that --- it
just starts the scan at the beginning of the index. However the other
index types do all require a constraint on the first index column;
for instance hash has to be able to determine a hash value.

Greg Stark suggests here:
http://archives.postgresql.org/pgsql-hackers/2005-05/msg00966.php
that GiST could also be fixed to work with any subset of the index
columns, but it hasn't been done yet, unless Teodor and Oleg snuck
something in during that last round of GiST work.

regards, tom lane