代码之家 › 专栏 › 技术社区 › Ricardo

对具有300万行的PostgreSQL数据库的慢速简单更新查询

sql-update postgresql sql

Ricardo · 技术社区 · 15 年前

我正在尝试一个简单的 UPDATE table SET column1 = 0 在Postegres 8.4上有大约300万行的桌子上,但这要花很长时间才能完成。它已经跑了10分钟多了。现在是我最后一次尝试。

以前,我尝试运行一个真空表并分析该表上的命令,我也尝试创建一些索引(尽管我怀疑在这种情况下这会有什么不同),但没有一个看起来有帮助。

还有其他想法吗?

谢谢, 李嘉图

更新:

这是表格结构:

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;

9 回复 | 直到 7 年前

rogerdpack 8 年前

看看这个答案: PostgreSQL slow on a large table with arrays and lots of updates

首先从更好的填充因子开始,执行真空填充以强制重写表,并在更新查询后检查热更新:

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

当您有很多记录需要更新时,热更新会更快。有关Hot的更多信息,请参见 article .

另外,您需要8.3版或更高版本。

Le Droid 11 年前

我必须用每行的不同值更新10亿或20亿行的表。每次运行都会产生约1亿次变化(10%)。我的第一个尝试是将它们分组到特定分区上的300K更新事务中,因为如果使用分区,PostgreSQL并不总是优化准备好的查询。

“update mytable set myfield=value其中 MyID= ID
给予 一千五百 更新/秒。这意味着每次跑步都会至少需要18个小时。
热更新解决方案,如此处所述,填充因子=50。给予 1600次更新/秒。我用的是固态硬盘,所以改进成本很高双倍的存储空间。
插入到具有更新值的临时表中,然后将其合并到更新…从给出 一万八千 更新/秒。如果我用吸尘器对于每个分区;否则为100000 up/s。库托
这里是操作顺序:

CREATE TEMP TABLE tempTable (id BIGINT NOT NULL, field(s) to be updated,
CONSTRAINT tempTable_pkey PRIMARY KEY (id));

根据可用的RAM,在缓冲区中积累大量更新当它被填满,或者需要更改表/分区,或者完成时:

COPY tempTable FROM buffer;
UPDATE myTable a SET field(s)=value(s) FROM tempTable b WHERE a.id=b.id;
COMMIT;
TRUNCATE TABLE tempTable;
VACUUM FULL ANALYZE myTable;

这意味着现在一次运行需要1.5小时而不是18小时进行1亿次更新,包括真空。

rogerdpack 8 年前

在等了35分钟后,我决定尝试一些不同的方法来完成更新查询(但仍然没有完成)。所以我做的是一个命令:

CREATE TABLE table2 AS 
SELECT 
  all the fields of table1 except the one I wanted to update, 0 as theFieldToUpdate
from myTable

然后添加索引,然后删除旧表并重命名新表以取代它。这只需要1.7分钟的处理时间,再加上一些额外的时间来重新创建索引和约束。但它确实有帮助!:)

当然,这只是因为没有其他人在使用数据库。如果是在生产环境中,我需要首先锁定表。

rogerdpack 8 年前

今天我花了很多时间处理类似的问题。我找到了一个 solution : 在更新之前删除所有约束/索引 . 无论正在更新的列是否已编入索引,PSQL似乎都会更新所有已更新行的所有索引。更新完成后,重新添加约束/索引。

rogerdpack 8 年前

试试这个(注意 generalFreq 以类型real开始,并保持不变):

ALTER TABLE myTable ALTER COLUMN generalFreq TYPE REAL USING 0;

这将重写表,类似于drop+create,并重新生成所有索引。但都是一个命令。速度快得多(大约是2倍),而且您不必处理依赖关系,也不必重新创建索引和其他内容,尽管它确实会在持续时间内锁定表(访问独占,即完全锁定)。或者,如果你想让其他一切都排在它后面,那也许就是你想要的。如果不更新“太多”行,那么这种方式比只更新慢。

Tom Gullen 15 年前

你是怎么运作的?如果循环每一行并执行update语句,则可能会运行数百万个单独的更新,这就是为什么它的执行速度会非常慢的原因。

如果您在一条语句中为所有记录运行一条UPDATE语句,那么它的运行速度会快得多,如果这个过程很慢,那么它可能比任何其他语句都更依赖于您的硬件。300万是很多记录。

rogerdpack 8 年前

我建议的第一件事 https://dba.stackexchange.com/questions/118178/does-updating-a-row-with-the-same-value-actually-update-the-row )只更新“需要”它的行,例如:

 UPDATE myTable SET generalFreq = 0 where generalFreq != 0;

(可能还需要GeneralFreq的索引)。然后更新更少的行。尽管如果值都不是零,但更新更少的行“有帮助”,因为否则它会更新它们和所有索引,而不管值是否更改。

另一个选项:如果星按照默认值和非空约束对齐,则可以删除旧列并创建 another 只需调整元数据,即时时间。

Rolintocour 7 年前

在我的测试中,我注意到一个大的更新(超过200000行)比两个100000行的更新(即使是临时表)慢。

我的解决方案是循环,在每个循环中创建一个由200000行组成的临时表,在这个表中我计算我的值,然后用新值更新主表。

每2000000行,我手动“真空分析我的表”,我注意到自动真空不适合这样的更新。

-2

Mudassir Hasan 13 年前

尝试

UPDATE myTable SET generalFreq = 0.0;

也许这是一个演员问题