[postgis-devel] topology : new index on node table

[Sandro Santilli]

On Wed, Jul 30, 2014 at 11:29:00AM +0200, Sandro Santilli wrote:

> There's a reference dataset produced some time ago by a user who resulted
> in a fix making topology creation much faster:
> http://lists.osgeo.org/pipermail/postgis-devel/2014-January/024078.html
> 
> I still have a couple of diagrams showing population time before/after
> that are waiting for a blog post that I never find the time to write...

Just a sneak preview of the blog post that might never be written

Queries:

 SELECT ST_CreateTopoGeo('million_poly_topo1', ST_Collect(geom))
 FROM ( SELECT geom from million_poly_topo1 order by gid limit 160000) as f;

 SELECT TopoGeo_addPolygon('million_poly_topo1', ST_GeometryN(geom,1))
 FROM ( SELECT geom from million_poly_topo1 order by gid limit 160000) as f;

Output topology has:

 160167 nodes, 160167 edges, 160167 faces

Times:

 a: before starting
 b: Ensure face splitting algorithm uses the edge index (#2610)
 c: Drop all calls to geometry::text during topology population (#2616)

      ST_CreateTopoGeo     TopoGeo_addPolygon
    +-------------------+--------------------+
 a  |    38352283.535   |    38375126.950    |
 b  |    12088554.776   |     9644736.277    |
 c  |    11963757.923   |     4984225.402    |

I've a table with more numbers, with limits from 5000 to 160000.
The index change would make another configuration (d, I guess).

Please please please file the ticket, maybe referencing this thread
and #2610 and #2616 as I'd really love to grow the optimizations further

--strk;