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