主页 / dba / 问题 / 184087
Accepted
raphael
Asked: 2017-08-09 11:22:25 +0800 CST

分区约束不用于涉及按时间戳分区的表的连接

  • 772

我有一个分区表结构，如：

CREATE TABLE measurements (
    sensor_id bigint,
    tx timestamp,
    measurement int
);

CREATE TABLE measurements_201201(
    CHECK (tx >= '2012-01-01 00:00:00'::timestamp without time zone 
       AND tx < ('2012-01-01 00:00:00'::timestamp without time zone + '1 mon'::interval))    
)INHERITS (measurements);
CREATE INDEX ON measurements_201201(sensor_id);
CREATE INDEX ON measurements_201201(tx);
CREATE INDEX ON measurements_201201(sensor_id, tx);
....

等等。每个表大约有 20M 行。

如果我在子句中查询传感器样本和时间戳样本WHERE，查询计划会显示选择的正确表和使用的索引，例如：

SELECT *
FROM measurements
INNER JOIN sensors TABLESAMPLE BERNOULLI (0.01) USING (sensor_id)
WHERE tx BETWEEN '2015-01-04 05:00' AND '2015-01-04 06:00' 
    OR tx BETWEEN '2015-02-04 05:00' AND '2015-02-04 06:00' 
    OR tx BETWEEN '2014-03-05 05:00' AND '2014-04-07 06:00' ;

但是，如果我使用 CTE，或者将时间戳值放入表中（未显示，即使在临时表上有索引）。

WITH sensor_sample AS(
    SELECT sensor_id, start_ts, end_ts
    FROM sensors TABLESAMPLE BERNOULLI (0.01)
    CROSS JOIN (VALUES (TIMESTAMP '2015-01-04 05:00', TIMESTAMP '2015-01-04 06:00'),
        (TIMESTAMP '2015-02-04 05:00', TIMESTAMP '2015-02-04 06:00'),
        (TIMESTAMP  '2014-03-05 05:00', '2014-04-07 06:00') ) tstamps(start_ts, end_ts)
)

类似下面的东西

SET constraint_exclusion = on;
SELECT * FROM measurements
INNER JOIN sensor_sample USING (sensor_id)
WHERE tx BETWEEN start_ts AND end_ts

对每个表执行索引扫描。这仍然相对较快，但是随着查询复杂性的增加，这可能会变成 seq 扫描，这对于从有限的分区表子集（50 个中的 4-5 个）中检索约 40K 行最终会非常慢。

我担心这样的事情是问题所在。

对于非平凡的表达式，您必须在查询中重复或多或少的逐字条件，以使 Postgres 查询规划器了解它可以依赖 CHECK 约束。即使看起来多余！

如何改进分区和查询结构以减少对所有数据运行 seq 扫描的可能性？

postgresql partitioning

1 个回答

  1. Best Answer

    基于约束的排除 [CBE] 在查询计划的早期阶段执行，就在查询被解析、映射到实际关系并重写之后。（内部，规划器/优化器阶段）

    计划者不能假设“sensor_sample”表的任何内容。

    因此，除非您在查询中硬编码了值，否则规划器不会排除“分区”。

    我猜 CTE 变体会发生什么......计划器受到限制，因为您使用 TABLESAMPLE 并且即使子查询中的文字是静态的，整个子查询也可能被视为易失性。（这只是我的猜测，我不是规划器代码专家）

    从好的方面来说，结果为负的索引扫描速度非常快。（最多单页扫描！）所以除非你有超过 10000 个分区，否则我不会打扰。

    所以，直接回答你的问题：

    • 您无法进一步改进此数据结构。

    • 关于索引扫描 - 它们很便宜；

    • 关于顺序扫描 - 正如您在自己的示例中看到的那样，尽可能避免使用它们。

    • 1

相关问题