[postgis-users] test / live environment, major performance difference
Obe, Regina
robe.dnd at cityofboston.gov
Mon Jun 11 08:46:21 PDT 2007
Your first analyze doesn't look like its using any indexes at all where
as the second looks like its using 3.
My guess would be you forgot to set indexes on your server tables and
laptop or maybe during data load process for some reason it choked when
creating the indexes on the tables. Just a guess.
Hope that helps,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Christo Du Preez
Sent: Monday, June 11, 2007 11:11 AM
To: PostGIS Users Discussion
Subject: [postgis-users] test / live environment,major performance
difference
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 &&
'010300000001000000050000000000000000C054C000000000008056C00000000000C05
4C0000000000080564000000000004058400000000000805640000000000040584000000
000008056C00000000000C054C000000000008056C0'::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 &&
'010300000001000000050000000000000000C054C000000000008056C00000000000C05
4C0000000000080564000000000004058400000000000805640000000000040584000000
000008056C00000000000C054C000000000008056C0'::geometry)
Total runtime: 33.107 ms
----------------------------------
Thanx in advance.
Christo Du Preez
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
More information about the postgis-users
mailing list