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

[Rémi Cura]

OK,
I ended looking for every instance of "containing_face" in the topology.sql
file in postgres extension

Note that i found
"  -- TODO: consider also adding an index on node.containing_face "

The index would be used in
_the "intersects" function (case of point/polygon) "'WHERE  ... AND
r2.element_id = n.containing_face" '"
_the addface function ''WHERE containing_face IS NOT NULL"
_ the ST_RemEdgeNewFace function "    -- Update containing_face for all
nodes still referencing old faces"
_the ST_RemEdgeModFace function "    -- Update containing_face for all
nodes still referencing old faces"
_ the _ST_AddFaceSplit function "  -- Update isolated nodes in new new face
"


I think there is significant evidence that such an index will be used.
My usage of topology is very limited (I only use edge topo) and I don't
have the benchmark to prove that such index would speed the operations.
If somebody uses face/node, it should be simple to benchmark : drop
topology, create topology, create index, import data in topology .
Then a simple look on index stats should tell if it was used ans how much.

However :
_the above function will obviously be speeded.
_index are mandatory on primary key (postgres default), this should be the
same for "simple" foreign key
_ the basic btree index is very low cost (time & memory)

About putting/removing indexes and constraint,
I think your right about trigger/constraint in one case : a batch topology
import.
I fear the changes to do so efficiently would be important (more or less
rewrite every function the batch way, and of course disable all
triggers/check) .

I feel disabling index, filling table, then computing indexes is the kind
of optimization that we are far far from needing. Maybe when data import
would be in the speed range of grass data import, thats a 100 factor to
gain before such optimization.

Cheers,
Rémi-C


2014-07-29 16:28 GMT+02:00 Sandro Santilli <strk at keybit.net>:

> On Tue, Jul 29, 2014 at 01:23:31PM +0200, Rémi Cura wrote:
> > Hey,
> > it seems there is no index on
> > node.containing_face in a classical topology schema.
> >
> > Is there a special reason to do so ?
> > On my sample data (about 25k node, 30 k edge), using such an index
> (`CREATE
> > INDEX ON bdtopo_topological.node (containing_face)`) make looking for a
> > containing_face going from 15ms to 0.5 ms (30 times faster).
>
> There's no strict policy about what indexes are created by CreateTopology.
> I think I've recently added creation of some indexes when they've been
> found useful for topology construction.
>
> I guess a rule of thumb could be to check if any core function would
> benefit
> from having an index. Not sure if any existing function queries points
> within
> a face. Can you check ?
>
> Can you also check the effect of having indexes defined on topology
> construction performance ? We should maybe have management functions
> to add/remove indexes/constraints based on topology state (construction,
> usage).
>
> --strk;
>
>  ()  ASCII ribbon campaign  --  Keep it simple !
>  /\  http://strk.keybit.net/rants/ascii_mails.txt
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20140730/d4c96181/attachment.html>