Slow AsTopoJSON when running on big tables #13

larsop · 2020-11-10T11:44:46Z

Is case test case I have 4107624 rows in edge_data, with full test case it will around 30 mill edges.

When I run the SQL below its's done in less 4 ms. The number of rows returned is 71 from the SQL below.

select distinct a.*
from
topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl
WHERE fa.mbr && ST_GeomFromEWKT('POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))') AND ((a.geo ).id) = re.topogeo_id AND
re.layer_id = tl.layer_id AND tl.schema_name = 'topo_ar5_sysdata_webclient_t2' AND tl.table_name = 'face_attributes' and fa.face_id=re.element_id;

but when I use this as input to in the sql below it takes more than a minute,

select * from topo_rein.query_to_topojson('select distinct a.* from topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl WHERE fa.mbr && ST_GeomFromEWKT(''POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'') AND ((a.geo ).id) = re.topogeo_id AND re.layer_id = tl.layer_id AND tl.schema_name = ''topo_ar5_sysdata_webclient_t2'' AND tl.table_name = ''face_attributes'' and fa.face_id=re.element_id',
25832,0,0);

Most of time is here .


CREATE TEMP TABLE topo_rein_topojson_edgemap(arc_id serial primary key, edge_id int);
CREATE TABLE
Time: 4.818 ms

CREATE INDEX ON topo_rein_topojson_edgemap(edge_id);
CREATE INDEX
Time: 0.744 ms

select AsTopoJSON('(4,2,742280,3)'::topogeometry,'topo_rein_topojson_edgemap');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             astopojson                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------...skipping...
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 { "type": "MultiPolygon", "arcs": [[[182,181,180,-180,-179,177,-177,175,174,173,-173,171,170,169,168,167,166,165,164,-164,162,161,160,-160,-159,157,156,155,-155,-154,152,-152,-151,149,148,147,-147,145,-145,-144,-143,-142,140,139,-139,-138,-137,-136,134,-134,-133,-132,130,-130,128,127,126,125,124,123,122,121,120,119,118,117,-117,115,-115,-114,112,-112,110,109,-109,107,106,105,104,103,102,101,100,-100,98,97,96,-96,94,93,-93,91,-91,89,-89,-88,86,-86,84,83,82,81,80,79,-79,-78,-77,-76,-75,-74,-73,-72,70,69,68,67,-67,-66,-65,-64,-63,-62,-61,59,58,-58,56,-56,-55,-54,-53,51,50,49,48,47,-47,45,-45,-44,42,-42,40,-40,38,-38,36,35,-35,-34,32,-32,30,29,28,27,-27,-26,-25,-24,22,-22,-21,-20,-19,-18,16,-16,-15,13,12,-12,-11,9,8,-8,6,5,-5,3,-3,1,0],[183],[184],[-186],[-187],[-188],[188],[189],[-191],[191],[192],[-194],[194],[-196],[196],[197],[-199],[-200],[200],[-202],[202],[203],[204],[-206],[206],[-208],[208],[209],[-211],[-212],[212],[-214],[-215],[215],[-217],[217],[-219],[-220],[-221],[221],[222],[223],[224],[225],[226],[227],[228],[-230],[-231],[231],[232],[233],[234],[235],[-237],[237],[238],[-240],[240],[241],[-243],[243],[244],[-246],[246],[-248],[248],[249],[250],[251],[252],[-254],[254],[255],[-257],[-258],[258],[260,259],[-262],[262],[-264],[264],[265],[266],[267],[-269],[269],[-271],[271],[272],[273],[274],[275],[-277],[277],[-279],[-280],[280],[-282],[282],[-284],[284],[285],[-287],[287],[-289],[-290],[290],[291],[292],[295,294,293],[-297],[297],[298],[-300],[300],[301],[-303],[303],[304],[305],[-307],[307],[308],[309],[-311],[311],[312],[313],[314],[315],[316],[317],[318],[319],[320],[-322],[322],[323],[-325],[325],[326],[327],[328],[329],[330],[331],[332],[333],[334],[-336],[336],[-338],[338],[339],[340],[341],[342],[343],[-345],[345],[346],[347],[348],[-350],[350],[351],[-353],[353],[-355],[355],[356],[357],[358],[359],[360],[361],[362],[363],[-365],[365],[-367],[-368],[368],[369],[370],[-372],[372],[373],[-375],[375],[-377],[377],[378],[-380],[380],[381],[-383],[383],[384],[-386],[-387],[387],[388],[-390],[390],[391],[-393],[393],[394],[395],[396],[397],[398],[399],[400],[401],[-403],[-404],[-405],[405],[406],[407],[-409],[-410],[410],[411],[-413],[413],[-415],[415],[-417],[417],[-419],[-420],[-421],[421],[-423],[-425,423],[425],[-427],[427],[-429],[-430],[430],[431],[-433],[-434],[-435],[-436],[436],[-438],[-439],[-440],[440],[441],[442],[443],[444],[445],[446],[-448],[-449],[-450],[-451],[452,451],[453],[454],[455],[-457],[457],[458],[459],[-461],[461],[-463],[-464],[464],[-466],[466],[467],[468],[-470],[470],[471],[-473],[473],[474],[475],[476],[477],[-479],[479],[480],[481],[482],[483],[484],[485],[486],[487],[488],[489],[490],[-492],[-493],[493],[494],[495],[496],[-498],[498],[499],[500],[501],[502],[503],[504],[-506],[506],[507],[-509],[509],[510],[511],[512],[513],[514],[515],[516],[517],[518],[519],[520],[521],[522],[523],[524],[525],[526],[527],[528],[-530],[530],[531],[532],[533],[534],[535],[536],[538,-538],[539],[540],[541],[542],[543],[544],[545],[546],[547],[548],[-550],[550],[551],[552],[-554],[554],[555],[556],[-558],[559,558],[-561],[561],[562],[-564],[564],[565],[566],[567],[568],[569],[-571],[-572],[572],[-574],[574],[-576],[576],[-578],[-580,578],[580],[581],[-583],[583],[584],[-586],[586],[587],[589,-589],[590],[-592],[-593],[593],[-595],[-596],[596],[597],[598],[599],[600],[601],[-603],[623,-623,-622,-621,-620,-619,617,616,615,614,613,612,-612,610,-610,608,-608,606,605,604,-604],[-625],[625],[626],[627],[628],[629],[630],[631],[632],[-634],[-635],[-636],[636],[-638],[-641,639,-639],[641],[-643],[643],[644],[645],[-647],[647],[648],[649],[-651],[-652],[652],[653],[654],[655],[-657],[-658],[658],[659],[-661],[661],[-663],[-664],[664],[-666],[666],[-668],[-669],[-670],[670],[671],[672],[-674],[-675],[-676],[676],[-678],[-679],[679],[-681],[681],[682],[-684],[684],[685],[686],[687],[688],[689],[690],[691],[-693],[693],[694],[-696],[696],[-698],[698],[699],[-701],[-702],[702],[703],[-705],[705],[-707],[-708],[711,710,709,-709],[712],[-714],[-715],[715],[716],[-718],[-719],[719],[720],[721],[722],[-724],[-725],[725],[-727],[727],[728],[-730],[-732,-731],[-733],[-734],[734],[735],[736],[737],[738],[-740],[-741],[-742],[-743],[743],[744],[-746],[746],[747],[748],[749],[-751],[-752],[752],[753],[-755],[-756],[756],[757],[758],[-760],[760],[761],[762],[763],[-765],[765],[766],[768,767],[769],[-771],[771],[772],[773],[-775],[-776],[-777],[777],[-779],[-780],[-781],[781],[782],[783],[784],[785],[786],[-788],[-789],[-790],[790],[791],[792],[-794],[794],[-796],[796],[-798],[-799],[799],[800],[801],[-803],[-804],[804],[805],[-807],[-808],[808],[-810],[810],[811],[812],[813],[814],[-816],[816],[817],[-820,-819],[820],[821],[822],[823],[-825],[825],[-827],[-828],[828],[-830],[-831],[-832],[832],[833],[834],[835],[836],[-838],[838],[-840],[840],[-842],[-843],[843],[844],[-846],[846],[847],[848],[-850],[-851],[851],[-853],[853],[854],[-856],[-857],[857],[858],[-860],[860],[-862],[862],[863],[-865],[865],[-867],[-868],[868],[869],[870],[871],[872],[-874],[874],[875],[-877],[877],[-879],[-880],[-881],[-882],[-883],[883],[-885],[-886],[886],[887],[888],[889],[890],[891],[892],[893],[894],[-896],[896],[-898],[898],[899],[900],[-902],[902],[-904],[-905],[905],[906],[-908],[-909],[-910],[910],[911],[-913],[-914],[-915],[915],[916],[-918],[-919],[-920],[920],[922,921],[923],[924],[925],[926],[-928],[-929],[929],[930],[931],[-933],[933],[934],[935],[936],[937],[-939],[939],[-941],[941],[-943],[943],[-945],[945],[-947],[947],[-950,948],[950],[-952],[-953],[953],[-955],[955],[-957],[957],[-959],[-960],[960],[961],[962],[963],[-965],[966,-966],[-968],[968],[969],[-971],[971],[972],[-974],[975,974],[-977],[-978],[-979],[979],[-981],[-982],[-983],[-984],[-985],[985],[-987],[987],[989,-989],[990],[991],[992],[-994],[994],[995],[-997],[997],[-999],[-1000],[1000],[1002,1001],[1003],[-1005],[1005],[1006],[1007],[1008],[-1010],[-1012,-1011],[-1013],[-1014],[1014],[-1016],[-1017],[1017],[1019,-1019],[-1022,1020],[1023,-1023],[1024],[-1026],[-1027],[-1028],[-1029],[-1030],[1030],[1031],[-1033],[-1034],[-1035],[-1036],[1036],[1037],[-1039],[-1040],[-1041],[1041],[1042],[-1044],[1044],[-1046],[-1047],[1047],[-1050,1048],[-1051],[-1052],[-1053],[-1054],[1054],[1055],[1056],[-1058],[1058],[-1061,1059],[1061],[-1063],[1063],[-1065],[1066,1065],[-1068],[1068],[1069],[-1071],[-1072],[-1073],[1073],[1074],[-1076],[1076],[-1078],[-1079],[1079],[1080],[-1082],[-1083],[-1084],[1084],[1085],[-1087],[-1088],[-1089],[-1090],[1090],[-1092],[1092],[-1094],[1094],[-1096],[1105,1104,-1104,1102,-1102,-1101,-1100,-1099,1097,1096],[1106],[1107],[-1109],[-1110],[1111,-1111],[-1113],[1113],[1114],[-1116],[1116],[-1118],[-1119],[-1120],[-1121],[-1122],[-1123],[-1124],[1124],[1125],[-1127],[1127],[-1129],[-1130],[1131,-1131],[-1133],[1133],[1134],[-1136],[-1137],[-1138],[1138],[1139],[-1141],[1141],[-1143],[1143],[1144],[-1146],[-1147],[-1148],[-1149],[-1150],[1151,-1151],[-1153],[1153],[-1155],[-1156],[-1157],[-1158],[1158],[1159],[1160],[-1163,-1162],[-1164],[-1165],[-1166],[1166],[1167],[-1169],[1169],[-1171],[1171],[1172],[-1174],[-1175],[-1176],[-1177],[-1178],[1178],[1179],[1180],[1181],[1182],[1183],[1184],[1185],[1187,-1187],[1188],[1190,1189],[-1192],[1192],[-1194],[-1195],[1196,-1196],[-1198],[-1199],[1199],[-1201],[-1202],[1202],[-1204],[1204],[-1206],[-1207],[-1208],[-1209],[-1210],[-1211],[-1212],[-1213],[-1214],[-1215],[-1216],[-1217],[-1218],[-1220,-1219],[1220],[-1222],[-1223],[-1224],[-1225],[1225],[1226],[1227],[-1229],[-1230],[-1231],[1231],[1232],[-1234],[-1235],[-1236],[-1237],[1237],[1238],[1239],[1240],[-1243,-1242],[-1244],[-1245],[-1246],[-1247],[-1248],[-1249],[-1250],[1250],[1251],[1252],[-1254],[-1255],[-1256],[1256],[1257],[1265,-1265,1263,1262,1261,1260,1259,1258],[1266],[1267],[1269,-1269],[1270],[1271],[-1273],[-1274],[1274],[1275],[-1277],[1277],[1278],[1279],[1280],[1281],[1282],[1283],[1287,1286,-1286,-1285],[-1289],[1289],[1290],[-1292],[-1293],[1293],[-1295],[-1296],[-1297],[-1298],[-1299],[1299],[1300],[1301],[1302],[-1304],[-1305],[-1306],[-1307],[1307],[1308],[-1310],[-1311],[-1312],[-1313],[1313],[-1315],[1315],[1316],[1317],[-1319],[1319],[1320],[-1322],[-1323],[1323],[-1325],[-1326],[1326],[-1328],[-1329],[-1330],[1330],[-1332],[1332],[-1334],[-1335],[-1336],[-1337],[-1338],[-1339],[-1340],[-1341],[-1342],[-1343],[-1344],[-1345],[-1346],[-1347],[-1348],[-1349],[-1350],[-1351],[-1352],[1352],[-1354],[-1355],[-1356],[-1357],[-1358],[-1359],[-1360],[1360],[-1362],[-1363],[1370,-1370,-1369,-1368,-1367,-1366,1364,-1364],[1374,1373,-1373,1371],[-1376],[-1377],[-1378],[1378],[-1380],[-1381],[-1382],[1385,-1385,1383,-1383],[-1387],[-1391,1389,1388,1387],[-1392],[-1393],[-1394],[-1395],[-1396],[-1397],[1404,1403,1402,-1402,1400,-1400,-1399,-1398],[-1406],[-1407],[-1408],[-1409],[-1410],[-1411],[-1412],[-1413],[-1414],[-1415],[-1416]]]}
(1 row)

Time: 9707.204 ms (00:09.707)

The text was updated successfully, but these errors were encountered:

larsop · 2020-11-10T12:01:12Z

This is also discussed in this issue .

Length of output

select Length(AsTopoJSON('(4,2,742280,3)'::topogeometry,'topo_rein_topojson_edgemap'));
 length 
--------
   8899
(1 row)

larsop · 2020-11-11T14:52:42Z

If I test with GeoJson it's about 50 times faster.

select length(ST_AsGeoJSON(('(4,2,742280,3)'::topogeometry)::geometry));
 length 
--------
 876996
(1 row)

Time: 187.651 ms

larsop · 2020-11-12T10:02:25Z

Here is some stats about this input polygon


select ST_NumGeometries(('(4,2,742280,3)'::topogeometry)::geometry);
 st_numgeometries 
------------------
                1
(1 row)

select count(*) as num_rings from ( select * from ST_DumpRings(ST_GeometryN(('(4,2,742280,3)'::topogeometry)::geometry,1))) as foo;
 num_rings 
-----------
      1138
(1 row)

select count(*) as num_points from ( select * from ST_DumpPoints(ST_GeometryN(('(4,2,742280,3)'::topogeometry)::geometry,1))) as foo;
 num_points 
------------
      32661
(1 row)

The mbr POLYGON((7.045622708820386 58.6096367578006,7.045622708820386 58.67156838867955,7.138942013511574 58.67156838867955,7.138942013511574 58.6096367578006,7.045622708820386 58.6096367578006)) of this polygon i many times bigger than the the area I request data for POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.13068723311331658.631042390424106))'

strk · 2020-11-12T17:33:33Z

Is there a chance to get a small-enough dump of the database ?

larsop · 2020-11-12T17:37:26Z

I check this some more and the problem in this case was all the holes, which would not show up anyway on the selected area by the client. In this Postgis branch I added bounding a box parameter and then it's about 80 times faster.


select length(AsTopoJSON('(4,2,742280,3)'::topogeometry,'topo_rein_topojson_edgemap','POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'::geometry));
 length 
--------
   1493
(1 row)

Time: 117.616 ms

This also reduces the payload that need to be sent to the client. (I have not tested topojson yet)

{ "type": "MultiPolygon", "arcs": [[[182,181,180,-180,-179,177,-177,175,174,173,-173,171,170,169,168,167,166,165,164,-164,162,161,160,-160,-159,157,156,155,-155,-154,152,-152,-151,149,148,147,-147,145,-145,-144,-143,-142,140,139,-139,-138,-137,-136,134,-134,-133,-132,130,-130,128,127,126,125,124,123,122,121,120,119,118,117,-117,115,-115,-114,112,-112,110,109,-109,107,106,105,104,103,102,101,100,-100,98,97,96,-96,94,93,-93,91,-91,89,-89,-88,86,-86,84,83,82,81,80,79,-79,-78,-77,-76,-75,-74,-73,-72,70,69,68,67,-67,-66,-65,-64,-63,-62,-61,59,58,-58,56,-56,-55,-54,-53,51,50,49,48,47,-47,45,-45,-44,42,-42,40,-40,38,-38,36,35,-35,-34,32,-32,30,29,28,27,-27,-26,-25,-24,22,-22,-21,-20,-19,-18,16,-16,-15,13,12,-12,-11,9,8,-8,6,5,-5,3,-3,1,0],[184,183],[187,186,185],[-190,188],[191,190],[193,-193],[195,194],[-197],[-199,197],[200,-200],[221,-221,-220,-219,-218,-217,215,214,213,212,211,210,-210,208,-208,206,-206,204,203,202,-202],[-225,223,-223],[228,227,226,-226],[-231,-230],[232,231],[-235,-234],[236,235],[-239,237],[240,-240],[242,241],[244,-244],[246,245],[-249,-248],[250,-250],[-253,251],[254,-254],[-257,255],[-259,257],[260,259],[270,269,-269,267,-267,-266,-265,-264,262,261],[272,-272],[274,-274],[276,-276],[-279,-278],[280,-280],[282,281],[284,-284],[-287,-286],[-289,-288],[296,-296,294,293,292,291,290,289],[298,-298],[302,301,-301,-300],[310,-310,-309,-308,-307,-306,304,-304],[314,313,-313,311],[318,-318,316,-316],[-323,321,320,319],[330,329,328,-328,326,-326,-325,-324]]]}

larsop · 2020-11-12T18:35:41Z

Is there a chance to get a small-enough dump of the database ?

Added a dump of the of topology schema topo_ar5_sysdata_webclient_t2 that I am testing on.

strk · 2020-11-12T19:21:51Z

Looking at that TopoJSON output, is it normal to have all those dangling edges ? Many arc_id are present as both positive and negative. In the very first output I see even internal rings formed by the SAME edge ?? See [-470],[470]

Are you sure the topology is valid ? I'd go check for those edges (those are arc_id, actual edge identifiers would be in the edgemap table). Either the topology is invalid or the code of AsGeoJSON is badly broken

strk · 2020-11-12T19:45:37Z

Oops, forget last comment, it's the two's complement for negative arcs which puzzled me !

strk · 2020-11-12T20:38:56Z

I received a dump of the edges for TopoGeometry 742280 (referenced in #13 (comment)), loaded them in a new topology and created a TopoGeometry object representing the corresponding polygon. Creating the TopoGeometry using toTopoGeom took ~13 seconds on itself.

The polygon has 1137 holes so the topology ends up having 1138 faces. The topogeometry is composed by a single element: the outer face, All the 2415 edges of the topology will be part of the output TopoJSON.

Calling AsTopoJSON against the so-created TopoGeometry on this system takes ~18 seconds, no matter the presence of an index in the edgemap, or if the edgemap starts empty or is already populated.

The TopoJSON output size is 15429 bytes.

I'll profile and report back what I find.

strk · 2020-11-12T23:10:44Z

I have a couple of ideas to speed the query up.

First idea brings down the time on my machine from 18 seconds to 2 second, using a temporary table to store all edges binding the TopoGeometry and thus not hitting the edges table more than once per TopoGeometry. The speed improvement may be higher when the topology has more than ONLY the subject topogeometry. The change currently results in some difference in output for the regression testsuite so I need to check those more carefully.

The second idea is just a minor refactoring that uses NO temporary table (so still hits the edge table) but brings down the time from 18 seconds to 5 seconds for me.

This second idea is less intrusive and quicker to test, if you want test it @larsop here's the patch:

commit 83e27be8ccdd84335f1844e38c26b0011ad094ae (HEAD -> topojson-drop-visited-from-bounding)
Author: Sandro Santilli <strk@kbt.io>
Date:   Thu Nov 12 23:30:21 2020 +0100

    Drop visited edges from bounding edges

diff --git a/topology/sql/export/TopoJSON.sql.in b/topology/sql/export/TopoJSON.sql.in
index e8b96378e..b7337c9a0 100644
--- a/topology/sql/export/TopoJSON.sql.in
+++ b/topology/sql/export/TopoJSON.sql.in
@@ -258,6 +258,11 @@ FROM _edgepath
 
       END LOOP; -- }
 
+      SELECT array_agg(x) FROM unnest(bounding_edges) x
+      WHERE NOT x = ANY(visited_edges)
+      INTO bounding_edges;
+      visited_edges := ARRAY[]::int[];
+
 #ifdef POSTGIS_TOPOLOGY_DEBUG
       --RAISE DEBUG 'Edges found:%, visited faces: %, ARCS: %' , edges_found, shell_faces, arcs;
 #endif

strk · 2020-11-12T23:38:20Z

Upstream ticket: https://trac.osgeo.org/postgis/ticket/4789

strk · 2020-11-12T23:55:25Z

The temporary-table implementation is in this git branch: https://git.osgeo.org/gitea/strk/postgis/src/branch/topojson-temptable

I've yet to verify if ordering differences are significant or not

larsop · 2020-11-13T05:26:35Z

Thanks I will test your patches today , but first since I had started with the patch where I use bounding box as optional parameter to AsTopoJSON I tested that first.

In this case the client moves around in small areas, from the client I get the extent of what is visible for the user and I then pass this as parameter to backend and I removed all holes that are outside the the area that are visible for user.

For single a bb area and the test topo the time was reduced from 80 times ( 9707.204 ms to 117.616 ms)

Here is a complete test case where it goes 70 times faster (from 59620.133 ms to 834.981 ms).
The size of data sent the server to client is reduced from 1127730 to 498381.

select length( query_to_topojson ) from topo_rein.query_to_topojson(
'select distinct a.* from topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl WHERE fa.mbr && ST_GeomFromEWKT(''POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'') AND ((a.geo ).id) = re.topogeo_id AND re.layer_id = tl.layer_id AND tl.schema_name = ''topo_ar5_sysdata_webclient_t2'' AND tl.table_name = ''face_attributes'' and fa.face_id=re.element_id',
25832,
0,
0);
        
length  
---------
 1127730
(1 row)

Time: 59620.133 ms (00:59.620)

And here is the fast one with bounding box as a parameter done in less than a second

select length( query_to_topojson ) from topo_rein.query_to_topojson(
'select distinct a.* from topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl WHERE fa.mbr && ST_GeomFromEWKT(''POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'') AND ((a.geo ).id) = re.topogeo_id AND re.layer_id = tl.layer_id AND tl.schema_name = ''topo_ar5_sysdata_webclient_t2'' AND tl.table_name = ''face_attributes'' and fa.face_id=re.element_id',
25832,
0,
0,
ST_GeomFromEWKT('POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'));

  length 
--------
 498381
(1 row)

Time: 834.981 ms

Could you also consider to include this patch in Postgres also ?

larsop · 2020-11-13T06:33:39Z

   END LOOP; -- }
 SELECT array_agg(x) FROM unnest(bounding_edges) x
 WHERE NOT x = ANY(visited_edges)
 INTO bounding_edges;
 visited_edges := ARRAY[]::int[];
#ifdef POSTGIS_TOPOLOGY_DEBUG
--RAISE DEBUG 'Edges found:%, visited faces: %, ARCS: %' , edges_found, shell_faces, arcs;
#endif

I tested this now and the the case with out bb was reduced from 59620.133 ms to 18128.421 ms.

select length( query_to_topojson ) from topo_rein.query_to_topojson(
'select distinct a.* from topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl WHERE fa.mbr && ST_GeomFromEWKT(''POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'') AND ((a.geo ).id) = re.topogeo_id AND re.layer_id = tl.layer_id AND tl.schema_name = ''topo_ar5_sysdata_webclient_t2'' AND tl.table_name = ''face_attributes'' and fa.face_id=re.element_id',
25832,
0,
0);

length  
---------
 1127730
(1 row)

Time: 18128.421 ms (00:18.128)

The test where use bb as parameter was going faster from 834.981 ms to 606.804 ms

select length( query_to_topojson ) from topo_rein.query_to_topojson(
'select distinct a.* from topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl WHERE fa.mbr && ST_GeomFromEWKT(''POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'') AND ((a.geo ).id) = re.topogeo_id AND re.layer_id = tl.layer_id AND tl.schema_name = ''topo_ar5_sysdata_webclient_t2'' AND tl.table_name = ''face_attributes'' and fa.face_id=re.element_id',
25832,
0,
0,
ST_GeomFromEWKT('POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'));
length 
--------
 498381
(1 row)

Time: 606.804 ms

Thanks, this is great with your patch and the patch from me, the TopoJson seen from client is now almost 100 times faster in our testcase.

The data we test here are original and can not split them in any smaller pieces and I am pretty sure we have polygons more complicated than this.

larsop · 2020-11-13T07:22:48Z

The temporary-table implementation is in this git branch: https://git.osgeo.org/gitea/strk/postgis/src/branch/topojson-temptable

I've yet to verify if ordering differences are significant or not

I also tested this one together with my patch where I use bb.

Here the time is reduced to 7344.997 ms, so this is your best patch so far.

select length( query_to_topojson ) from topo_rein.query_to_topojson(
'select distinct a.* from topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl WHERE fa.mbr && ST_GeomFromEWKT(''POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'') AND ((a.geo ).id) = re.topogeo_id AND re.layer_id = tl.layer_id AND tl.schema_name = ''topo_ar5_sysdata_webclient_t2'' AND tl.table_name = ''face_attributes'' and fa.face_id=re.element_id',
25832,
0,
0);

 length  
---------
 1127730
(1 row)

Time: 7344.997 ms (00:07.345)

With bb we get this time.


select length( query_to_topojson ) from topo_rein.query_to_topojson(
'select distinct a.* from topo_ar5_sysdata_webclient_t2.webclient_flate_v a ,topo_ar5_sysdata_webclient_t2.face fa, topo_ar5_sysdata_webclient_t2.relation re, topology.layer tl WHERE fa.mbr && ST_GeomFromEWKT(''POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'') AND ((a.geo ).id) = re.topogeo_id AND re.layer_id = tl.layer_id AND tl.schema_name = ''topo_ar5_sysdata_webclient_t2'' AND tl.table_name = ''face_attributes'' and fa.face_id=re.element_id',
25832,
0,
0,
ST_GeomFromEWKT('POLYGON ((7.130687233113316 58.631042390424106, 7.130422041758779 58.636004335153906, 7.145740835508981 58.636226053294784, 7.14600385700763 58.63126406559522, 7.130687233113316 58.631042390424106))'));

 length 
--------
 498381
(1 row)

Time: 744.245 ms

This is about 100 ms slower that using your first patch together with bb.

So to get below 1 second it seems like we need to use bounding box patch in some way.

strk · 2020-11-13T11:09:09Z

From ~70seconds to ~7seconds isn't bad, thanks for testing!

As for the bbox patch I find it too wild.
We'd be basically perform a pseudo "intersection" operation, but returning a larger pointset rather than a smaller one (there would be no holes in the returned geometry). What if the frontend caches the TopoGeometry objects to still show them on panning ?

larsop · 2020-11-13T11:28:56Z

It's not a problem because when we do a query based on bb we return return a polygons that intersects this bb today.

If the client moves a meter west it's has to request new data anyway , because there might be a new polygon that now intersects the new bb .

The only case client can do some caching is when zooming in.

Another problem with caching here is that when the user starts to update the database, it's important the data the user work is the data in the database.

larsop · 2020-11-13T11:56:54Z

I was also thinking about adding the bb border between lines that share the same inside face and are crossing the the bb.

Yes this a much more complicated operation and a lot of different cases to think about, so it would probably need a lot more work, but if we got it to work it would be something "like" vector tails in a way.

strk · 2020-11-13T12:31:37Z

I merged my temporary table patch in master branch of PostGIS, so we can talk about further improvements.

strk · 2020-11-13T12:41:10Z

I shall note that your patch will only remove holes composed by a single edge. In the topology I constructed from the dump there are only 135 holes composed by a single edge vs. 1003 holes composed by more than one edge. It might have been an import issue on my side, but worth checking in your real database if this is also the case (ie: how many holes are you dropping?).

It would also be useful to restart the profiling from scratch: is select AsTopoJSON('(4,2,742280,3)'::topogeometry,'topo_rein_topojson_edgemap'); still the bottleneck ? It used to take ~9.7 seconds, how about now ?

larsop · 2020-11-13T13:00:07Z

I shall note that your patch will only remove holes composed by a single edge. In the topology I constructed from the dump there are only 135 holes composed by a single edge vs. 1003 holes composed by more than one edge. It might have been an import issue on my side, but worth checking in your real database if this is also the case (ie: how many holes are you dropping?).

Yes that's correct I was unsure about how to do this, so I just made a simple test that cleaned out the the simple holes.

So we should probably make this test to include more than simple holes.

It would also be useful to restart the profiling from scratch: is select AsTopoJSON('(4,2,742280,3)'::topogeometry,'topo_rein_topojson_edgemap'); still the bottleneck ? It used to take ~9.7 seconds, how about now ?

Great work, this is also very fast now down to 1/4 of second.


vroom2.ad.skogoglandskap.no postgres@ar5web=# select length(ST_AsGeoJSON(('(4,2,742280,3)'::topogeometry)::geometry));
 length 
--------
 876996
(1 row)

Time: 223.660 ms

larsop · 2021-01-14T10:55:36Z

I set this as closed/solved and I create a new task to reduce topojson size based on input bounding box

larsop assigned strk Nov 11, 2020

larsop assigned larsop and unassigned strk Jan 14, 2021

larsop closed this as completed Jan 14, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Slow AsTopoJSON when running on big tables #13

Slow AsTopoJSON when running on big tables #13

larsop commented Nov 10, 2020 •

edited

larsop commented Nov 10, 2020

larsop commented Nov 11, 2020 •

edited

larsop commented Nov 12, 2020

strk commented Nov 12, 2020

larsop commented Nov 12, 2020

larsop commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

larsop commented Nov 13, 2020

larsop commented Nov 13, 2020

larsop commented Nov 13, 2020

strk commented Nov 13, 2020

larsop commented Nov 13, 2020

larsop commented Nov 13, 2020

strk commented Nov 13, 2020

strk commented Nov 13, 2020

larsop commented Nov 13, 2020

larsop commented Jan 14, 2021

Slow AsTopoJSON when running on big tables #13

Slow AsTopoJSON when running on big tables #13

Comments

larsop commented Nov 10, 2020 • edited

larsop commented Nov 10, 2020

larsop commented Nov 11, 2020 • edited

larsop commented Nov 12, 2020

strk commented Nov 12, 2020

larsop commented Nov 12, 2020

larsop commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

strk commented Nov 12, 2020

larsop commented Nov 13, 2020

larsop commented Nov 13, 2020

larsop commented Nov 13, 2020

strk commented Nov 13, 2020

larsop commented Nov 13, 2020

larsop commented Nov 13, 2020

strk commented Nov 13, 2020

strk commented Nov 13, 2020

larsop commented Nov 13, 2020

larsop commented Jan 14, 2021

larsop commented Nov 10, 2020 •

edited

larsop commented Nov 11, 2020 •

edited