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

[Jose Carlos Martinez Llario]

On 17/04/2012 23:36, Andy Colson wrote:
> On 4/17/2012 4:00 PM, 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!! :)
>>
>> 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 have a table called suelos (10000 polygons) with a normal geometry
>> column (geom) and a topology column (topogeom) where I populated the
>> geometries.
>>
>> This query takes 25 secs.
>> select count(*) from suelos s1, suelos s2 where st_intersects (s1.geom,
>> s2.geom);
>>
>> I was expecting this query to take 1 or less secs:
>> select count(*) from suelos s1, suelos s2 where intersects (s1.topogeom,
>> s2.topogeom);
>>
>> but after taking more than 10 mins I cancelled it.
>>
>> Then I noticed that the topogeom column is not using any spatial index
>> then I added a column:
>>
>> s1=# alter table suelos add column box geometry (polygon, 23030);
>> s1=# update suelos set box = st_envelope (topogeom::geometry);
>> s1=# create index gist_suelos_topogeom using gist (box);
>>
>> and the following sentence took 60 secs (still much more that I was
>> expecting)
>> select count(*) from suelos s1, suelos s2 where s1.box && s2.box and
>> intersects (s1.topogeom, s2.topogeom);
>>
>>
>> Then my questions?
>>
>> - 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?
>> - The topogeom composite type is using indexes with its fields?
>> - Why its taking more time that geometry types if it should have
>> opposite behavior?
>>
>> Regards and thanks,
>> Jose
>>
>>
>>
>
>
> Have you tried something like:
>
> select count(*) from suelos s1, suelos s2
> where intersects (s1.topogeom,  st_envelope(s2.topogeom));
>

>
> > - How can we use spatial index with topogeometry layers, making a new
> > box column and keep it synchronized?
Yes, but I wanted to ask if we are forced to implement this kind of 
synchronization (for example with triggers) or what about the 
possibility to include a box in the topogeom composite type.
>
>
> You can put an update trigger on the table to reset the box.
>
>
> -Andy