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

[Jose Carlos Martinez]

Hi Sandro, thanks for the answer

On 18/04/2012 10:35, Sandro Santilli wrote:
> 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.
ok, I understand. Anyways, the code is interesting for learning.
>
>>   - 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.
You re talking about a functional index?
s1=# create index test on suelos using gist (st_envelope(topogeom));
ERROR:  functions in index expression must be marked IMMUTABLE

Even St_envelope is immutable  I think its not working because  
converting from topogeom to geometry needs reading for other tables so 
it can not be immutable


>
> 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).
I will keep trying to study and to help you if I can. I think topology 
is a very attractive field for teaching and researching.