PostgreSQL多JOIN查询运行缓慢，如何优化获取各地点最高运费数据

你的现有查询逻辑正确，性能可以通过优化大幅提升，核心优化方向有两个：减少冗余的表关联、添加匹配查询逻辑的覆盖索引。

现有查询的冗余问题 #

你当前关联了location表，但最终输出只用到了location.id字段，而该字段已经存在于product_location_shipping表中，完全可以去掉这一次不必要的JOIN操作。

优化后的查询语句 #

SELECT DISTINCT ON (pl_s.location_id, sd.shipping_method_id)
    pl_s.location_id,
    sd.shipping_method_id,
    sm.name as shipping_method_name,
    sd.price
FROM product_location_shipping AS pl_s 
JOIN product_location_shipping_details AS pl_sd
    ON pl_s.id = pl_sd.product_location_shipping_id
JOIN shipping_details AS sd
    ON sd.id = pl_sd.shipping_details_id
JOIN shipping_method AS sm
    ON sm.id = sd.shipping_method_id
WHERE pl_s.product_id = 1
ORDER BY pl_s.location_id, sd.shipping_method_id, sd.price DESC;

性能翻倍的索引优化方案 #

添加以下覆盖索引，避免查询过程中的回表查询和文件排序操作：

• product_location_shipping表加联合索引：CREATE INDEX idx_pls_product_location ON product_location_shipping(product_id, location_id, id);
• product_location_shipping_details表加联合索引：CREATE INDEX idx_plsd_plid_sdid ON product_location_shipping_details(product_location_shipping_id, shipping_details_id);
• shipping_details表加联合索引：CREATE INDEX idx_sd_id_method_price ON shipping_details(id, shipping_method_id, price DESC);
• shipping_method表加联合索引：CREATE INDEX idx_sm_id_name ON shipping_method(id, name);

可选的窗口函数实现 #

如果你的数据量特别大，也可以测试窗口函数的写法，和DISTINCT ON的性能差异根据数据分布决定：

WITH ranked_shipping AS (
    SELECT 
        pl_s.location_id,
        sd.shipping_method_id,
        sm.name as shipping_method_name,
        sd.price,
        ROW_NUMBER() OVER (PARTITION BY pl_s.location_id, sd.shipping_method_id ORDER BY sd.price DESC) as rn
    FROM product_location_shipping AS pl_s 
    JOIN product_location_shipping_details AS pl_sd
        ON pl_s.id = pl_sd.product_location_shipping_id
    JOIN shipping_details AS sd
        ON sd.id = pl_sd.shipping_details_id
    JOIN shipping_method AS sm
        ON sm.id = sd.shipping_method_id
    WHERE pl_s.product_id = 1
)
SELECT location_id, shipping_method_id, shipping_method_name, price
FROM ranked_shipping
WHERE rn = 1;

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