我有一个名为 order 的 PostgreSQL 表,我user_id
使用 LIST 分区根据列对其进行了分区,
PARITION BY LIST lower(right(user_id, 2))
该表包含customer_id
和order_id
等列。在分区之前,我经常使用以下查询模式来查询表:
SELECT *
FROM orders
WHERE customer_id = 234234 AND order_id = 234234;
SELECT *
FROM orders
WHERE order_id = 234234;
现在,由于分区,我使用如下:
SELECT *
FROM orders
WHERE user_id = 234234 AND customer_id = 234234 AND order_id = 234234 AND lower(right(user_id, 2)) = '34';
我注意到分区修剪没有得到充分利用,因为我必须包含lower(right(user_id, 2)) = '34'
在 WHERE 子句中才能触发修剪。
但是,我没有特定的需要直接在此查询中过滤 user_id 。
我的问题是:
user_id
既然我已经基于 WHERE 子句对表进行了分区,我是否应该停止传递WHERE 子句?创建索引是否有利于
(user_id, customer_id, order_id)
优化查询性能?(lower(right(user_id, 2)), customer_id, order_id)
或者,我应该在 WHERE 子句中创建索引并省略传递 user_id 以便更好地修剪吗?(order_id, lower(right(user_id,2)))
我还应该为第二个查询建立索引吗?的选择性order_id
将高于user_id
。
我想确保分区修剪得到最佳利用,同时保持良好的查询性能。在这种情况下,任何有关索引和分区的建议或最佳实践将不胜感激。
分区是一种数据管理工具(例如当您想要
DROP
一次对整个数据进行分区时)。它不是一个旨在提高查找性能(例如SELECT
查询类型)的工具,并且在某些情况下它实际上会稍微损害性能。索引旨在提高查找性能,并且比分区效率更高,因为分区以线性方式划分数据,而索引以对数方式划分数据。
是的,因为听起来您根本不需要分区,并且
user_id
您的用例似乎也不需要分区,假设 anorder_id
相当独特,因为它不会进一步减少数据。不,因为
user_id
无论如何都不会进一步过滤数据(再次基于字段相对于它的基数),所以添加到查询和索引中order_id
是多余的。user_id
相反,您应该创建一个索引(order_id)
来(order_id, customer_id)
覆盖您的示例查询。(重要的是,您首先要引导order_id
,因此它涵盖了这两个查询。)不。甚至不确定仅查看右侧 2 位数字的
user_id
用途。不。坚持前面提到的简单索引
(order_id, customer_id)
。这涵盖了您的查询,并且不能真正提高效率,除非有另一个字段可以进一步减少返回的数据。此外,选择性对于相等搜索并不重要,这正是您的两个示例查询所做的。