[postgis-users] query optimization
Dylan Keon
keon at nacse.org
Tue Dec 14 15:21:07 PST 2004
Hi list,
I'm trying to optimize queries on a PostGIS-enabled database. PostGIS
0.8.2 and PostgreSQL 7.4.5 are on a Dell 2650 dual 2.4GHz Xeon with 2GB
RAM, running RHEL.
Most of the tables storing geometries contain less than 1 million rows,
and queries on those tables are quite fast. But one table contains over
13 million rows, and queries take longer than I would like. I've run
SELECT update_geometry_stats() and VACUUM ANALYZE, and built a GiST
index on the geometry column. I have not built any other indexes since
this is a simple table that will mostly be subjected to bounding box
queries.
Any suggestions on how I can further optimize? Or do I have it as good
as it's gonna get? The first query below is typical of most of the
queries that will be done.
Thanks!
--Dylan
tsunami-# explain analyze
tsunami-# select depth from owase_50m where geom && 'BOX3D(136.2
33.8,136.8 34.2)'::box3d;
Seq Scan on owase_50m (cost=0.00..478944.89 rows=983445 width=4)
(actual time=11813.053..56433.021 rows=983496 loops=1)
Filter: (geom && 'SRID=-1;BOX3D(136.2 33.8 0,136.8 34.2 0)'::geometry)
Total runtime: 57148.437 ms
tsunami=# explain analyze
tsunami-# select min(X(geom)) as minx, min(Y(geom)) as miny,
max(X(geom)) as maxx, max(Y(geom)) as maxy from owase_50m;
Aggregate (cost=579406.43..579406.44 rows=1 width=140) (actual
time=107140.546..107140.546 rows=1 loops=1)
-> Seq Scan on owase_50m (cost=0.00..445457.71 rows=13394871
width=140) (actual time=4.876..62661.358 rows=13394871 loops=1)
Total runtime: 107178.251 ms
tsunami=# \d owase_50m
Column | Type | Modifiers
--------+----------+-----------
id | integer |
x | integer |
y | integer |
depth | integer |
geom | geometry |
Indexes:
"owase_50m_index" gist (geom)
Check constraints:
"$2" CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL)
"$1" CHECK (srid(geom) = -1)
More information about the postgis-users
mailing list