Optimal order for creating a composite index in PostgreSQL with multiple conditions

Question

I have a table with three columns: user_id, customer_id, and order_id.

In my queries, I frequently filter the data using conditions like,

  ... WHERE user_id = 23434 AND customer_id = 234234 AND order_id IN [23334, 23423, 23452];

I want to optimize the query performance by creating a composite index on these columns, but I'm not sure in which order the columns should be included in the index.

Considering that the selectivity of the columns would be in order customer_id, ordre_id and user_id, what would be the optimal order for creating the composite index?

I could do (customer_id, order_id, user_id), but here to filter out records based on user_id, database has to visit every leaf node in the range of passed order_id.

But, If I do (customer_id, user_id, order_id), here it will scan a lot of indexes matching the user_id and do the filtering and then fetch the rows either from disk or shared_buffers. I am not sure here. If someone could provide more info on this.

Should I prioritize the columns based on their selectivity or follow a different approach?

I am more inclined towards (customer_id, order_id, user_id) but I need some clarification.

Answer 1

The selectivity of columns does not matter as long as you compare with =. I write that in bold, since it is a common, but wrong, element of DBA folklore that the selectivity matters.

What matters, however, is that the columns that are compared with = are first in the index definition.

So you only need to make sure that order_id is the last column in your three-column index.

---

To cast the net wider, it is seldom a good idea to only consider a single query when you create an index. You should consider the existing indexes and the rest of the workload. Let me give you some examples:

• If there is already an index on user_id, you should drop that index and instead create one on (user_id, customer_id, order_id). Getting rid of an index is a benefit that can suf´ggest a certain column order in the index.

• If some of your queries use WHERE clauses like WHERE user_id IN (1, 2, 3) AND customer_id IN (4, 5, 6), you should put the more selective column first in the index. For comparisons other than =, it matters.

• If you already have an index on (customer_id, order_id), perhaps you shouldn't create an additional index. Sure, that index won't be the perfect one, but if user_id is not very selective, it might be good enough.