postgresql 在where语句中使用CTE非常慢

d4so4syb 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(229)

我在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)

它在用数组id ex替换ANY(select status_id from stuckable_statuses)时运行得很慢。(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

比较相同的SQL与数组，我可以看到它没有使用索引的jobs。status可能是因为jobs表真的很大（~ 15 kk行），而status_describes是~200行。
如果可能的话，你能帮我优化一下这个SQL吗？
谢谢你！
更新：
下面是使用硬编码数组的查询：

SELECT jobs.* FROM transcription_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

来源：https://stackoverflow.com/questions/76563792/using-cte-in-where-statement-is-very-slow

2条答案

按热度按时间

vlurs2pr1#

主要问题是它认为它将找到1563473行，但实际上找到303行。如果实际上找到了1563473行，那么seq扫描上的散列连接可能真的比ANY驱动的索引扫描快。
不幸的是，对于当前的数据模型和现有的PostgreSQL版本，您可能无法对此做任何事情。看起来，在作业表中，可卡住的状态比不可卡住的状态要少得多，但计划员无法知道这一点。
要强制执行更快的计划，可以在运行此查询之前临时关闭enable_hashjoin或enable_seqscan。这绝对是一个丑陋的解决方案，但它应该是一个可靠的解决方案。如果关闭并行查询（设置max_parallel_workers_per_gather=0），那么 * 可能 * 足以将计划切换到更快的计划。如果您没有从并行查询中获得太多好处，那么这将是一个不那么丑陋的解决方案，但也不太可靠。或者，您可以尝试添加plan hints。
最健壮的解决方案可能只是将其作为两个查询运行，在一个中获取status_id的数组/列表，然后将该数组/列表填充到第二个中。这样，计划者就可以真正看到将要使用的值，并可以相应地进行计划。（我注意到你的硬编码计划仍然被严重错误估计，但不像另一个那样严重，也不足以推动计划选择。这可能是一个单独的问题，与你目前面临的问题无关。）
对于您的数据模型，我凭直觉认为，给定状态的可坚持性不会经常改变，如果有的话。如果您可以将该值直接记录为jobs表中的一个新列，并在状态本身发生变化时对其进行更改，而不需要间接指向单独的表，那么几乎可以立即解决这个问题。

赞(0）回复(0）举报 2023-06-29

rsl1atfo2#

PostgreSQL在执行subquery或CTE和ANY()时不使用索引。
可以使用inner join：

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

确保你有status_descriptions.status_id和jobs.status的索引。索引可以大大提高连接操作的性能。

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql 在where语句中使用CTE非常慢

2条答案

相关问题

热门标签

最新问答