I wonder if my dump/restore routine isn't causing this issue. Seeing that I do the db development on my laptop (the fast one) and then restores it on the other two machines. I have confirmed if all the indexes are present after a restore.
This is the routine: /usr/local/pgsql/bin/pg_dump -t layer mapdb | gzip > layer.gz rsync --progress --rsh=ssh layer.gz [EMAIL PROTECTED]:/home/postgres/layer.gz -- /usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz rsync --progress --rsh=ssh visiblelayer.gz [EMAIL PROTECTED]:/home/postgres/visiblelayer.gz -- /usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz rsync --progress --rsh=ssh style.gz [EMAIL PROTECTED]:/home/postgres/style.gz -- /usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz rsync --progress --rsh=ssh layertype.gz [EMAIL PROTECTED]:/home/postgres/layertype.gz -- DROP TABLE visiblelayer; DROP TABLE style; DROP TABLE layer; DROP TABLE layertype; gunzip -c layertype.gz | /usr/local/pgsql/bin/psql mapdb gunzip -c style.gz | /usr/local/pgsql/bin/psql mapdb gunzip -c visiblelayer.gz | /usr/local/pgsql/bin/psql mapdb gunzip -c layer.gz | /usr/local/pgsql/bin/psql mapdb /usr/local/pgsql/bin/vacuumdb -d mapdb -z -v Obe, Regina wrote: > 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 > > > -- Christo Du Preez Senior Software Engineer Mecola IT Mobile: +27 [0]83 326 8087 Skype: christodupreez Website: http://www.locateandtrade.co.za _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
