[postgis-users] Another Question on PostGIS Performance

[Thomas Haechler]

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