[postgis-users] postgis topology performance

[Sandro Santilli]

On Thu, Mar 20, 2014 at 06:46:51PM +0100, Rémi Cura wrote:
> Yep,
> We tried a modified version of AddNode  without check (because I already
> delete duplicates nodes before calling the function), it was not incredibly
> faster, and anyway way faster than line insertion (where I feel we need to
> focus now).

Show numbers, c'mon!  :)

> I may try to simplify the add edge function, but I don't think this is the
> way to go.

For sure the AddEdge funtion makes a lot more checks.
I was actually thinking to split that myself between a checking and
non-checking version.
Also the checks themselves might have space for optimizations.

> It would be better to do batch direct insertion in table
> (to be precise : insert all edges into edge_data for the columns edge_id,
> start_node, end_node, geom )
> , then batch edge_data completion (next_left_edge, next_right_edge, found
> with querry on node_id),
> then batch compute face (with polygonize) .

I agree this would be also an interesting way to proceed.
Still I don't think polygonize would be that fast (not right now).

> This way there would be no edge by edge insertion.

But there'll still be edge-by-edge updates, which would be still
expensive (every update is like a new insert).

> About your question :
> only filling the edge_data with geometry (on next_left etc) is about 4.5
> sec (on par with inserting all the points) for 10k lines. ON this 4.5 sec
> you can remove about 3 sec related to my data selection process (I only
> insert a part of a table).
> This way, I would say that taking 1.5 sec to insert 10 k lines+ check
> constraints and update index is OK for me.

So it makes 1.5 seconds for 10k edges + 4.5 seconds for their unique points
(how many ?)

> Still a long way to go from 80 sec to few sec (10 sec would be cool, if it
> scales well).

Well, if you could compute all the linking in memory it'd be about those
inserts only (< 10 secs for the inserts). Time to look at GEOS GeomGraph
capability of representing all we need ?

--strk;