[postgis-users] spatial query performance
Vojtech Honzik
v.honzik at sh.cvut.cz
Wed Mar 3 06:06:36 PST 2004
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)
----------------------------------------------------------------------------
--------------------------------------------
More information about the postgis-users
mailing list