[postgis-users] query optimization

[Paul Ramsey]

Dylan,

Well, you could write query two using the extent() function instead, 
which would prevent you from doing four accessors on the geometry for 
each row. But that will probably not save all that much time.

In query one, the estimator thinks you will get 983445 rows and you 
actually got 983496. That's a great estimate! And because you are 
pulling so much of the table (almost a million rows) it sequence scans 
it. You pull that much data, it will take time to get it.

In query two, you literally are sequence scanning the whole table. You 
have to traverse 13M rows, that takes time.

At this point you have run out of software fixes, your only recourse is 
to better hardware. Faster disk access will speed up your queries. Put 
your database on a multi-spindle RAID array (RAID 10 or RAID 5, with as 
many drives as possible, spinning as fast as possible). You are scanning 
the whole table, so you are going to be disk bound.

Paul

Dylan Keon wrote:

> 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)
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users