Re: test / live environment, major performance difference

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(at)???(dot)???(dot)???(dot)???:/home/postgres/layer.gz

--

/usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz

rsync --progress --rsh=ssh visiblelayer.gz
root(at)???(dot)???(dot)???(dot)???:/home/postgres/visiblelayer.gz

--

/usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz

rsync --progress --rsh=ssh style.gz
root(at)???(dot)???(dot)???(dot)???:/home/postgres/style.gz

--

/usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz

rsync --progress --rsh=ssh layertype.gz
root(at)???(dot)???(dot)???(dot)???:/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

Craig James wrote:
>
> On 2007-06-11 Christo Du Preez wrote:
>> 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.
>
> One simple possibility that bit me in the past: If you do
> pg_dump/pg_restore to create a copy of the database, you have to
> ANALYZE the newly-restored database. I mistakenly assumed that
> pg_restore would do this, but you have to run ANALYZE explicitely
> after a restore.
>
> Craig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
Christo Du Preez

Senior Software Engineer
Mecola IT
Mobile: +27 [0]83 326 8087
Skype: christodupreez
Website: http://www.locateandtrade.co.za