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

[Jose Carlos Martinez Llario]

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