Re: Index on multiple columns VS multiple index

This is a good article on the subject. I know it's not PG specific, but it
expands a bit on Jonathan's point.

http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/
On Jan 2, 2014 2:34 PM, "Jonathan S. Katz" <jonathan(dot)katz(at)excoventures(dot)com>
wrote:

> Hi Brice,
>
> On Jan 2, 2014, at 2:24 PM, Brice André wrote:
>
> Yes, except that on b, it's a inequality operator.
>
>
> 2014/1/2 Jonathan S. Katz <jonathan(dot)katz(at)excoventures(dot)com>
>
>> On Jan 2, 2014, at 2:17 PM, Brice André wrote:
>>
>> Hello everyone,
>>
>> I have a question concerning index : suppose I have a table with fields
>> 'a' and 'b' and that all requests perform WHERE clauses on 'a' field, and
>> some requests also perform WHERE clauses on 'b' fields. What is the best
>> approach for indexing strategy:
>>
>> - One index on 'a' and one on 'b'
>> - One index on both columns 'a' and 'b'
>> - A combination of both solutions ?
>>
>> Could you clarify your question a bit? Are you saying your queries are
>> predominantly
>>
>> SELECT ... FROM table WHERE a = ?
>>
>> With some queries that are
>>
>> SELECT ... FROM table WHERE a = ? AND b = ?
>>
>> Thanks,
>>
>> Jonathan
>>
>
> Moving your reply to the list.
>
> Assuming the data type you are using supports B-tree indexes, I can't
> think of any cases where inequality (specifically <> or !=) would use an
> index, so a single index on 'a' is what you are looking for.
>
> However, if you are doing anything with equality (<, <=, =, >=, >) then
> you would wnat a multi-column index on (a,b), in that column order.
>
> Best,
>
> Jonathan
>
>