From: Craig Ringer <craig@postnewspapers.com.au>
To: Marinos Yannikos <mjy@geizhals.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: 1-/2-dimensional indexes for common columns, rationale?
Date: Thu, 27 Mar 2008 00:27:24 +0900
Message-ID: <47EA6B5C.4020908@postnewspapers.com.au> (raw)
In-Reply-To: <47EA6888.2050506@geizhals.at>
References: <47EA5B4D.5000300@geizhals.at>
<20080326150208.GH27264@a-kretschmer.de>
<47EA6888.2050506@geizhals.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, mjy@geizhals.at
Subject: Re: 1-/2-dimensional indexes for common columns, rationale?
In-Reply-To: <47EA6B5C.4020908@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox