相同的选择vs删除查询创建不同的查询计划，而执行时间却大不相同

Question

与相同的选择查询相比，我正在尝试加快删除查询，该查询似乎非常慢：

慢删除查询：
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” rel =“ nofollow noreferrer”> 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.666.666.6666.6666.666.66 66.66 66.66在X86_64-PC-Linux-GNU上，由Debian Clang版本12.0.1，64位

Postgres选择了不同的查询计划，从而导致性能截然不同。我对Postgres不太熟悉，无法理解为什么它做出了这个决定。希望有一种简单的方法可以将其引导到这里更好的计划。

Answer 1

我不确定是什么触发了选择和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提出了针对未知时间戳的通用计划：

index cond ：（（output_tx_time＆gt; = $ 0）和（output_tx_time＆lt; $ 1））

我的固定查询允许Postgres为实际给定常数值设计一个计划。如果您的列统计信息是最新的，则可以根据预期有资格使用该时间间隔的行数进行更多优化。查询计划将更改为：

index cond ：（（（output_tx_time＆gt; ='2022-03-01T00：00：00+00：00：00：00：00':: TIMESTAMP带时区）和（output_tx_time＆lt;'2022-03-03-02T00：00：00：00：00：00：00：00：00：00：00+ 00:00'::时间戳与时区））

，您会看到不同的行估计，这可能会导致不同的查询计划。

当然，delete不能具有精确相同的计划。除了delete具有写入锁并写入垂死行的明显区别外，它也无法（当前 - 至少至至少pg 15）使用 parallealism ，并且不能使用仅索引扫描。 ：

• 使用带有索引的另一个表删除

请参阅 >可能使用仅索引扫描。

Answer 2

为什么要提出不同的计划相对容易解释。首先，删除无法使用并行查询，因此据信更易于友好的计划更受到选择而不是删除的青睐。也许将来的一些限制将在某些版本中得到缓解。其次，删除不能像纯select can一样在ma_tx_out_pkey上使用folly-scan，而是使用索引扫描。这也将使删除的速度更快的速度比选择要少。这两个因素的总和显然足以使IT切换计划。我们已经看到了第一个因素的证据，您可能可以通过设置enable_seqscan关闭并查看删除选择的计划来验证这一第二个因素，如果是嵌套的循环，验证了最后一个索引扫描不是索引，则。

但是，当然，这些因素可以使计划之间的决策不同的唯一原因是，尽管计划估计在实际绩效上截然不同，但计划的估计是如此之近。那么，什么解释了这种错误的亲密关系？使用我们拥有的信息很难确定（如果您已经完成解释（分析，缓冲区）使用TRACK_IO_TIMING打开）。

一种可能性是实际绩效的差异是虚幻的。也许嵌套循环之所以如此之快，只是因为它需要的所有数据都在内存中，而唯一的原因是您使用与测试的一部分相同的参数反复执行相同的查询。如果您更改时间戳params，还是清除运行之间的postgresql缓冲区和文件缓存？

另一种可能性是您的系统的调整很差。例如，如果您的数据在SSD上，则Random_page_cost的默认设置可能太高了。 1.1可能比4更合理。

最后，您的工作设置可能太低了。这导致了使用奢侈数量的批次：8192。这是一个很难预测的性能，因为这取决于您的硬件，内核，文件系统等（这也许就是为什么计划者不尝试尝试尝试考虑到）。它非常容易测试，您可以在本地（在会话中）增加Work_mem的设置，并查看它是否改变了速度。

仅基于您的删除实际上找不到任何行删除的事实，就可以进行大部分分析。如果删除行，那将使情况更加复杂。