多分区Postgres表的高效查询

Question

我刚刚重组了我的数据库以在 Postgres 8.2 中使用 分区 。现在我遇到了查询性能的问题：

SELECT *
FROM my_table
WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11'
ORDER BY id DESC
LIMIT 100;

表中有 4500 万行。在分区之前，这将使用反向索引扫描，并在达到限制时立即停止。

分区后（在 time_stamp 范围内），Postgres 对主表和相关分区进行完整索引扫描并合并结果，对它们进行排序，然后应用限制。这需要太长的时间。

我可以用以下方法修复它：

SELECT * FROM (
  SELECT *
  FROM my_table_part_a
  WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11'
  ORDER BY id DESC
  LIMIT 100) t
UNION ALL
SELECT * FROM (
  SELECT *
  FROM my_table_part_b
  WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11'
  ORDER BY id DESC
  LIMIT 100) t
UNION ALL
  ... and so on ...
ORDER BY id DESC
LIMIT 100

这运行得很快。时间戳超出范围的分区甚至不包含在查询计划中。

我的问题是：在 Postgres 8.2 中是否可以使用一些提示或语法来防止查询规划器扫描整个表，但仍然使用仅引用主表的简单语法？

基本上，我可以避免在当前定义的每个分区上动态构建大型 UNION 查询的痛苦吗？

编辑：我启用了constraint_exclusion（感谢@Vinko Vrsalovic）

Answer 1

您是否尝试过约束排除（您链接到的文档中的第 5.9.4 节）

约束排除是一个查询
改进的优化技术
分区表的性能
以所描述的方式定义
多于。举个例子：

 SET constraint_exclusion = on; 
 SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; 

没有
约束排除，上面的查询
会扫描每个分区
测量表。有约束
启用排除后，规划器将
检查每个的约束条件
并尝试证明
不需要扫描分区，因为
它不能包含任何行会议
查询的 WHERE 子句。当
planner可以证明这一点，它排除了
查询计划中的分区。

您可以使用 EXPLAIN 命令来
显示计划之间的差异
与constraint_exclusion打开和a
计划关闭它。

Answer 2

我遇到了类似的问题，可以通过在 WHERE 中转换条件来解决。
EG：（假设time_stamp列是timestamptz类型）

WHERE time_stamp >= '2010-02-10'::timestamptz and time_stamp < '2010-02-11'::timestamptz

另外，确保表上的CHECK条件以相同的方式定义......
例如：
检查（时间戳<'2010-02-10'::timestamptz）

Answer 3

我遇到了同样的问题，在我的情况下，它归结为两个原因：

1. 我索引了 timestamp with time zone 类型的列，并通过该列的 timestamp Without time 类型进行了分区约束。

2. 修复所有子表的约束后，需要ANALYZE。

编辑：另一点知识 - 重要的是要记住约束排除（允许 PG 根据您的分区标准跳过扫描某些表）不起作用，引用：non-immutable诸如 CURRENT_TIMESTAMP 之类的函数

我收到了 CURRENT_DATE 的请求，这是我的问题的一部分。