PostgreSQL查询优化：如何让聚合子句先过滤再执行？

原查询耗时27秒的核心原因是CTE vimages 会先对整个view_images表执行JOIN和聚合操作，再与xxs表关联过滤，即使最终只返回1行结果，也做了大量无意义的全表计算。而硬编码ig.id时，PostgreSQL能自动将过滤条件下推，仅处理view_images中匹配目标ID的行，因此性能大幅提升。

改写后的查询方案 #

以下几种写法都能实现条件下推，让view_images先过滤再聚合，达到与硬编码ID一致的执行效率：

方案1：直接关联过滤（最简洁）

适用于不需要保留xxs表无对应view_images数据的场景（若要保留LEFT JOIN逻辑，可调整为LEFT JOIN）：

explain (analyze, costs, verbose, buffers) 
SELECT
    vim.view_id,
    array_to_json(array_agg(row_to_json( row(            
        vim.created_at,
        igim.id, 
        igim.name, 
        vim.sequence_no
    )) order by vim.sequence_no desc)) as json
FROM xxs ig
JOIN view_images vim ON vim.view_id = ig.id
JOIN xx_images igim ON vim.xx_image_id = igim.id
WHERE ig.alias = '257_belmont_cir_brunswick_ga'
GROUP BY vim.view_id

方案2：先获取目标ID再关联（保留原LEFT JOIN逻辑）

先从xxs表拿到目标ID，再用这个ID限制view_images的查询范围，完全匹配原查询的LEFT JOIN语义：

explain (analyze, costs, verbose, buffers) 
WITH target_ig AS (
    SELECT id FROM xxs WHERE alias = '257_belmont_cir_brunswick_ga'
)
SELECT
    vimages.view_id,
    vimages.json
FROM target_ig ig
LEFT JOIN (
    SELECT
        vim.view_id,
        array_to_json(array_agg(row_to_json( row(            
            vim.created_at,
            igim.id, 
            igim.name, 
            vim.sequence_no
        )) order by vim.sequence_no desc)) as json
    FROM view_images vim
    JOIN xx_images igim ON vim.xx_image_id = igim.id
    WHERE vim.view_id IN (SELECT id FROM target_ig)
    GROUP BY vim.view_id
) vimages ON vimages.view_id = ig.id

额外性能优化建议 #

为了确保查询始终高效，建议添加以下索引：

• 给xxs.alias创建唯一索引（每个alias对应唯一行，加速目标ID查询）：

  CREATE UNIQUE INDEX idx_xxs_alias ON xxs(alias);
  

• 给view_images.view_id创建索引，加速ID匹配过滤：

  CREATE INDEX idx_view_images_view_id ON view_images(view_id);
  

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