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

[Sandro Santilli]

On Wed, Jul 30, 2014 at 12:36:17PM +0200, Rémi Cura wrote:
> 2014-07-30 12:24 GMT+02:00 Sandro Santilli <strk at keybit.net>:

> >       funcname         | calls | total_time | self_time
> > -----------------------+-------+------------+-----------
> >  topogeo_addpolygon    |  5000 |  55761.337 |  1369.537
> >  topogeo_addlinestring |  5004 |  48302.105 |  4038.88
> >  st_addedgemodface     |  5004 |   33837.97 | 10328.094
> >  _st_addfacesplit      | 10008 |  22461.719 | 17372.12
> >  topogeo_addpoint      | 10008 |   9712.522 |  3570.827
> >  st_addisonode         |  5004 |   6032.572 |  4861.636
> >  getringedges          | 10008 |   4876.065 |  4876.065
> >  st_getfacegeometry    | 11921 |   4674.631 |  3712.201
> >  st_pointonsurface     |  9591 |   1267.424 |  1267.424
> >  st_buildarea          | 11921 |     962.43 |   962.43
> >
> > You'll note that _ST_AddFaceSplit is called 10008 times, and at every
> > call the number of points should be at least 1 more than the previous,
> > with last call finding ~5000 nodes in the table.
> >
> > For the record, my current timing for loading those 5000 rows
> > is 55822.181 ms whereas my last best was 36134.786 ms 
>
> is it the timing with
> CREATE INDEX ON node (containing_face); ?

Without index.

With the index it takes 60325.320 ms here (more!).
Top 10 functions in that case:

       funcname        | calls | total_time | self_time
-----------------------+-------+------------+-----------
 topogeo_addpolygon    |  5000 |  60375.332 |  1384.518
 topogeo_addlinestring |  5004 |  52830.637 |  4116.069
 st_addedgemodface     |  5004 |  38204.475 | 10552.634
 _st_addfacesplit      | 10008 |   26599.97 | 21492.476
 topogeo_addpoint      | 10008 |   9794.984 |  3626.734
 st_addisonode         |  5004 |   6059.486 |  4889.334
 getringedges          | 10008 |   4890.839 |  4890.839
 st_getfacegeometry    | 11921 |   4722.874 |  3735.434
 st_pointonsurface     |  9591 |    1277.04 |   1277.04
 st_buildarea          | 11921 |     987.44 |    987.44

Can't spend more time on this today, sorry.

--strk;