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约束.
解决方法
在执行任何其他操作之前,您应该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:
VACUUMcannot be executed inside a transaction block.
您可以将操作拆分为几个大块并在两者之间运行：
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;
