Message-ID: <47EA6B5C.4020908@postnewspapers.com.au>
Date: Thu, 27 Mar 2008 00:27:24 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Marinos Yannikos <mjy@geizhals.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: 1-/2-dimensional indexes for common columns, rationale?
References: <47EA5B4D.5000300@geizhals.at>
	<20080326150208.GH27264@a-kretschmer.de>
	<47EA6888.2050506@geizhals.at>
In-Reply-To: <47EA6888.2050506@geizhals.at>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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