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

[Andy Colson]

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