Re: 1-/2-dimensional indexes for common columns, rationale?

Marinos Yannikos wrote:
>>
>> Which version do you have? Since 8.1 pg can use a so called 'bitmap
>> index scan', because of this feature i guess you don't need the index
>> foo3. (if you have 8.1 or higher)
>
> 8.3.1 - foo3 is being used though in presence of both foo1 and foo2,
> so I'd suppose that it's a better choice even with bitmap index scan
> available...
>
PostgreSQL can also partially use a multi-column index. For example, if
you dropped your index on (a) Pg could use index (a,b) to help with
queries for `a'. However, the index would be slower than an index on a
alone would be.

See:

http://www.postgresql.org/docs/8.3/interactive/indexes-multicolumn.html

As usual, the best answer is really "do some testing with your queries,
and with EXPLAIN ANALYZE, and see what works best". Test with inserts
too, because it's likely that the cost of updating each of the three
indexes isn't equal.

It might also be worth looking into using partial indexes if some of
your data is "hotter" than others and perhaps more worth the index
update cost.

--
Craig Ringer