PostgreSQL中CTE在WHERE子句中查询过慢的优化求助

问题描述 #

在PostgreSQL v13.10中执行以下SQL时速度极慢：

WITH stuckable_statuses AS (
  SELECT status_id FROM status_descriptions
  WHERE (tags @> ARRAY['stuckable']::varchar[])
)

SELECT jobs.* FROM jobs
WHERE jobs.status = ANY(select status_id from stuckable_statuses)

但将ANY(select status_id from stuckable_statuses)替换为硬编码ID数组（如(1,2,3)）后，查询速度变得非常快。

原慢查询执行计划 #

Gather  (cost=1005.64..5579003.45 rows=1563473 width=2518) (actual time=45.495..40138.515 rows=303 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Hash Semi Join  (cost=5.64..5421656.15 rows=651447 width=2518) (actual time=44.533..40126.793 rows=101 loops=3)
        Hash Cond: (jobs.status = status_descriptions.status_id)
        ->  Parallel Seq Scan on jobs  (cost=0.00..5378777.15 rows=13571815 width=2518) (actual time=0.892..38662.091 rows=10537079 loops=3)
        ->  Hash  (cost=5.56..5.56 rows=6 width=4) (actual time=0.377..0.378 rows=11 loops=3)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Seq Scan on status_descriptions  (cost=0.00..5.56 rows=6 width=4) (actual time=0.310..0.370 rows=11 loops=3)
                    Filter: (tags @> '{stuckable}'::character varying[])
                    Rows Removed by Filter: 146
Planning Time: 0.711 ms
Execution Time: 40138.654 ms

表结构（取自Rails的schema.rb） #

create_table "jobs", id: :serial, force: :cascade do |t|
  t.string "filename"
  t.string "sandbox"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.integer "status", default: 0, null: false
  t.integer "provider_id"
  t.integer "lang_id"
  t.integer "profile_id"
  t.datetime "extra_date"
  t.datetime "main_date"
  t.datetime "performer_id"
  t.index ["provider_id", "status", "extra_date"], name: "jobs_on_media_provider_id__status__extra_date"
  t.index ["provider_id", "status", "main_date"], name: "jobs_on_media_provider_id_and_status_and_due_date"
  t.index ["profile_id", "status", "extra_date"], name: "index_jobs_on_profile_id__status__extra_date"
  t.index ["profile_id", "status", "main_date"], name: "index_transcription_jobs_on_profile_id_and_status_and_due_date"
  t.index ["status", "sandbox", "lang_id", "extra_date"], name: "index_jobs_on_status__sandbox__lang_id__extra_date"
  t.index ["status", "sandbox", "lang_id", "main_date"], name: "index_jobs_on_status_and_sandbox_and_lang_id_and_due_date"
  t.index ["performer_id", "status", "extra_date"], name: "index_jobs_on_performer_id__status__extra_date"
  t.index ["performer_id", "status", "main_date"], name: "index_jobs_on_performer_id_and_status_and_due_date"
end

create_table "status_descriptions", id: :serial, force: :cascade do |t|
  t.integer "status_id"
  t.string "title"
  t.string "tags", array: true
  t.index ["status_id"], name: "index_status_descriptions_on_status_id"
end

硬编码数组的查询及执行计划 #

查询语句 #

SELECT jobs.* FROM jobs
WHERE jobs.status IN (2, 3, 4, 291, 290, 46, 142, 260, 6, 7, 270)

执行计划 #

Index Scan using index_jobs_on_status__sandbox__lang_id__current_stage_due_date on jobs  (cost=0.56..98661.05 rows=26541 width=2518) (actual time=0.032..63.266 rows=483 loops=1)
  Index Cond: (status = ANY ('{2,3,4,291,290,46,142,260,6,7,270}'::integer[]))
Planning Time: 0.356 ms
Execution Time: 63.337 ms

问题分析 #

原查询执行慢的核心原因是PostgreSQL优化器未选择jobs表上的status相关索引，反而对1500万行的jobs表执行全表扫描。这是因为优化器无法准确预估子查询select status_id from stuckable_statuses的返回行数（执行计划预估6行，实际返回11行），误判哈希半连接比索引扫描更高效，实际情况恰好相反。

优化方案 #

方案1：将子查询转为数组形式 #

通过ARRAY()函数将子查询结果转为数组，让优化器识别出这是固定ID集合，从而触发索引扫描：

SELECT jobs.* FROM jobs
WHERE jobs.status = ANY(
  ARRAY(SELECT status_id FROM status_descriptions WHERE tags @> ARRAY['stuckable']::varchar[])
)

方案2：使用IN子查询替代ANY #

PostgreSQL对IN子查询的优化逻辑更适配小结果集场景：

SELECT jobs.* FROM jobs
WHERE jobs.status IN (
  SELECT status_id FROM status_descriptions WHERE tags @> ARRAY['stuckable']::varchar[]
)

方案3：强制物化CTE #

如果status_descriptions表数据量小且不频繁变动，强制物化CTE让优化器先计算出所有stuckable状态ID，再执行主查询：

WITH stuckable_statuses AS MATERIALIZED (
  SELECT status_id FROM status_descriptions
  WHERE tags @> ARRAY['stuckable']::varchar[]
)
SELECT jobs.* FROM jobs
WHERE jobs.status = ANY(SELECT status_id FROM stuckable_statuses)

方案4：创建物化视图（长期优化） #

若此类查询频繁执行，可创建专门存储stuckable状态ID的物化视图，定期刷新，让优化器更容易选择最优计划：

-- 创建物化视图
CREATE MATERIALIZED VIEW stuckable_status_ids AS
SELECT status_id FROM status_descriptions WHERE tags @> ARRAY['stuckable']::varchar[];

-- 创建索引
CREATE UNIQUE INDEX idx_stuckable_status_ids ON stuckable_status_ids(status_id);

-- 查询使用
SELECT jobs.* FROM jobs
WHERE jobs.status IN (SELECT status_id FROM stuckable_status_ids);

-- 定期刷新（按需执行）
REFRESH MATERIALIZED VIEW stuckable_status_ids;

效果验证 #

以上方案均可让优化器选择jobs表上的status相关索引，执行时间会接近硬编码数组的查询，大幅提升效率。

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