[postgis-users] test / live environment, major performance difference
Christo Du Preez
christo at mecola.com
Mon Jun 11 23:37:53 PDT 2007
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
root@???.???.???.???:/home/postgres/layer.gz
--
/usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz
rsync --progress --rsh=ssh visiblelayer.gz
root@???.???.???.???:/home/postgres/visiblelayer.gz
--
/usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz
rsync --progress --rsh=ssh style.gz
root@???.???.???.???:/home/postgres/style.gz
--
/usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz
rsync --progress --rsh=ssh layertype.gz
root@???.???.???.???:/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: 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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
More information about the postgis-users
mailing list