PostgreSQL分区表动态裁剪与查询计划优化实战

-- 创建按月分区的订单表
CREATE TABLE orders (
    id          BIGSERIAL,
    order_no    VARCHAR(32) NOT NULL,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12,2),
    status      SMALLINT DEFAULT 0,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 创建子分区
CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02 PARTITION OF orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE orders_2026_08 PARTITION OF orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- 在分区键上创建索引
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- 计划时裁剪：常量条件
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-08';

-- 结果：只扫描 orders_2026_08 分区
-- Append (cost=0.00..100.00 rows=1000 width=80)
--   -> Seq Scan on orders_2026_08
--       Filter: (created_at >= '2026-08-01' AND created_at < '2026-08-08')

-- 执行时裁剪：参数化查询
PREPARE get_orders(TIMESTAMP) AS
    SELECT * FROM orders
    WHERE created_at >= $1 AND created_at < $1 + INTERVAL '7 days';

EXECUTE get_orders('2026-08-01');

-- PostgreSQL 14+自动支持执行时裁剪
-- 但generic plan可能不裁剪，需注意

-- 检查是否使用了Generic Plan
EXPLAIN (ANALYZE, COSTS OFF)
EXECUTE get_orders('2026-08-01');

-- 如果看到所有分区都被扫描，说明使用了Generic Plan
-- 解决方案1：强制Custom Plan
SET plan_cache_mode = force_custom_plan;

-- 解决方案2：在应用层使用参数化查询而非PREPARE
-- 大多数ORM默认使用扩展协议，始终使用Custom Plan

-- 先按月分区，再按user_id哈希子分区
CREATE TABLE orders (
    id BIGSERIAL,
    order_no VARCHAR(32),
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2),
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_08 PARTITION OF orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01')
    PARTITION BY HASH (user_id);

-- 每个月份下创建4个哈希子分区
CREATE TABLE orders_2026_08_h0 PARTITION OF orders_2026_08
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_2026_08_h1 PARTITION OF orders_2026_08
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_2026_08_h2 PARTITION OF orders_2026_08
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_2026_08_h3 PARTITION OF orders_2026_08
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 自动创建未来分区（pg_partman扩展）
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_control := 'created_at',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 3
);

-- 配置自动维护任务
SELECT partman.schedule_maintenance();

-- DETACH旧分区（归档冷数据）
ALTER TABLE orders DETACH PARTITION orders_2025_01 CONCURRENTLY;