[postgis-users] Another Question on PostGIS Performance

[David Blasby]

Thomas Haechler wrote:

> > EXPLAIN ANALYZE SELECT
> > asbinary(force_collection(force_2d(the_geom)),'XDR'),gid::text from (
> > SELECT map.the_geom, data.hs_zoller, map.gid from veg as map, veg_atr as
> > data where map.pg = data.pg )as foo ;
>
> I got the following results:
>
> NOTICE:  QUERY PLAN:
>
> Hash Join  (cost=1.50..931.75 rows=6591 width=44) (actual
> time=7.40..2542.82 rows=6591 loops=1)
>   ->  Seq Scan on veg map  (cost=0.00..814.91 rows=6591 width=40)
> (actual time=0.12..301.26 rows=6591 loops=1)
>   ->  Hash  (cost=1.40..1.40 rows=40 width=4) (actual time=1.22..1.22
> rows=0 loops=1)
>         ->  Seq Scan on veg_atr data  (cost=0.00..1.40 rows=40 width=4)
> (actual time=0.06..0.77 rows=40 loops=1)
> Total runtime: 2563.83 msec

Well - this has got the query down to 2.5 seconds!

I'm surprised that the send-to-mapserver and draw process takes so long.  Is
the data stored locally?  Is the database on a different machine than
mapserver?  Is it using too much memory?

dave