postgresql – Postgres 9.6升级后极慢的查询执行

Postgres从9.5升级到9.6与查询Osmosis(用于处理OpenStreetMap数据的命令行 Java应用程序)相关的处理时间非常慢.特别是将本地osm数据库与主osm repo同步(Postgis 2.3 from 2.2).同步过去每天都发生,平均花费大约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.