对 300 万行的 PostgreSQL 数据库进行慢速简单更新查询 [英] Slow simple update query on PostgreSQL database with 3 million rows

问题描述

我正在尝试一个简单的 UPDATE table SET column1 = 0 在 Postegres 8.4 上大约 300 万行的表上，但它需要很长时间才能完成.它已经运行了10多分钟.现在是我的最后一次尝试.

I am trying a simple UPDATE table SET column1 = 0 on a table with ~3 million rows on Postegres 8.4 but it is taking forever to finish. It has been running for more than 10 min. now in my last attempt.

之前，我尝试在该表上运行 VACUUM 和 ANALYZE 命令，并且还尝试创建一些索引(尽管我怀疑这在这种情况下会有所不同)但似乎没有任何帮助.

Before, I tried to run a VACUUM and ANALYZE commands on that table and I also tried to create some indexes (although I doubt this will make any difference in this case) but none seems to help.

还有其他想法吗?

谢谢，里卡多

更新:

这是表结构:

CREATE TABLE myTable
(
  id bigserial NOT NULL,
  title text,
  description text,
  link text,
  "type" character varying(255),
  generalFreq real,
  generalWeight real,
  author_id bigint,
  status_id bigint,
  CONSTRAINT resources_pkey PRIMARY KEY (id),
  CONSTRAINT author_pkey FOREIGN KEY (author_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT c_unique_status_id UNIQUE (status_id)
);

我正在尝试运行 UPDATE myTable SET generalFreq = 0;

推荐答案

看看这个答案:PostgreSQL 在包含数组和大量更新的大表上运行缓慢

首先从更好的 FILLFACTOR 开始，执行 VACUUM FULL 以强制表重写并在 UPDATE 查询后检查 HOT 更新:

First start with a better FILLFACTOR, do a VACUUM FULL to force table rewrite and check the HOT-updates after your UPDATE-query:

SELECT n_tup_hot_upd, * FROM pg_stat_user_tables WHERE relname = 'myTable';

当您有大量记录要更新时，HOT 更新会更快.更多关于 HOT 的信息可以在这个 文章.

HOT updates are much faster when you have a lot of records to update. More information about HOT can be found in this article.

附言.您需要 8.3 或更高版本.

Ps. You need version 8.3 or better.

这篇关于对 300 万行的 PostgreSQL 数据库进行慢速简单更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！