Re: Index on multiple columns VS multiple index

Hello Erik,

Thanks for this very useful link.

I think that I found the answers to nearly all my questions. I must still
think a little about it but it was very interesting.

But I have still one question that remains :

suppose I define an index on ('a', 'b') columns, will it be useful for a
search on 'a' column only, or will it be ignore by postgresl ?

Regards,
Brice

PS : from what I read, I think that I should consider two indices :

('a', 'b') and
('a', 'c', 'b')

because, from what I understand, multi-column index is faster, and, as
there is no more seeking after an inequality, 'b' should be at the right of
all columns of index (and as 'a' is the most selective column in my case,
it should be at the left of the index definition).

but if the answer to the above question is that postgresl does not use a
multi-columns index on a single column search, I should probably also
consider a third index on 'a' only... But this is still not clear for me...

2014/1/2 Erik Darling <edarling80(at)gmail(dot)com>

> 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
>>
>>