Postgres 慢存在子查詢

我有一個隊列系統，它有兩個表，jobs 和 jobs_queues。jobs 是我們讓作業執行的地方，而我們用隊列名稱填充 job_queues，這樣當我們選擇作業時，我們可以丟棄那些已經在執行的作業，因此隊列按順序執行。

我的問題在於檢查 que job_queues 表中鎖定作業的子查詢。查詢如下所示：

explain analyze
select jobs.queue_name, jobs.id
from "graphile_worker".jobs
where (jobs.locked_at is null or jobs.locked_at < (now() - '4 hours'::interval))
and (
 jobs.queue_name is null
or
 exists (
   select 1
   from "graphile_worker".job_queues
   where job_queues.queue_name = jobs.queue_name
   and (job_queues.locked_at is null or job_queues.locked_at < (now() - '4 hours'::interval))
   for update
   skip locked
 )
)
and run_at <= now()
and attempts < max_attempts
and (null is null or jobs.task_identifier = any(null))
and (null is null or (flags ?| null) is not true)
order by priority asc, run_at asc, id asc
limit 1
for update
skip locked;

表的 DDL 可以在此處查看：https ://github.com/graphile/worker/blob/main/sql/000001.sql （來自庫）

我已經在 job_queues 中填充了具有 100 個隊列名稱的表，每個表在作業表中有 10000 個作業，查詢的查詢計劃如下所示。

Limit  (cost=0.43..17.33 rows=1 width=33) (actual time=149.335..149.336 rows=1 loops=1)
 ->  LockRows  (cost=0.43..2817155.90 rows=166667 width=33) (actual time=149.335..149.335 rows=1 loops=1)
       ->  Index Scan using jobs_priority_run_at_id_locked_at_without_failures_idx on jobs  (cost=0.43..2815489.23 rows=166667 width=33) (actual time=149.332..149.332 rows=1 loops=1)
             Index Cond: (run_at <= now())
             Filter: ((attempts < max_attempts) AND ((locked_at IS NULL) OR (locked_at < (now() - '04:00:00'::interval))) AND ((queue_name IS NULL) OR (SubPlan 1)))
             Rows Removed by Filter: 80000
             SubPlan 1
               ->  LockRows  (cost=4.28..8.31 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=79993)
                     ->  Bitmap Heap Scan on job_queues  (cost=4.28..8.30 rows=1 width=10) (actual time=0.001..0.001 rows=0 loops=79993)
                           Recheck Cond: (queue_name = jobs.queue_name)
                           Filter: ((locked_at IS NULL) OR (locked_at < (now() - '04:00:00'::interval)))
                           Rows Removed by Filter: 1
                           Heap Blocks: exact=79993
                           ->  Bitmap Index Scan on job_queues_pkey  (cost=0.00..4.28 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=79993)
                                 Index Cond: (queue_name = jobs.queue_name)
Planning time: 0.313 ms
Execution time: 149.391 ms

如果我要在子查詢的 where 部分中刪除locked_at，它會變得更快：

Limit  (cost=0.43..17.32 rows=1 width=33) (actual time=0.062..0.063 rows=1 loops=1)
 ->  LockRows  (cost=0.43..2814655.91 rows=166667 width=33) (actual time=0.061..0.062 rows=1 loops=1)
       ->  Index Scan using jobs_priority_run_at_id_locked_at_without_failures_idx on jobs  (cost=0.43..2812989.24 rows=166667 width=33) (actual time=0.056..0.057 rows=1 loops=1)
             Index Cond: (run_at <= now())
             Filter: ((attempts < max_attempts) AND ((locked_at IS NULL) OR (locked_at < (now() - '04:00:00'::interval))) AND ((queue_name IS NULL) OR (SubPlan 1)))
             Rows Removed by Filter: 1
             SubPlan 1
               ->  LockRows  (cost=4.28..8.30 rows=1 width=10) (actual time=0.032..0.032 rows=1 loops=1)
                     ->  Bitmap Heap Scan on job_queues  (cost=4.28..8.29 rows=1 width=10) (actual time=0.021..0.021 rows=1 loops=1)
                           Recheck Cond: (queue_name = jobs.queue_name)
                           Heap Blocks: exact=1
                           ->  Bitmap Index Scan on job_queues_pkey  (cost=0.00..4.28 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)
                                 Index Cond: (queue_name = jobs.queue_name)
Planning time: 0.232 ms
Execution time: 0.142 ms

我看到的一件奇怪的事情是，對於我在 job_queues 表中鎖定的每個隊列，查詢變得更重，這裡有一些具有不同數量的鎖定隊列的查詢工廠（通過鎖定我的意思是在 job_queues 和 job 中設置locked_at 和locked_by 列表）：

0 個工作：https ://explain.depesz.com/s/vM9g 1 個工作：https ://explain.depesz.com/s/a0Lv 2 個工作：https ://explain.depesz.com/s/fJ7I 3 個工作: https://explain.depesz.com/s/ouW9 8 個工作: https://explain.depesz.com/s/s9Yb

然後我的問題是為什麼它這麼慢，為什麼我設置locked_at的行越多它就越慢，我該如何優化它。

這很容易解釋。您正在尋找屬於未鎖定隊列的第一份工作（也符合其他一些標準，但顯然這些很容易滿足，以及不符合其他一些標準，但顯然也很容易不滿足）。隨著越來越多的隊列被鎖定，屬於未鎖定隊列的工作變得越來越難找到，並且需要越來越多的工作才能找到它們。

如果你不關心隊列是否被鎖定，那麼很容易找到工作。

至於怎麼優化，好吧，別再做了。如果您的所有隊列都已滿，請停止嘗試使它們更滿。努力讓它們變得更空。

在更複雜的謂詞中使用EXISTSwithOR嵌套幾乎肯定會強制它為其他考慮的*每一行執行一次該查詢。*也SomeField IS NULL OR SomeField < value將導致掃描，因此如果其他謂詞沒有足夠的選擇性¹（或沒有相關索引）來限制認為您可能正在為這 10,000 個作業中的大多數執行子選擇的行。

我可能會嘗試這樣的查詢：

1. 用 中的謂詞將重構EXISTS為 a以丟棄不匹配的行。如果您正在獲取一個列表，並且在某些情況下，外部表中的一行可能有多個匹配項，您可能需要使用或刪除重複結果，儘管您只關心第一個結果，這不會物質³。LEFT OUTER JOIN``WHERE``GROUP BY``DISTINCT
2. 將其分成兩個 SELECT 一個 withWHERE locked_at IS NULL和一個 withWHERE locked_at < (now() - '4 hours'::interval)並將它們與 組合起來UNION，在每個新的 select 中複製其餘的查詢，或者作為一個 CTE，然後您可以使用它來進一步過濾²。這通常可以通過將掃描轉換為兩個或多個搜尋來改進此類查詢。*注意：這取決於 上是否有可用索引locked_at，否則您會將一次掃描變成兩次。*它還依賴於不匹配大多數行的檢查。
3. 以上兩種。

如果您可以更改架構，另一種選擇可能是擁有一個可直接索引的列，該列表明隊列可以根據該列進行搜尋，而不是派生此屬性。不過，這可能會增加太多額外的邏輯來維持該價值，因此您必鬚根據此類查詢中的節省來評估這一點。

---

$$ 1 $$ run_at <= now()並且attempts < max_attempts不太可能非常有選擇性

$$ 2 $$您可能會從這兩個選項中獲得完全不同的性能，並且它會根據您的數據庫版本而進一步變化，所以兩者都是。我更喜歡 CTE 選項，因為它會更清晰，更容易維護，如果它表現得一樣好或更好（或至少不會明顯更差）

$$ 3 $$它可能與其他人一樣被重複，但您永遠不會在意，因為您只考慮第一行之外的任何內容，因此您不會因為重複的結果而加倍採取進一步的行動

引用自：https://dba.stackexchange.com/questions/305364