PostgreSQL物化视图与并行查询执行计划优化实战

PostgreSQL的物化视图（Materialized View）将查询结果物理存储为表，适合报表统计、OLAP分析和读多写少场景。配合并行查询（Parallel Query），复杂聚合查询的执行效率可提升数倍。数据库运维中，物化视图刷新策略和并行度调优是性能优化的关键环节。本文给出完整的配置方案和执行计划分析。

PostgreSQL物化视图创建与刷新机制

普通视图（View）仅存储查询定义，每次查询时重新执行SQL。物化视图将查询结果物化为物理表，支持索引加速，但数据不会自动同步更新。创建语法：

-- 创建物化视图: 每日销售汇总
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    o.shop_id,
    s.shop_name,
    DATE(o.order_time) AS sale_date,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS total_amount,
    AVG(o.total_amount) AS avg_amount,
    COUNT(DISTINCT o.user_id) AS unique_users
FROM orders o
JOIN shops s ON o.shop_id = s.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.shop_id, s.shop_name, DATE(o.order_time)
WITH DATA;

-- 创建索引加速物化视图查询
CREATE UNIQUE INDEX idx_mv_daily_sales_date_shop
    ON mv_daily_sales (sale_date, shop_id);

CREATE INDEX idx_mv_daily_sales_shop_amount
    ON mv_daily_sales (shop_id, total_amount DESC);

-- 查看物化视图大小和最后刷新时间
SELECT
    relname AS view_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS size,
    reltuples::bigint AS row_count
FROM pg_catalog.pg_class
WHERE relkind = 'm'
ORDER BY pg_total_relation_size(relid) DESC;

物化视图刷新分两种模式：

-- 完全刷新: 锁定物化视图, 重新执行全部查询
-- 期间无法读取数据(非CONCURRENTLY模式)
REFRESH MATERIALIZED VIEW mv_daily_sales;

-- 并发刷新: 需要唯一索引, 刷新期间可读取
-- 内部使用2PC创建临时表+重命名, 不阻塞读操作
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

-- 查看物化视图是否可并发刷新(需要唯一索引)
SELECT relname, relispopulated
FROM pg_class
WHERE relkind = 'm' AND relname = 'mv_daily_sales';

增量刷新策略与触发器同步方案

完全刷新对大数据量物化视图耗时较长。以下通过触发器+增量表实现仅刷新变更数据：

-- 创建增量变更记录表
CREATE TABLE mv_daily_sales_delta (
    shop_id INT,
    sale_date DATE,
    operation CHAR(1),  -- I(insert) / U(update) / D(delete)
    old_order_id BIGINT,
    new_total_amount NUMERIC(12,2),
    created_at TIMESTAMP DEFAULT now()
);

-- 在源表上创建触发器记录变更
CREATE OR REPLACE FUNCTION track_order_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO mv_daily_sales_delta
            (shop_id, sale_date, operation, new_total_amount)
        VALUES (NEW.shop_id, DATE(NEW.order_time),
                'I', NEW.total_amount);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO mv_daily_sales_delta
            (shop_id, sale_date, operation,
             old_order_id, new_total_amount)
        VALUES (NEW.shop_id, DATE(NEW.order_time),
                'U', OLD.id, NEW.total_amount);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO mv_daily_sales_delta
            (shop_id, sale_date, operation, old_order_id)
        VALUES (OLD.shop_id, DATE(OLD.order_time),
                'D', OLD.id);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_changes
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION track_order_changes();

-- 增量刷新存储过程
CREATE OR REPLACE PROCEDURE refresh_mv_incremental()
LANGUAGE plpgsql AS $$
BEGIN
    -- 1. 删除受影响日期的旧数据
    DELETE FROM mv_daily_sales
    WHERE (shop_id, sale_date) IN (
        SELECT DISTINCT shop_id, sale_date
        FROM mv_daily_sales_delta
    );

    -- 2. 重新计算受影响日期的聚合数据
    INSERT INTO mv_daily_sales
    SELECT
        o.shop_id, s.shop_name,
        DATE(o.order_time) AS sale_date,
        COUNT(*) AS order_count,
        SUM(o.total_amount) AS total_amount,
        AVG(o.total_amount) AS avg_amount,
        COUNT(DISTINCT o.user_id) AS unique_users
    FROM orders o
    JOIN shops s ON o.shop_id = s.id
    WHERE (o.shop_id, DATE(o.order_time)) IN (
        SELECT DISTINCT shop_id, sale_date
        FROM mv_daily_sales_delta
    )
    GROUP BY o.shop_id, s.shop_name, DATE(o.order_time);

    -- 3. 清空增量表
    TRUNCATE TABLE mv_daily_sales_delta;

    COMMIT;
END;
$$;

并行查询配置与执行计划分析

PostgreSQL 9.6+支持并行查询，包括并行顺序扫描（Parallel Seq Scan）、并行聚合（Parallel Aggregate）和并行哈希连接（Parallel Hash Join）。核心参数：

-- 查看并行查询相关参数
SHOW max_parallel_workers;              -- 最大并行工作进程数
SHOW max_parallel_workers_per_gather;   -- 每个Gather节点最大并行度
SHOW min_parallel_table_scan_size;      -- 触发并行扫描的最小表大小
SHOW parallel_setup_cost;               -- 并行启动成本
SHOW parallel_tuple_cost;              -- 每个元组的并行成本

-- 调优建议配置
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET min_parallel_table_scan_size = '8MB';
ALTER SYSTEM SET parallel_setup_cost = 100;
ALTER SYSTEM SET parallel_tuple_cost = 0.03;
ALTER SYSTEM SET min_parallel_index_scan_size = '512kB';
SELECT pg_reload_conf();

-- 强制指定并行度(测试用)
SET max_parallel_workers_per_gather = 4;
SET enforce_parallel_mode = on;

使用EXPLAIN ANALYZE分析执行计划，关注并行扫描是否生效：

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT shop_id, SUM(total_amount) AS revenue
FROM orders
WHERE order_time >= '2026-01-01'
GROUP BY shop_id
ORDER BY revenue DESC;

-- 期望输出(并行扫描生效):
-- Finalize GroupAggregate
--   Group Key: orders.shop_id
--   ->  Sort
--         Sort Key: (sum(orders.total_amount)) DESC
--         ->  Gather
--               Workers Planned: 4
--               Workers Launched: 4     -- 实际启动了4个工作进程
--               ->  Partial HashAggregate
--                     Group Key: orders.shop_id
--                     ->  Parallel Index Scan
--                           on orders_order_time_idx
--                           Index Cond: (order_time >= '2026-01-01')

-- 如果Workers Launched为0, 说明并行未生效
-- 常见原因:
-- 1. 表太小, 未达min_parallel_table_scan_size阈值
-- 2. 查询包含CURSOR/LIMIT等阻塞并行的操作
-- 3. parallel_setup_cost设置过高
-- 4. 工作进程数已达max_parallel_workers上限

并行哈希join与分区表并行查询优化

大表关联查询是并行查询的重点应用场景：

-- 并行哈希连接: 大表JOIN大表
SET enable_parallel_hash = on;
SET min_parallel_table_scan_size = 0;  -- 测试用, 强制小表也并行

EXPLAIN (ANALYZE)
SELECT o.shop_id, s.region, COUNT(*) AS cnt
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN shops s ON o.shop_id = s.id
WHERE o.order_time >= '2026-06-01'
GROUP BY o.shop_id, s.region;

-- 分区表并行查询: 每个分区分配一个工作进程
CREATE TABLE orders_partitioned (
    id BIGSERIAL,
    shop_id INT,
    total_amount NUMERIC(12,2),
    order_time TIMESTAMP
) PARTITION BY RANGE (order_time);

-- 按月分区
CREATE TABLE orders_202601 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_202602 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- ...更多分区

-- 分区裁剪+并行扫描
SET enable_partition_pruning = on;
SET max_parallel_workers_per_gather = 8;

EXPLAIN (ANALYZE)
SELECT shop_id, SUM(total_amount)
FROM orders_partitioned
WHERE order_time BETWEEN '2026-01-01' AND '2026-06-30'
GROUP BY shop_id;
-- 每个月分区分配一个工作进程, 8个月分区用满8个并行度

数据备份恢复场景下，pg_dump可利用并行jobs加速导出：pg_dump -j 4 -Fd -f /backup/db db_name。分库分表方案中，分区表配合并行查询可替代部分Citus/中间件分片需求，降低架构复杂度。对于SQL查询优化，核心原则是确保过滤条件命中索引、聚合查询命中物化视图、大表扫描命中并行计划，三个层次配合可将查询耗时从分钟级降至秒级。