Slow replace update features when no check on versionid
Below is short list from pg_stat_statements.
This sql
WITH newedges(edge_id,geom) AS ( VALUES (8833205,'0102000020A21000001F000000B392C0E64CBA26405FF67D05D3C54D40950F72DB3DBA2640CC526BE3D1C54D408C0CC14D26BA26407A69B198D0C54D40E79934D216BA264027A4BB8DCFC54D40DEE0284F08BA26402065FC2ACEC54D40020CD963F1B926406A037468CCC54D406D9845D0DCB92640D89439A6CAC54D40E3E64B13ADB92640D9DDE99FC8C54D40B01FA5DD83B926408EDFB68BC8C54D40B7AF1B6667B926400D980ED6C8C54D405DD7633E47B92640AC9414E0C8C54D40F99BC65B1EB92640C0DA63B6C8C54D40D746FB4AE6B82640B0AE3897C7C54D407193E308
seems to use a lot time
Attached is a list complete full quary slow.txt
short_query | total_time | calls | mean | percentage_cpu
------------------------------------------------------------------------+------------+----------+------------+----------------
SELECT * FROM topo_ar5ngis.update_features(CAST ($ | 5898576.70 | 41 | 143867.72 | 17.56
SELECT topo_update.update_features( +| 5896761.46 | 11 | 536069.22 | 17.55
featureset | | | |
WITH newedges(edge_id,geom) AS ( VALUES (8833205,' | 5835496.08 | 3 | 1945165.36 | 17.37
SELECT primitive +| 4401377.73 | 9504 | 463.11 | 13.10
FROM +| | | |
( +| | | |
| | | |
SELECT topo_update.upsert_feature( +| 4396912.93 | 1 | 4396912.93 | 13.09
geom, +| | | |
prop, | | | |
SELECT topology.ST_RemEdgeModFace(topo.name, rec.e | 1350820.79 | 6114 | 220.94 | 4.02
WITH RECURSIVE edgering AS ( SELECT $1 as signed_e | 1021975.55 | 5382 | 189.89 | 3.04
UPDATE "topo_ar5ngis_sysdata_webclient".edge_data | 484600.59 | 1065 | 455.02 | 1.44
UPDATE "topo_ar5ngis_sysdata_webclient".edge_data | 483368.53 | 1066 | 453.44 | 1.44
UPDATE "topo_ar5ngis_sysdata_webclient".node SET c | 360300.42 | 1066 | 337.99 | 1.07
SELECT EXISTS ( SELECT $1 FROM "topo_ar5ngis_sysda | 180858.97 | 6110 | 29.60 | 0.54
SELECT EXISTS ( SELECT $1 FROM "topo_ar5ngis_sysda | 170038.90 | 6108 | 27.84 | 0.51
SELECT edge_id,start_node,end_node,left_face,right | 97049.38 | 6108 | 15.89 | 0.29
SELECT 1 FROM ONLY "topo_ar5ngis_sysdata_webclient | 71609.42 | 11676352 | 0.01 | 0.21
SELECT edge_id,start_node,end_node,left_face,right | 70094.39 | 17277 | 4.06 | 0.21
SELECT 1 FROM ONLY "topo_ar5ngis_sysdata_webclient | 61761.17 | 12092264 | 0.01 | 0.18
SELECT edge_id,geom FROM "topo_ar5ngis_sysdata_web | 57829.97 | 17275 | 3.35 | 0.17
SELECT node_id,containing_face,geom FROM "topo_ar5 | 31923.50 | 17279 | 1.85 | 0.10
UPDATE topo_ar5ngis.edge_attributes t +| 19584.33 | 1732 | 11.31 | 0.06
SET oppha | | | |
select * from topo_update.query_to_topojson($1,$2, | 18422.71 | 13 | 1417.13 | 0.05
SELECT topo_update.upsert_feature( +| 17777.94 | 1 | 17777.94 | 0.05
geom, +| | | |
prop, | | | |
WITH ring AS ( +| 17034.23 | 1619 | 10.52 | 0.05
SELECT +| | | |
row_number() OVER () | | | |
SELECT topology.ST_RemIsoNode(topo.name, rec.node_ | 16015.22 | 5651 | 2.83 | 0.05
SELECT +| 13228.53 | 1775 | 7.45 | 0.04
topology.TopoElementArray_agg(+| | | |
A | | | |
select * from topo_ar5ngis.add_border($1,$2) as re | 12919.93 | 1 | 12919.93 | 0.04
WITH newedges(edge_id,right_face) AS ( VALUES ($1, | 12374.12 | 6 | 2062.35 | 0.04
SELECT edge_id,left_face,right_face,geom FROM "top | 11824.77 | 1475 | 8.02 | 0.04
SELECT edge_id,left_face,right_face,geom FROM "top | 10752.76 | 11516 | 0.93 | 0.03
WITH newedges(edge_id,right_face) AS ( VALUES ($1, | 10502.69 | 5 | 2100.54 | 0.03
WITH newedges(edge_id,right_face) AS ( VALUES ($1, | 10420.53 | 5 | 2084.11 | 0.03
WITH newedges(edge_id,left_face) AS ( VALUES ($1,$ | 10341.30 | 5 | 2068.26 | 0.03
WITH newedges(edge_id,right_face) AS ( VALUES ($1, | 9887.99 | 4 | 2472.00 | 0.03
SELECT $2 FROM ONLY "topo_ar5ngis_sysdata_webclien | 9626.24 | 37420 | 0.26 | 0.03
select * from topo_ar5ngis.surfaces_as_geojson_jav | 9573.45 | 1 | 9573.45 | 0.03
SELECT edge_id,start_node,end_node,next_left_edge, | 9379.37 | 6003 | 1.56 | 0.03
DELETE FROM "topo_ar5ngis_sysdata_webclient".node | 8965.98 | 5651 | 1.59 | 0.03
WITH newedges(edge_id,left_face) AS ( VALUES ($1,$ | 8873.14 | 4 | 2218.28 | 0.03
WITH newedges(edge_id,right_face) AS ( VALUES ($1, | 8850.93 | 3 | 2950.31 | 0.03
WITH newedges(edge_id,right_face) AS ( VALUES ($1, | 8712.85 | 3 | 2904.28 | 0.03
CREATE TEMPORARY TABLE add_border_results_more_inf | 8527.24 | 1 | 8527.24 | 0.03