PostgreSQL中Join与子查询优化及特定日期JSON数据查询

根据你的需求，我整理了两种场景的查询语句——一种是对销售数据做汇总（适合报表需求），另一种是直接关联单条交易记录，你可以根据实际情况选择：

场景1：销售数据按产品+日期汇总（推荐用于报表） #

SELECT json_agg(agg_result.*)
FROM (
    SELECT
        pp.product_name,
        pp.k AS product_price, -- 从product_price_log取产品价格
        pt.total_sales AS sales_amount, -- 从product_transaction汇总销售金额
        pp.sale_date
    FROM sts.product_price_log pp
    -- 先过滤日期+预聚合交易数据，减少后续关联的数据量
    JOIN (
        SELECT
            product_name,
            sale_date,
            SUM(price) AS total_sales -- 可替换为COUNT(price)统计交易笔数，按需调整
        FROM sts.product_transaction
        WHERE sale_date IN ('2018-01-01', '2018-01-15')
        GROUP BY product_name, sale_date
    ) pt 
        ON pp.product_name = pt.product_name 
        AND pp.sale_date = pt.sale_date
    WHERE pp.sale_date IN ('2018-01-01', '2018-01-15')
    -- 可选：按日期+产品排序，让JSON结果更规整
    ORDER BY pp.sale_date, pp.product_name
) agg_result;

场景2：直接关联单条交易记录（无需汇总） #

如果你的product_transaction表中每条记录对应唯一的产品+日期组合，可以用这个简化版：

SELECT json_agg(agg_result.*)
FROM (
    SELECT
        pp.product_name,
        pp.k AS product_price,
        pt.price AS transaction_price,
        pp.sale_date
    FROM sts.product_price_log pp
    JOIN sts.product_transaction pt
        ON pp.product_name = pt.product_name 
        AND pp.sale_date = pt.sale_date
    WHERE pp.sale_date IN ('2018-01-01', '2018-01-15')
) agg_result;

下面都是我日常调优常用的方法，亲测有效：

1. 先筛选再关联，减少中间数据 #

永远不要先关联两张大表再筛选条件！比如上面的查询里，我先在交易表的子查询里过滤了目标日期，还做了预聚合，这样Join的时候只处理需要的数据，速度会快很多。

2. 给关联/筛选字段加合适的索引 #

针对你的表结构，一定要建这几个复合索引：

-- 给product_price_log建日期+产品的复合索引
CREATE INDEX idx_ppl_sale_date_product ON sts.product_price_log(sale_date, product_name);

-- 给product_transaction建日期+产品的复合索引，如果需要聚合，再加覆盖索引
CREATE INDEX idx_pt_sale_date_product ON sts.product_transaction(sale_date, product_name);
-- 覆盖索引：直接从索引取聚合所需数据，不用回表查原数据
CREATE INDEX idx_pt_sale_date_product_price ON sts.product_transaction(sale_date, product_name) INCLUDE (price);

3. 用CTE替代多层嵌套子查询（PostgreSQL 12+友好） #

复杂子查询嵌套会让代码难读，用CTE（公共表表达式）拆分逻辑，而且PostgreSQL 12+对CTE的优化很好，不会像旧版本那样强制物化：

WITH filtered_transactions AS (
    SELECT
        product_name,
        sale_date,
        SUM(price) AS total_sales
    FROM sts.product_transaction
    WHERE sale_date IN ('2018-01-01', '2018-01-15')
    GROUP BY product_name, sale_date
)
SELECT json_agg(agg_result.*)
FROM (
    SELECT
        pp.product_name,
        pp.k AS product_price,
        ft.total_sales AS sales_amount,
        pp.sale_date
    FROM sts.product_price_log pp
    JOIN filtered_transactions ft
        ON pp.product_name = ft.product_name 
        AND pp.sale_date = ft.sale_date
    WHERE pp.sale_date IN ('2018-01-01', '2018-01-15')
) agg_result;

4. 别在Join条件里用函数，会导致索引失效 #

比如不要写ON UPPER(pp.product_name) = UPPER(pt.product_name)，这种写法会让索引直接失效。如果需要忽略大小写，要么插入数据时统一格式，要么建函数索引：

CREATE INDEX idx_ppl_product_upper ON sts.product_price_log(UPPER(product_name));

5. 用EXPLAIN ANALYZE看执行计划找瓶颈 #

这是调优的核心！执行EXPLAIN ANALYZE + 你的查询，看有没有全表扫描（Seq Scan）、有没有用到索引、Join类型是不是合理。比如看到Seq Scan on sts.product_transaction，说明索引没生效，得检查索引或者筛选条件。

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