[postgis-users] Another Question on PostGIS Performance

[Thomas Haechler]

> 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         

Btw. thank you also for the message concerning spatial index use
optimization, this was a very good explanation of indexes / query
planning. Next thing I am going to do tomorrow morning is following
Jan's advise on data cleaning.

Regards,

Thomas


-- 

THOMAS HAECHLER

Geographic Information Systems Division
Institute of Geography
University of Zurich
Switzerland

thomas.haechler at geo.unizh.ch