Re: Index on multiple columns VS multiple index - Mailing list pgsql-sql

From Jonathan S. Katz
Subject Re: Index on multiple columns VS multiple index
Date
Msg-id 97DDB603-D01D-4355-872A-5913DBBC5268@excoventures.com
Whole thread Raw
In response to Index on multiple columns VS multiple index  (Brice André <brice@famille-andre.be>)
Responses Re: Index on multiple columns VS multiple index
Re: Index on multiple columns VS multiple index
List pgsql-sql
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 by date:

Previous
From: "Jonathan S. Katz"
Date:
Subject: Re: Index on multiple columns VS multiple index
Next
From: Erik Darling
Date:
Subject: Re: Index on multiple columns VS multiple index