I have a PostgreSQL table named orders, which I have partitioned based on the
user_idcolumn using LIST partitioning using,
PARITION BY LIST lower(right(user_id, 2))
The table has columns
order_id, among others. Before partition, I was frequently querying the table using the following query pattern:
SELECT * FROM orders WHERE customer_id = 234234 AND order_id = 234234; SELECT * FROM orders WHERE order_id = 234234;
Now, due to partition I am using as below:
SELECT * FROM orders WHERE user_id = 234234 AND customer_id = 234234 AND order_id = 234234 AND lower(right(user_id, 2)) = '34';
I've noticed that partition pruning is not fully utilized because I must include
lower(right(user_id, 2)) = '34' in the WHERE clause to trigger pruning.
However, I don't have a specific need to filter on the user_id directly in this query.
My questions are:
Should I stop passing
user_idin the WHERE clause since I have already partitioned the table based on it?
Would it be beneficial to create an index on
(user_id, customer_id, order_id)to optimize the query performance?
Alternatively, should I create an index on
(lower(right(user_id, 2)), customer_id, order_id)and omit passing user_id in the WHERE clause for better pruning?
Should I also have an index on
(order_id, lower(right(user_id,2)))for the second query? Selectivity of
order_idwill be higher than
I want to ensure that the partition pruning is utilized optimally while maintaining good query performance. Any advice or best practices regarding indexing and partitioning in this scenario would be greatly appreciated.