升级到Postgres 9.6后，查询执行极慢

Question

我在Postgres从9.5升级到9.6后，使用Osmosis（用于处理OpenStreetMap数据的命令行Java应用程序）查询处理时间极慢。具体来说，是将本地osm数据库与主osm存储库同步（从Postgis 2.2升级到2.3）。同步以前每天进行一次，平均需要约500秒。升级后，48小时后仍未完成。与之前相比，我注意到非常缓慢（低于零）的对象处理时间（如下所示的日志记录）。我相当确定问题出在Postgres更新上，但不知道该如何解决。pg_stat_activity给出以下查询（当前）：

UPDATE ways w 
SET linestring = ( 
    SELECT ST_MakeLine(c.geom) AS way_line 
    FROM ( 
        SELECT n.geom AS geom 
        FROM nodes n 
        INNER JOIN way_nodes wn ON n.id = wn.node_id 
        WHERE (wn.way_id = w.id) 
        ORDER BY wn.sequence_id ) c ) 
WHERE w.id IN ( 
    SELECT w.id 
    FROM ways w 
    INNER JOIN way_nodes wn ON w.id = wn.way_id 
    WHERE wn.node_id = $1 
    GROUP BY w.id ) 

更新后的日志记录：

Oct 31, 2017 12:23:59 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.45
Oct 31, 2017 12:24:00 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Oct 31, 2017 12:24:00 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Oct 31, 2017 12:24:00 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Oct 31, 2017 12:24:16 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022046 with action Modify, 0.06660006660006661 objects/second.
Oct 31, 2017 4:12:49 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022047 with action Modify, 7.292125918680253E-5 objects/second.
Oct 31, 2017 6:54:27 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022053 with action Modify, 1.0311411856040729E-4 objects/second.
Oct 31, 2017 9:39:22 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022056 with action Modify, 1.0106204077408672E-4 objects/second.
Nov 01, 2017 5:07:11 AM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022057 with action Modify, 3.72178939465691E-5 objects/second.
Nov 01, 2017 7:43:20 AM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022058 with action Modify, 1.0673723278600615E-4 objects/second.

更新前的日志记录：

Oct 22, 2017 11:00:01 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.45
Oct 22, 2017 11:00:02 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Oct 22, 2017 11:00:02 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Oct 22, 2017 11:00:02 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Oct 22, 2017 11:00:17 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 557521 with action Modify, 0.06700616456714018 objects/second.
Oct 22, 2017 11:00:22 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 49820266 with action Modify, 58.15347721822542 objects/second.
Oct 22, 2017 11:00:27 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 81804664 with action Modify, 40.64719810576164 objects/second.
Oct 22, 2017 11:00:32 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 109690032 with action Modify, 24.57577530719719 objects/second.
Oct 22, 2017 11:00:38 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 124684125 with action Modify, 58.35490938060148 objects/second.

- picmate 涅

---

你使用了什么方法进行升级 - pg_upgrade吗？如果是这样，升级后是否对数据库执行了ANALYZE操作？ - harmic

谢谢您的评论。是的，它是pg_upgrade。不，我没有执行ANALYZE。我会尝试一下。 - picmate 涅

pg_upgrade 创建了一个名为 analyze_new_cluster 的 shell 脚本/批处理文件，目的就是为此而设。 - user330315

Harmic，你应该像这样回答，这样我们就可以将其标记为正确答案并为您点赞！ - Spence

Answer 1

提醒一下，我遇到了完全相同的问题：我把pg从9.4升级到了9.6后，查询速度变慢了很多。

我只运行了vacuum，没有改变什么，但是当我按照@harmic的建议运行了analyze之后，现在运行得很好（甚至更快，多亏了pg 9.6的改进 :)）！

- babis21

---

pg_upgrade 会创建一个名为 analyze_new_cluster 的 shell 脚本 / 批处理文件，专门用于此目的（我甚至认为当 pg_upgrade 完成时会提示您运行该脚本）。 - user330315

这个问题也困扰了我。我并没有有意跳过运行分析脚本，但我不知道它有多重要——听起来像是与收集性能统计信息相关的可选任务，而不是确保数据库保持在可接受速度工作的基本任务！ - mpavey

Answer 2

在升级Postgres之后，您需要分析数据库以获得快速的查询计划。

要分析整个数据库，请运行以下查询

ANALYZE VERBOSE  --for all database analyze 

如果只想分析特定的表格，请使用以下查询

ANALYZE VERBOSE  [Table_name] --for particular table

这个查询在以下支持的版本中可用：Current (13) / 12 / 11 / 10 / 9.6 / 9.5

参考链接: https://www.postgresql.org/docs/9.1/sql-analyze.html

- nirav kanani