[postgis-users] Another Question on PostGIS Performance

[Jan Hartmann]

Thomas,

It has nothing to do with indexes or joins. On rereading the mail you 
sent me personally I saw that even a simple DATA statement

DATA "the_geom from veg"

took more than 10 seconds. This certainly is far too slow for the number 
of lines you mentioned and could be a problem of the underlying map 
(digitizing errors?). To test this, you could try to use the original 
shapefile with MapServer. If this takes about as much time, you probably 
  have to clean up your data. If not, perhaps the people from 
Refractions would be interested in comparing this shapefile with the 
much slower PostGIS version.

Regards,

Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl

Thomas Haechler wrote:
> Graeme Leeming wrote:
> 
>>Thomas,
>>
>>Based on the table info you have provided, it appears that you have *not*
>>created an index on pg in the veg table.  You'll need to add a third index
>>to it of the form "CREATE INDEX veg_pg_index ON veg ( pg );" as done to the
>>veg_atr table.  After another VACUUM ANALYZE see if that makes any
>>difference...
>>
> 
> 
> Unfortunately, this didn't bring a significant change. According to
> EXPLAIN, this index doesn't seem to be used anyway:
> 
> NOTICE:  QUERY PLAN:
> 
> Nested Loop  (cost=0.00..7.91 rows=1 width=44) (actual
> time=9.27..12753.65 rows=6591 loops=1)
>   ->  Index Scan using veg_gist_index on veg map  (cost=0.00..6.01
> rows=1 width=40) (actual time=3.74..1725.93 rows=6591 loops=1)
>   ->  Seq Scan on veg_atr data  (cost=0.00..1.40 rows=40 width=4)
> (actual time=0.03..0.89 rows=40 loops=6591)
> Total runtime: 12775.59 msec
> 
> After setting enable_seqscan=off in the conf file and restarting pgsql:
> 
> Nested Loop  (cost=0.00..9.04 rows=1 width=44) (actual
> time=8.32..4262.83 rows=6591 loops=1)
>   ->  Index Scan using veg_gist_index on veg map  (cost=0.00..6.01
> rows=1 width=40) (actual time=3.73..1769.57 rows=6591 loops=1)
>   ->  Index Scan using veg_atr_pg_index on veg_atr data 
> (cost=0.00..3.01 rows=1 width=4) (actual time=0.08..0.10 rows=1
> loops=6591)
> Total runtime: 4285.89 msec
>   
> But in the application it still takes the same time, so I think maybe I
> should try to reduce the amount of features to render by using multiple
> layers of different scale as Paul suggested.
> 
> 
> Regards,
> 
> Thomas
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>