[postgis-users] intersects (topogeom, topogeom) is too slow
Jose Carlos Martinez Llario
jomarlla at cgf.upv.es
Tue Apr 17 14:45:02 PDT 2012
On 17/04/2012 23:36, Andy Colson wrote:
> On 4/17/2012 4:00 PM, Jose Carlos Martinez Llario wrote:
>> 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
>> This query takes 25 secs.
>> select count(*) from suelos s1, suelos s2 where st_intersects (s1.geom,
>> I was expecting this query to take 1 or less secs:
>> select count(*) from suelos s1, suelos s2 where intersects (s1.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
>> 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,
> 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.
More information about the postgis-users