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

[Andy Colson]

On 4/17/2012 4:36 PM, 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?
>
>
> You can put an update trigger on the table to reset the box.
>
>
> -Andy




 > select count(*) from suelos s1, suelos s2
 > where intersects (s1.topogeom, st_envelope(s2.topogeom));


Oops.  Forget this part, it makes no sense at all, sorry.



 >> 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);


You are comparing every row of one table to every row of the other. 
That is probably not a real world query, is it?  Wont you have some 
other conditions you can put on?  A bounding box to draw?  A click 
point?  How real world is this query?

-Andy