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

Postgres row_number()大约每24小时将表大小增加一倍

query-optimization postgresql database

Shpigford · 技术社区 · 3 年前

我有一个Assets表,其中有大约165000行。但是,Assets组成了“Collections”,每个Collection可能有大约10000个项目,我想为其保存一个“rank”,以便用户可以看到给定资产在集合中的排名。

排名可能会发生变化(基于内部分数),因此需要定期更新(每小时更新几次)。

目前正在以每个收藏为基础进行这项工作:

UPDATE assets a
SET rank = a2.seqnum
FROM
  (SELECT a2.*,
          row_number() OVER (
                             ORDER BY elo_rating DESC) AS seqnum
   FROM assets a2
   WHERE a2.collection_id = #{collection_id} ) a2
WHERE a2.id = a.id;

然而,这导致表的大小大约每24小时翻一番(即1GB到2GB)。

A. VACUUM FULL 解决了这个问题,但这并不是一个真正的解决方案。

可以调整查询以不创建那么多(我认为是)临时存储吗?

运行PostgreSQL 13。

0 回复 | 直到 3 年前

Erwin Brandstetter 3 年前

每次更新都会在Postgres中写入一个新行版本。所以(除了 TOAST ed列)更新表中的每一行的大小大约是它的两倍。这就是你观察到的。死元组稍后可以被清理以缩小表的物理大小——这就是为什么 VACUUM FULL 做 昂贵地 看见

Are TOAST rows written for UPDATEs not changing the TOASTable column?

或者,你可能只是不跑 真空度已满 并将桌子保持在其最小物理尺寸的两倍左右。如果你表现平平 VACUUM 没有 FULL !) 足够了——如果你没有长期运行的事务来阻止它——到下一次时,Postgres将在自由空间映射中标记死元组 UPDATE 启动并可以重用磁盘空间,因此停留大约是其最小尺寸的两倍。这可能比一直收缩和重新生长桌子便宜,因为最昂贵的部分通常是物理生长桌子。一定要有侵略性 autovacuum 表的设置。看见

可能更好 然而,将排名分解为一个最小的独立1:1表(也称为“垂直分区”),这样只需要“每小时写几次”最小的行。可能包括 elo_rating 您在查询中提到,该查询似乎至少同样频繁地更改(?)。 ( LEFT ) JOIN 到查询中的主表。虽然这增加了相当大的开销,但它可能仍然(实质上)更便宜。取决于完整的图片,最重要的是表中的平均行大小 assets 以及除了昂贵的更新之外的典型负载。

看见

推荐文章

user164863 · 我可以让这个mySQL查询更快吗?

6 年前

Ronak Patel · mysql-无适当索引的长时间运行查询

6 年前

Thomas Martres · queryset未使用order\u预回迁对象

6 年前

Mohd Abdul Mujib · 检查上次查询中是否使用了缓冲区

6 年前

Ronbear · 更改视图定义可以改进/降低两种不同的查询

6 年前

Camilo · SQL-如何对15行的组求和并找到最大和

6 年前

curiousgeorge · 在具有单独更新列的重复密钥更新批上插入

6 年前

dinwal · 索引中列的顺序

6 年前

Oblomingo · 如何优化依赖于执行时间的T-SQL查询?

6 年前

Mikhail Nikalyukin · 如何在PostgreSQL查询中优化位图堆扫描?

7 年前