如何在PostgreSQL分区表中高效获取指定日期前最大时间戳行

问题描述 #

我创建了一张按年份范围分区的测量数据表，存储带时间戳的测量数据，并为时间戳字段创建了索引：

CREATE TABLE vision.measure_value_p (
    id_measure_channel integer NOT NULL,
    module_date timestamp without time zone NOT NULL,
    value_unit numeric(16,4)
) 
PARTITION BY RANGE (module_date);

CREATE TABLE vision.measure_value_2022 PARTITION OF vision.measure_value_p FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE vision.measure_value_2021 PARTITION OF vision.measure_value_p FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
...

CREATE INDEX measure_value_idx_p ON vision.measure_value_p USING btree (module_date);

我需要获取指定日期前时间戳最大的行，执行了以下查询：

SELECT * FROM vision.measure_value_p WHERE module_date < '2021-10-01' ORDER BY module_date DESC LIMIT 1;

查询结果正确，但通过EXPLAIN发现PostgreSQL会扫描所有符合条件的分区（如2021、2020等），仅跳过2022分区。即使在2021分区找到结果，仍会继续扫描其他分区。执行计划如下：

[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Async Capable": false,
      "Plans": [
        {
          "Node Type": "Gather Merge",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Workers Planned": 2,
          "Plans": [
            {
              "Node Type": "Sort",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Sort Key": ["measure_value_p.module_date DESC"],
              "Plans": [
                {
                  "Node Type": "Append",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": true,
                  "Async Capable": false,
                  "Subplans Removed": 0,
                  "Plans": [
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Member",
                      "Parallel Aware": true,
                      "Async Capable": false,
                      "Relation Name": "measure_value_2020",
                      "Alias": "measure_value_p_3",
                      "Filter": "(module_date < '2021-10-01 00:00:00'::timestamp without time zone)"
                    },
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Member",
                      "Parallel Aware": true,
                      "Async Capable": false,
                      "Relation Name": "measure_value_2021",
                      "Alias": "measure_value_p_4",
                      "Filter": "(module_date < '2021-10-01 00:00:00'::timestamp without time zone)"
                    },
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Member",
                      "Parallel Aware": true,
                      "Async Capable": false,
                      "Relation Name": "measure_value_2019",
                      "Alias": "measure_value_p_2",
                      "Filter": "(module_date < '2021-10-01 00:00:00'::timestamp without time zone)"
                    },
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Member",
                      "Parallel Aware": true,
                      "Async Capable": false,
                      "Relation Name": "measure_value_2018",
                      "Alias": "measure_value_p_1",
                      "Filter": "(module_date < '2021-10-01 00:00:00'::timestamp without time zone)"
                    },
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Member",
                      "Parallel Aware": true,
                      "Async Capable": false,
                      "Relation Name": "measure_value_default",
                      "Alias": "measure_value_p_5",
                      "Filter": "(module_date < '2021-10-01 00:00:00'::timestamp without time zone)"
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

我尝试创建了如下索引，但执行计划没有变化：

CREATE INDEX measure_value_idx_p ON vision.measure_value_p USING btree (module_date DESC LAST NULLS);

希望查询能从2021分区开始查找，找到结果后停止扫描其他分区，请问如何优化该查询以提升效率？

优化方案 #

1. 显式按分区优先级查询，利用UNION ALL提前终止扫描 #

PostgreSQL分区表默认会扫描所有符合WHERE条件的分区再整体排序，可手动指定从最新的目标分区开始查询，一旦拿到结果就停止：

SELECT * FROM vision.measure_value_2021 
WHERE module_date < '2021-10-01' 
ORDER BY module_date DESC LIMIT 1
UNION ALL
SELECT * FROM vision.measure_value_2020 
WHERE module_date < '2021-10-01' 
ORDER BY module_date DESC LIMIT 1
UNION ALL
SELECT * FROM vision.measure_value_2019 
WHERE module_date < '2021-10-01' 
ORDER BY module_date DESC LIMIT 1
...
ORDER BY module_date DESC LIMIT 1;

按分区时间从新到旧排列子查询，外层LIMIT 1会在获取到第一行有效结果后终止后续分区的扫描。

2. 确保分区索引被有效利用 #

当前执行计划显示全表扫描，说明分区索引未被触发。需确认每个分区都有独立的module_date索引：

-- 检查分区索引状态
SELECT tablename, indexname FROM pg_indexes WHERE schemaname = 'vision' AND tablename LIKE 'measure_value_%';

若缺少索引，手动为各分区创建降序索引：

CREATE INDEX measure_value_2021_idx ON vision.measure_value_2021 USING btree (module_date DESC);
CREATE INDEX measure_value_2020_idx ON vision.measure_value_2020 USING btree (module_date DESC);
-- 其他分区同理

可临时禁用全表扫描测试索引是否生效：

SET enable_seqscan = OFF;
SELECT * FROM vision.measure_value_p 
WHERE module_date < '2021-10-01' 
ORDER BY module_date DESC LIMIT 1;
SET enable_seqscan = ON;

3. 更新统计信息，辅助规划器决策 #

执行ANALYZE更新表的统计数据，让PostgreSQL更准确判断分区数据分布：

-- 分析整个分区表
ANALYZE vision.measure_value_p;
-- 或单独分析目标分区
ANALYZE vision.measure_value_2021;
ANALYZE vision.measure_value_2020;

4. 使用LATERAL子查询灵活遍历分区（PostgreSQL 9.3+支持） #

通过LATERAL按时间从新到旧遍历分区，找到第一个非空结果就停止：

SELECT mv.*
FROM (
    VALUES 
        ('vision.measure_value_2021'::regclass),
        ('vision.measure_value_2020'::regclass),
        ('vision.measure_value_2019'::regclass),
        ('vision.measure_value_2018'::regclass)
) AS partitions(part)
CROSS JOIN LATERAL (
    SELECT * FROM part 
    WHERE module_date < '2021-10-01' 
    ORDER BY module_date DESC LIMIT 1
) AS mv
ORDER BY mv.module_date DESC LIMIT 1;

这种方式无需硬编码每个分区的查询逻辑，适合分区数量较多的场景。

内容的提问来源于stack exchange，提问作者Zazak