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
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
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
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.