Skip site navigation (1) Skip section navigation (2)

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

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Marinos Yannikos <mjy(at)geizhals(dot)at>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 1-/2-dimensional indexes for common columns, rationale?
Date: 2008-03-26 15:27:24
Message-ID: 47EA6B5C.4020908@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: PFCDate: 2008-03-26 16:24:46
Subject: Re: 1-/2-dimensional indexes for common columns, rationale?
Previous:From: A. KretschmerDate: 2008-03-26 15:25:18
Subject: Re: 1-/2-dimensional indexes for common columns, rationale?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group