[postgis-users] test / live environment, major performance difference

[Christo Du Preez]

Hi All,

I really hope someone can shed some light on my problem. I'm not sure if
this is a posgres or potgis issue.

Anyway, we have 2 development laptops and one live server, somehow I
managed to get the same query to perform very well om my laptop, but on
both the server and the other laptop it's really performing bad.

All three environments are running the same versions of everything, the
two laptops are identical and the server is a monster compared to the
laptops.

I have narrowed down the problem (I think) and it's the query planner
using different plans and I haven't got a clue why. Can anyone please
shed some light on this?

EXPLAIN ANALYZE
SELECT l.*
FROM layer l, theme t, visiblelayer v, layertype lt, style s
WHERE l.the_geom && geomfromtext('POLYGON((-83.0 -90.0, -83.0 90.0, 97.0
90.0, 97.0 -90.0, -83.0 -90.0))')
AND t.name = 'default'
AND v.themeid = t.id
AND v.zoomlevel = 1
AND v.enabled
AND l.layertypeid = v.layertypeid
AND lt.id = l.layertypeid
AND s.id = v.styleid
ORDER BY lt.zorder ASC

----------------------------------

 Sort  (cost=181399.77..182144.30 rows=297812 width=370) (actual
time=1384.976..1385.072 rows=180 loops=1)
   Sort Key: lt.zorder
   ->  Hash Join  (cost=31.51..52528.64 rows=297812 width=370) (actual
time=398.656..1384.574 rows=180 loops=1)
         Hash Cond: (l.layertypeid = v.layertypeid)
         ->  Seq Scan on layer l  (cost=0.00..43323.41 rows=550720
width=366) (actual time=0.016..1089.049 rows=540490 loops=1)
               Filter: (the_geom &&
'010300000001000000050000000000000000C054C000000000008056C00000000000C054C0000000000080564000000000004058400000000000805640000000000040584000000000008056C00000000000C054C000000000008056C0'::geometry)
         ->  Hash  (cost=31.42..31.42 rows=7 width=12) (actual
time=1.041..1.041 rows=3 loops=1)
               ->  Hash Join  (cost=3.90..31.42 rows=7 width=12) (actual
time=0.107..1.036 rows=3 loops=1)
                     Hash Cond: (v.styleid = s.id)
                     ->  Nested Loop  (cost=2.74..30.17 rows=7 width=16)
(actual time=0.080..1.002 rows=3 loops=1)
                           Join Filter: (v.themeid = t.id)
                           ->  Seq Scan on theme t  (cost=0.00..1.01
rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
                                 Filter: (name = 'default'::text)
                           ->  Hash Join  (cost=2.74..29.07 rows=7
width=20) (actual time=0.071..0.988 rows=3 loops=1)
                                 Hash Cond: (lt.id = v.layertypeid)
                                 ->  Seq Scan on layertype lt
(cost=0.00..18.71 rows=671 width=8) (actual time=0.007..0.473 rows=671
loops=1)
                                 ->  Hash  (cost=2.65..2.65 rows=7
width=12) (actual time=0.053..0.053 rows=3 loops=1)
                                       ->  Seq Scan on visiblelayer v
(cost=0.00..2.65 rows=7 width=12) (actual time=0.022..0.047 rows=3 loops=1)
                                             Filter: ((zoomlevel = 1)
AND enabled)
                     ->  Hash  (cost=1.07..1.07 rows=7 width=4) (actual
time=0.020..0.020 rows=7 loops=1)
                           ->  Seq Scan on style s  (cost=0.00..1.07
rows=7 width=4) (actual time=0.005..0.012 rows=7 loops=1)
 Total runtime: 1385.313 ms

----------------------------------

 Sort  (cost=37993.10..37994.11 rows=403 width=266) (actual
time=32.053..32.451 rows=180 loops=1)
   Sort Key: lt.zorder
   ->  Nested Loop  (cost=0.00..37975.66 rows=403 width=266) (actual
time=0.130..31.254 rows=180 loops=1)
         ->  Nested Loop  (cost=0.00..30.28 rows=1 width=12) (actual
time=0.105..0.873 rows=3 loops=1)
               ->  Nested Loop  (cost=0.00..23.14 rows=1 width=4)
(actual time=0.086..0.794 rows=3 loops=1)
                     ->  Nested Loop  (cost=0.00..11.14 rows=2 width=8)
(actual time=0.067..0.718 rows=3 loops=1)
                           Join Filter: (s.id = v.styleid)
                           ->  Seq Scan on style s  (cost=0.00..2.02
rows=2 width=4) (actual time=0.018..0.048 rows=7 loops=1)
                           ->  Seq Scan on visiblelayer v
(cost=0.00..4.47 rows=7 width=12) (actual time=0.031..0.079 rows=3 loops=7)
                                 Filter: ((zoomlevel = 1) AND enabled)
                     ->  Index Scan using theme_id_pkey on theme t
(cost=0.00..5.98 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=3)
                           Index Cond: (v.themeid = t.id)
                           Filter: (name = 'default'::text)
               ->  Index Scan using layertype_id_pkey on layertype lt
(cost=0.00..7.12 rows=1 width=8) (actual time=0.010..0.014 rows=1 loops=3)
                     Index Cond: (lt.id = v.layertypeid)
         ->  Index Scan using fki_layer_layertypeid on layer l
(cost=0.00..36843.10 rows=88183 width=262) (actual time=0.031..9.825
rows=60 loops=3)
               Index Cond: (l.layertypeid = v.layertypeid)
               Filter: (the_geom &&
'010300000001000000050000000000000000C054C000000000008056C00000000000C054C0000000000080564000000000004058400000000000805640000000000040584000000000008056C00000000000C054C000000000008056C0'::geometry)
 Total runtime: 33.107 ms

----------------------------------

Thanx in advance.
Christo Du Preez