2

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.

1
  • For equality filters, the order of expressions in a multicolumn index does not matter. Move expressions for range conditions to the rear. What can matter, though: Leading index expressions can be used much more efficiently when other (trailing) index expressions are not used for a query (other queries). And more selective columns are more likely to be useful that way, as hardly selective items don't usually benefit from an index to begin with. Jul 14, 2023 at 22:08

1 Answer 1

3

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.

4
  • Very strictly speaking a more selective key column first might avoid another lookup further down the intermediate leafs of a B+tree index. But it's unlikely to make much difference so on the whole you're right. Jul 14, 2023 at 17:39
  • @Charlieface PostgreSQL descends the b-tree index only once. Then it only reads on the leaf page level (leaf pages are linked) until it has found all matching index entries. If all comparisons are =, the number of leaf entries scanned that way will be equal, no matter what the order of entries in the index is. So it does not matter at all. PostgreSQL does not have an index skip scan, where it descends the tree multiple times. Jul 14, 2023 at 19:03
  • Sorry I was talking about a case where you do a normal seek and the row doesn't exist, and what I meant to say was: if it doesn't even find any intermediate nodes it won't get to the leaf at all. Whereas if you swap the order then it may descend further into the index. But as I say it's unlikely to make much difference. Jul 15, 2023 at 22:24
  • @Charlieface Ah, I get you now. True, that may make a minuscule difference. Jul 17, 2023 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.