PostgreSQL：大数据量下产品当前与历史价格的查询优化

现有products表与product_prices_history表，需求为每个产品生成单行记录，包含当前价格、当前价格创建时间，以及存在且与当前价格不同的上一次历史价格及其创建时间。当前采用LATERAL JOIN的查询语句在100万产品、4000万价格历史记录的场景下运行极慢，寻求替代解决方案。

SELECT 
    products.id, 
    price.price as price, 
    price.created_at as price_created_at,
    prev_price.price as prev_price,
    prev_price.created_at as prev_price_created_at 
FROM products
LEFT JOIN LATERAL (
    SELECT price, created_at
    FROM product_prices_history
    WHERE product_prices_history.product_id = products.id
    ORDER BY created_at DESC
    LIMIT 1
) price ON true
LEFT JOIN LATERAL (
    SELECT price, created_at
    FROM product_prices_history as prev_price 
    WHERE prev_price.product_id = products.id AND prev_price.price <> price.price
    ORDER BY created_at DESC
    LIMIT 1
) prev_price ON true;

products表 #

product_prices_history表 #

方案一：窗口函数批量筛选记录 #

通过窗口函数为每个产品的价格记录排序并标记价格变化节点，一次性提取当前价格和上一次不同的价格，避免多次LATERAL JOIN的嵌套查询：

WITH price_with_markers AS (
    SELECT
        product_id,
        price,
        created_at,
        -- 按创建时间倒序排名，取第一条为当前价格
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at DESC) AS rn,
        -- 标记与上一条（更早的）价格是否不同
        CASE WHEN price != LAG(price) OVER (PARTITION BY product_id ORDER BY created_at DESC)
             THEN 1 ELSE 0 END AS is_diff_from_current
    FROM product_prices_history
),
current_prices AS (
    SELECT product_id, price, created_at FROM price_with_markers WHERE rn = 1
),
prev_prices AS (
    SELECT
        product_id,
        price,
        created_at
    FROM (
        SELECT
            product_id,
            price,
            created_at,
            ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at DESC) AS diff_rn
        FROM price_with_markers
        WHERE rn > 1 AND is_diff_from_current = 1
    ) t
    WHERE diff_rn = 1
)
SELECT
    p.id,
    cp.price,
    cp.created_at AS price_created_at,
    pp.price AS prev_price,
    pp.created_at AS prev_price_created_at
FROM products p
LEFT JOIN current_prices cp ON p.id = cp.product_id
LEFT JOIN prev_prices pp ON p.id = pp.product_id;

方案二：先去重连续相同价格再关联 #

如果历史表中有大量连续相同的价格记录，先去重仅保留价格变化的节点，大幅减少后续处理的数据量：

WITH price_changes_only AS (
    SELECT
        product_id,
        price,
        created_at
    FROM (
        SELECT
            product_id,
            price,
            created_at,
            -- 只保留价格发生变化的记录（含第一条）
            CASE WHEN price = LAG(price) OVER (PARTITION BY product_id ORDER BY created_at)
                 THEN 0 ELSE 1 END AS keep_record
        FROM product_prices_history
    ) t
    WHERE keep_record = 1
),
ranked_changes AS (
    SELECT
        product_id,
        price,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at DESC) AS rn
    FROM price_changes_only
)
SELECT
    p.id,
    rc1.price AS price,
    rc1.created_at AS price_created_at,
    rc2.price AS prev_price,
    rc2.created_at AS prev_price_created_at
FROM products p
LEFT JOIN ranked_changes rc1 ON p.id = rc1.product_id AND rc1.rn = 1
LEFT JOIN ranked_changes rc2 ON p.id = rc2.product_id AND rc2.rn = 2;

关键索引优化 #

无论采用哪种方案，必须创建复合索引提升查询效率：

CREATE INDEX idx_product_price_history ON product_prices_history (product_id, created_at DESC, price);

该索引可让窗口函数的排序、筛选直接走索引，避免全表扫描。

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