如何加快慢速UPDATE查询

Question

我有以下UPDATE查询：

UPDATE Indexer.Pages SET LastError=NULL where LastError is not null;

目前，此查询大约需要93分钟才能完成。我想找到使它更快一点的方法。

该Indexer.Pages表大约有506,000行，其中大约490,000行包含的值LastError，因此我怀疑我是否可以利用此处的任何索引。

该表（未压缩时）中包含约46
gigs的数据，但是该数据的大部分位于名为的文本字段中html。我相信简单地加载和卸载许多页面会导致速度下降。一个想法是做一个新表 只是
在Id和html现场，并保持Indexer.Pages尽可能小。但是，测试该理论将是一件可观的工作，因为我实际上没有硬盘空间来创建表的副本。我必须将其复制到另一台计算机上，放下表，然后将数据复制回去，这可能需要整个晚上。

有想法吗？我正在使用Postgres 9.0.0。

更新：

这是模式：

CREATE TABLE indexer.pages
(
  id uuid NOT NULL,
  url character varying(1024) NOT NULL,
  firstcrawled timestamp with time zone NOT NULL,
  lastcrawled timestamp with time zone NOT NULL,
  recipeid uuid,
  html text NOT NULL,
  lasterror character varying(1024),
  missingings smallint,
  CONSTRAINT pages_pkey PRIMARY KEY (id ),
  CONSTRAINT indexer_pages_uniqueurl UNIQUE (url )
);

我也有两个索引：

CREATE INDEX idx_indexer_pages_missingings
  ON indexer.pages
  USING btree
  (missingings )
  WHERE missingings > 0;

和

CREATE INDEX idx_indexer_pages_null
  ON indexer.pages
  USING btree
  (recipeid )
  WHERE NULL::boolean;

该表上没有触发器，并且还有另一个表具有FK约束Pages.PageId。

Answer 1

在执行 其他任何操作 之前， 应将
PostgreSQL 升级
到当前版本，至少是主版本的最新安全版本。请参阅该项目的准则。

我还想强调一下Kevin提到的涉及该列的 索引LastError。通常， HOT更新 可以回收数据页上的死行，并使UPDATE更快得多-
有效地消除了（大部分）清理工作的需要。

如果您的列 以任何方式 在任何索引 中 使用，则会禁用HOT
UPDATE，因为它会破坏索引。如果真是这样，您应该可以通过在删除所有这些索引之前并稍后重新创建它们来大大加快查询 的 速度UPDATE。

在这种情况下，它将有助于运行多个较小的UPDATE： 如果 ……
update列不包含在任何索引中（启用HOT更新）。…UPDATE在 多个
事务中很容易分为多个补丁。…这些修补程序中的行分布在整个表上（物理上，而不是逻辑上）。…没有其他并发事务可以防止死元组被重用。

然后，您将不需要VACCUUM在多个修补程序之间进行操作，因为HOT更新可以直接重用死元组-仅重用来自 先前
事务的死元组，而不是来自相同或并发事务的元组。您可能想VACUUM在操作结束时安排一个时间，或者只是让自动真空完成其工作。

对于UPDATE-不需要的任何其他索引，也可以执行相同操作，然后从您的数字判断，UPDATE无论如何都不会使用索引。如果要更新表的大部分内容，那么从头开始构建新索引要比对每个已更改的行进行增量更新索引要快得多。

另外，您的更新不太可能破坏任何 外键约束
。您也可以尝试删除并重新创建它们。这确实会打开一个时隙，在该时隙中不会强制执行参照完整性。如果在此期间违反了完整性，则在UPDATE尝试重新创建FK时会收到错误消息。如果您在
一个 事务中完成所有操作，则并发事务永远都不会看到已删除的FK，但是您要在表上进行写锁定-与删除/重新创建索引或触发器相同）

最后，禁用和启用 ****更新不需要的
触发器 。

确保一次完成所有这些操作。也许可以在许多较小的补丁程序中执行此操作，因此它不会阻塞并发操作太长时间。

所以：

BEGIN;
ALTER TABLE tbl DISABLE TRIGGER user; -- disable all self-made triggers
-- DROP indexes (& fk constraints ?)
-- UPDATE ...
-- RECREATE indexes (& fk constraints ?)
ALTER TABLE tbl ENABLE TRIGGER user;
COMMIT;

您不能VACUUM在事务块内运行。每个文档：

VACUUM 无法在事务块内执行。

您可以将您的操作分成几个大块，然后在两个之间运行：

VACUUM ANALYZE tbl;

如果您不必处理并发事务，则可以（甚至更有效）：

ALTER TABLE tbl DISABLE TRIGGER user; -- disable all self-made triggers
-- DROP indexes (& fk constraints ?)

-- Multiple UPDATEs with logical slices of the table
-- each slice in its own transaction.
-- VACUUM ANALYZE tbl;  -- optionally in between, or autovacuum kicks in

-- RECREATE indexes (& fk constraints ?)
ALTER TABLE tbl ENABLE TRIGGER user;