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

Sandro Santilli strk at keybit.net
Tue Jul 29 07:28:28 PDT 2014

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,


 ()  ASCII ribbon campaign  --  Keep it simple !
 /\  http://strk.keybit.net/rants/ascii_mails.txt  

More information about the postgis-devel mailing list