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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [email protected] 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
