Re: Index on multiple columns VS multiple index

Поиск
Список
Период
Сортировка
От Brice André
Тема Re: Index on multiple columns VS multiple index
Дата
Msg-id CAOBG12nTYcd-QfhLBg2UQK1cRQwQh8_0bcjqhwrgac0DDTrAaw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Index on multiple columns VS multiple index  (Erik Darling <edarling80@gmail.com>)
Ответы Re: Index on multiple columns VS multiple index
Список pgsql-sql
Дерево обсуждения
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@gmail.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.katz@excoventures.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.katz@excoventures.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


В списке pgsql-sql по дате отправления: