[postgis-users] intersects (topogeom, topogeom) is too slow

Sandro Santilli strk at keybit.net
Wed Apr 18 01:35:29 PDT 2012

On Tue, Apr 17, 2012 at 11:00:47PM +0200, Jose Carlos Martinez Llario wrote:
> Hi,
> first I wanted to say thanks because Im asking a lot of questions
> lately and you guys always answer me in a fast and kind way. Sandro
> you specially must be tired of me. sorry!! :)

Not at all. I'm happy that my creation is having a social life ;)

> now another more question.
> I found the function intersects (topogeom, topogeom) quite
> interesting even it is not documented, because I was expecting it to
> show me the persistent topology performance.

I noticed that function too, but it's really something that has _not_
been reviewed in this last year of improvements.
So something from 2005. Not regress-tested. Not reviewed for performance.

So please don't take it as a proof of what performance you may get,
but as an experiment.

>  - How can we use spatial index with topogeometry layers, making a
> new box column and keep it synchronized? Why topogeom composite type
> does not include a box that we can index?

This is a good question. It's to be researched upon.
I've asked Regina some time ago to play a bit with an SQL implementation
of && operator. Theoretically an SQL implementation could be inlined
and as such could be using indices on the primitive tables.

Maintaining an index on TopoGeometry objects themeselves would
not easily be done automatically because the index would need to
be updated every time the primitives change. You can generally think
of it as cache maintainance. Which in turn raises the question of
how big should the link be between knwon TopoGeometry objects (those
found in the topo.relation table) and deployed ones (those actually
found in the tables reported as being the deploy tables for the given
TopoGeometry layers).

Lots of interesting tasks on the road ahead :)

> - The topogeom composite type is using indexes with its fields?

I don't understand this question, but the answer sounds like being NO.

> - Why its taking more time that geometry types if it should have
> opposite behavior?

I/O costs ? Prepared geometries ? Go figure...
For sure there are more queries involved for each pair of geometries
being compared. I bet your use case might be implemented using a
completely different path to optimize those queries (something that
an ST_Intersects override couldn't possibly do, not having visibility
of the whole input you're willing to consider).


  |   __/  |    Delivering high quality PostGIS 2.0 !
  |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com

More information about the postgis-users mailing list