如何提高 Postgresql 中 UPDATE 查询结果时间？

Question

我有以下查询运行了 3 小时以上：

UPDATE eop_201007
set coord_x = gi.x_etrs89, coord_y = gi.x_etrs89,gr_type = 4
from eop_201007 as eop, geoindex201001 as gi
where eop.cp7=gi.cp7 AND eop.gr_type=0;

eop 表有 300k 以上的记录，gi 表有 100k 以上的记录。

cp7 字段在两个表中都建立了索引，这需要太多时间才能完成。

我做错了吗？我该如何改进这个？

Answer 1

检查此主题并使用 EXPLAIN看看发生了什么。更好的配置 WAL 也可能有所帮助，只需在更新过程中检查内存使用情况和写入速度即可。

编辑：并确保没有其他事务锁定您的表，您必须永远等待......

SELECT 
    relname, 
    * 
FROM 
    pg_locks
        JOIN pg_class ON pg_locks.relation  = pg_class.oid

Answer 2

您的 FROM 中不需要“eop_201007 as eop”。以下内容将起作用：

UPDATE eop_201007
set coord_x = gi.x_etrs89, coord_y = gi.x_etrs89,gr_type = 4
from geoindex201001 as gi
where eop_201007.cp7=gi.cp7 AND eop_201007.gr_type=0;

我认为额外的 eop 会导致交叉联接（基本上是两个巨大表的叉积），因为它不受原始 eop 表的限制，该表已经“自动”出现在 FROM 列表中

如果这不能解决问题，这里有一些其他的想法：

如果你还没有的话，你可能想先对其进行真空分析。确保在 postgresql.conf 中调整了所有内存设置。工作内存、共享缓冲区等可以产生巨大的差异。

如果这是一次性的事情，而不是每晚的工作，您应该关闭 fsync。另外，请确保（如果关闭 fsync）没有配置太多检查点段（24 个左右即可），否则会污染磁盘缓存。

正如@Frank Heikens 所说，你应该看看解释。另请检查 EXPLAIN ANALYZE（如果您的查询确实完成）。