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

[Mark Cave-Ayland]

On Tue, 2007-06-12 at 08:37 +0200, Christo Du Preez wrote:
> 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


Hi Christo,

Are you sure that your postgresql.conf files are identical for both
installations? (you also haven't mentioned which versions of
PostGIS/PostgreSQL you are using). Looking quickly at the plans, on your
development (fast) server, the planner estimates that an index scan
joining l.layertypeid onto v.layertypeid should return 88,000 rows when
in fact it returns only 60. How many different layertypeids do you have?


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk