Opened 9 years ago

Last modified 8 weeks ago

#2993 new enhancement

Reduce updates on topology building

Reported by: strk Owned by: strk
Priority: medium Milestone: PostGIS Fund Me
Component: topology Version: master
Keywords: performance Cc:

Description

Measuring operations of TopoGeo_addPolygon I saw that each call results in one INSERT and 2 UPDATEs for each node (1 node per polygon) and an INSERT and an UPDATE for each edge (1 edge in absence of intersection or more).

This ticket is to study the problem and search for a reduction of UPDATES, which in the case of edges are also never HOT, thus resulting in edge indexes having twice as many rows as the actual source table.

Change History (4)

comment:1 by strk, 9 years ago

To check for the number of operations performed on the edge_table after converting simple geometries, here's a useful query:

select * from pg_stat_all_tables where relname = 'edge_data' and schemaname = :toponame;

From #2998 I see that converting 99 simple geometries can result in 175 inserts and 365 updates on the table (two updates for each insert + a spare 15 more). Additionally, 417 sequencial scans and 3428 index scans are performed (ugh).

comment:2 by strk, 9 years ago

Keywords: performance added

comment:3 by robe, 7 years ago

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

comment:4 by strk, 8 weeks ago

Another source of excessive updates is the policy of assigning a new identifier to the face on the _left_ of the edge that happen to split an existing face. When the left side of the new ring is the _external_ part of it, the number of edges to be updated is more likely to be bigger than the number of updates (although not necessarely so).

Note: See TracTickets for help on using tickets.