sql – 如何加快缓慢的UPDATE查询

我有以下UPDATE查询：

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

现在,此查询大约需要93分钟才能完成.我想找到方法让它更快一些.

Indexer.Pages表有大约506,000行,其中大约490,000行包含LastError的值,所以我怀疑我可以利用这里的任何索引.

该表(未压缩时)中包含大约46个数据,但大多数数据位于名为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;

此表上没有触发器,还有一个表在Pages.PageId上具有FK约束.

解决方法

到目前为止,@kgrittn posted as comment是最好的答案.我只是在填写细节.

在执行任何其他操作之前,您应该upgrade PostgreSQL to a current version,至少是主要版本的最后一个安全版本. See guidelines on the project.

我还要强调Kevin提到的涉及LastError列的索引.通常情况下,HOT更新可以回收数据页面上的死行,并使更新速度更快 – 有效地消除(大部分)抽真空的需要.有关：

> Redundant data in update statements

如果以任何方式在任何索引中使用您的列,则禁用HOT UPDATE,因为它会破坏索引.如果是这种情况,您应该能够通过在更新之前删除所有这些索引来大大加快查询速度,并在以后重新创建它们.

在这种情况下,它将有助于运行多个较小的UPDATE：
如果……
…更新的列不参与任何索引(启用HOT更新).
… UPDATE很容易在多个事务中分成多个补丁.
…这些补丁中的行分布在表格上(物理上,不是逻辑上).
…没有其他并发事务可以保持死元组不被重用.

然后你不需要在多个补丁之间使用VACCUUM,因为HOT更新可以直接重用死元组 – 只有先前事务中的死元组,而不是来自相同或并发的死元组.您可能希望在操作结束时安排VACUUM,或者只是让自动抽真空完成其工作.

对于UPDATE不需要的任何其他索引也可以这样做 – 并且从您的数字判断UPDATE无论如何都不会使用索引.如果更新表的大部分内容,则从头开始构建新索引比使用每个更改的行逐步更新索引要快得多.

此外,您的更新不可能破坏任何外键约束.您可以尝试删除&重新创造那些.这确实打开了一个不强制引用完整性的时隙.如果在UPDATE期间违反了完整性,则在尝试重新创建FK时会出错.如果你在一个事务中完成所有操作,并发事务永远不会看到丢弃的FK,但你对表执行写锁定 – 与删除/重新创建索引或触发器相同

最后,disable & enable triggers更新不需要.

务必在一次交易中完成所有这些操作.也许在许多较小的补丁中进行,因此它不会阻止并发操作太长时间.

所以：

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. Per documentation:

VACUUM cannot be executed inside a transaction block.
@H_301_68@
您可以将操作拆分为几个大块并在两者之间运行：
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;

sql – 如何加快缓慢的UPDATE查询

解决方法

猜你在找的MsSQL相关文章