[postgis-users] query optimization

[Dylan Keon]

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)