[postgis-users] spatial query performance

[Vojtech Honzik]

I have two tables - geologie (type polygon - 138 features ) and toky (type
polyline - 270 features). I would like to get the result of intersection of
both layers and think I must have some formal error in my query. Although I
created GIST spatial indexes for both layers, it seems they are not used in
this query even the similar where condition is used in manual. Can anyone
give me a hint what am I doing wrong? I am begginner both in RDBMS and
PosGIS. I am using  PostgreSQL 7.3.4 and PostGIS 0.8.1. My machine is AMD
Duron @ 1200 MHz with 256 MB RAM so I think the prefrormance on such small
tables should be much better than is.

Thank you.

Vojtech Honzik

----------------------------------------------------------------------------
--------------------------------------------

barbora=> explain analyse SELECT distinct geologie.gid as g FROM
geologie,toky where
barbora-> (geologie.the_geom &&
'BOX3D(-785000 -978000, -777500 -972000)'::box3d)
barbora-> AND
barbora-> (toky.the_geom &&
'BOX3D(-785000 -978000, -777500 -972000)'::box3d)
barbora-> AND
barbora->  intersects (toky.the_geom,geologie.the_geom);
                                                       QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------
 Unique  (cost=3943.71..3997.02 rows=1066 width=68) (actual
time=16657.68..16658.11 rows=63 loops=1)
   ->  Sort  (cost=3943.71..3970.37 rows=10663 width=68) (actual
time=16657.68..16657.85 rows=434 loops=1)
         Sort Key: geologie.gid
         ->  Nested Loop  (cost=0.00..3230.34 rows=10663 width=68) (actual
time=6.62..16654.29 rows=434 loops=1)
               Join Filter: intersects("inner".the_geom, "outer".the_geom)
               ->  Seq Scan on geologie  (cost=0.00..18.73 rows=138
width=36) (actual time=0.45..6.92 rows=138 loops=1)
                     Filter: (the_geom && 'SRID=-1;BOX3D(-785000 -978000
0,-777500 -972000 0)'::geometry)
               ->  Seq Scan on toky  (cost=0.00..20.38 rows=232 width=32)
(actual time=0.01..3.21 rows=270 loops=138)
                     Filter: (the_geom && 'SRID=-1;BOX3D(-785000 -978000
0,-777500 -972000 0)'::geometry)
 Total runtime: 16658.32 msec
(10 rádek)
----------------------------------------------------------------------------
--------------------------------------------