Re: Index on multiple columns VS multiple index

Поиск
Список
Период
Сортировка
От Brice André
Тема Re: Index on multiple columns VS multiple index
Дата
Msg-id CAOBG12krR4LOsompeUKBe2qVT5zHk-9+3K16H=AVTPGMt8yfNw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Index on multiple columns VS multiple index  ("Jonathan S. Katz" <jonathan.katz@excoventures.com>)
Ответы Re: Index on multiple columns VS multiple index
Список pgsql-sql
Дерево обсуждения
Hello Jonathan,

Sorry, it's probably my bad english that is confusing. When I sayed inequality, I ment operators like <= or >=. In fact, 'a' is an integer' which is a foreign key on a primary key of another table and b is a timestamp.

So, if I understand you, making an index on both ('a', 'b') will be faster than two separate indices ?

But, if yes, does this index can be useful for a search on 'a' only ? Or do I need a separate index for this ?

I generally also have a ORDER clause on 'b'. I suppose that the index will be a good point for it too ?

And, last question, I also have time-consuming queries that are of the form :

SELECT .. FROM table WHERE 'a'=x AND 'c'=y AND 'b' >= z

where 'c' is an integer, but that is not a foreign key. Does it makes sense to create an additional multi-column index on ('a', 'b', 'c') ? Does the order of declaration of columns in the index creation makes a difference ? (for example ('a', 'c', 'b')) ? And is this index useful for a search on 'a' and 'b' only ?

I am sorry for bothering you with all those questions, but I tried to get information from the doc, but I have big difficulties understanding multi-column index stuff...

Thanks for your help,

Brice


2014/1/2 Jonathan S. Katz <jonathan.katz@excoventures.com>
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 по дате отправления: