我正在嘗試加快洗掉查詢的速度，與相同的選擇查詢相比，該查詢似乎非常慢：
慢刪查詢：
https ://explain.depesz.com/s/kkWJ
delete from processed.token_utxo
where token_utxo.output_tx_time >= (select '2022-03-01T00:00:00 00:00'::timestamp with time zone)
and token_utxo.output_tx_time < (select '2022-03-02T00:00:00 00:00'::timestamp with time zone)
and not exists (
select 1
from public.ma_tx_out
where ma_tx_out.id = token_utxo.id
)
快速選擇查詢：https ://explain.depesz.com/s/Bp8q
select * from processed.token_utxo
where token_utxo.output_tx_time >= (select '2022-03-01T00:00:00 00:00'::timestamp with time zone)
and token_utxo.output_tx_time < (select '2022-03-02T00:00:00 00:00'::timestamp with time zone)
and not exists (
select 1
from public.ma_tx_out
where ma_tx_out.id = token_utxo.id
)
表參考：
create table processed.token_utxo (
id bigint,
tx_out_id bigint,
token_id bigint,
output_tx_id bigint,
output_tx_index int,
output_tx_time timestamp,
input_tx_id bigint,
input_tx_time timestamp,
address varchar,
address_has_script boolean,
payment_cred bytea,
redeemer_id bigint,
stake_address_id bigint,
quantity numeric,
primary key (id)
);
create index token_utxo_output_tx_id on processed.token_utxo using btree (output_tx_id);
create index token_utxo_input_tx_id on processed.token_utxo using btree (input_tx_id);
create index token_utxo_output_tx_time on processed.token_utxo using btree (output_tx_time);
create index token_utxo_input_tx_time on processed.token_utxo using btree (input_tx_time);
create index token_utxo_address on processed.token_utxo using btree (address);
create index token_utxo_token_id on processed.token_utxo using btree (token_id);
版本：PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
Postgres 選擇了不同的查詢計劃，這導致了截然不同的性能。我對 Postgres 不夠熟悉，無法理解它做出這個決定的原因。希望有一種簡單的方法可以引導它在這里制定更好的計劃。
uj5u.com熱心網友回復：
為什么它提出不同的計劃相對容易解釋。首先，DELETE 不能使用并行查詢，因此被認為對并行更友好的計劃更受 SELECT 而不是 DELETE 的青睞。也許這個限制會在未來的某個版本中放寬。其次，DELETE 不能對 ma_tx_out_pkey 使用 index-only-scan，就像純 SELECT 一樣——它將使用索引掃描。這也將使較快的計劃對于 DELETE 而言似乎不如對于 SELECT 而言快。這兩個因素結合起來顯然足以讓它改變計劃。我們已經看到了第一個因素的證據，您可以通過將 enable_seqscan 設定為 off 并查看 DELETE 選擇的計劃來驗證第二個因素，如果是嵌套回圈，則驗證最后一次索引掃描不是僅索引.
但是當然，這些因素可以使計劃之間的決定不同的唯一原因是因為計劃估計一開始就非常接近，盡管在實際性能上是如此不同。那么是什么解釋了這種虛假的親密關系呢？這很難用我們擁有的資訊來確定（如果你已經完成EXPLAIN (ANALYZE, BUFFERS)了 track_io_timing 打開）。
一種可能是實際表現的差異是虛幻的。也許嵌套回圈如此之快只是因為它需要的所有資料都在記憶體中，唯一的原因是您在測驗程序中使用相同的引數重復執行相同的查詢。如果您更改時間戳引數，或者在運行之間清除 PostgreSQL 緩沖區和檔案快取，它仍然那么快嗎？
另一種可能性是您的系統調整不當。例如，如果您的資料在 SSD 上，那么 random_page_cost 的默認設定可能太高了。1.1 可能是比 4 更合理的設定。
最后，您的 work_mem 設定可能太低了。這導致使用大量批次的散列：8192。這對性能的影響程度很難預測，因為它取決于您的硬體、內核、檔案系統等。（這可能是計劃者不嘗試的原因考慮到它）。它很容易測驗，您可以在本地（在您的會話中）增加 work_mem 的設定，看看它是否會改變速度。
僅基于您的洗掉實際上沒有找到要洗掉的任何行這一事實，才能進行大部分分析。如果它正在洗掉行，那會使情況變得更加復雜。
uj5u.com熱心網友回復：
我不確定是什么觸發了查詢計劃在SELECT和之間的切換DELETE，但我確實知道這一點：回傳常量值的子查詢實際上是沒有幫助的。改用：
SELECT *
FROM processed.token_utxo t
WHERE t.output_tx_time >= '2022-03-01T00:00:00 00:00'::timestamptz -- no subquery
AND t.output_tx_time < '2022-03-02T00:00:00 00:00'::timestamptz -- no subquery
AND NOT EXISTS (SELECT FROM public.ma_tx_out m WHERE m.id = t.id)
DELETE FROM processed.token_utxo t
WHERE t.output_tx_time >= '2022-03-01T00:00:00 00:00'::timestamptz
AND t.output_tx_time < '2022-03-02T00:00:00 00:00'::timestamptz
AND NOT EXISTS (SELECT FROM public.ma_tx_out m WHERE m.id = t.id)
正如您在查詢計劃中看到的那樣，Postgres 為未知時間戳提供了一個通用計劃：
指數條件：((output_tx_time >= $0) AND (output_tx_time < $1))
我的固定查詢允許 Postgres 為實際給定的常量值設計一個計劃。如果您的列統計資訊是最新的，則可以根據預期符合該時間間隔的行數進行更多優化。查詢計劃將更改為：
索引條件：((output_tx_time >= '2022-03-01T00:00:00 00:00'::timestamp with time zone) AND (output_tx_time < '2022-03-02T00:00:00 00:00': :timestamp with time zone))
您會看到不同的行估計，這可能會導致不同的查詢計劃。
當然，DELETE不可能有完全相同的計劃。除了DELETE具有寫鎖和寫入垂死行的明顯區別外，它也不能（目前 - 至少 pg 15）使用并行性，并且不能使用僅索引掃描。看：
- 使用另一個帶索引的表洗掉
因此，您將看到SELECT可能使用僅索引掃描的索引掃描。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/497100.html