My test computer has PostgreSql 8.3, 4 GB RAM, SSD disks, Intel X2Extreme CPU So it is much faster than this prod server. No idea how to emulate this environment. I can create new db in prod server as old copy but this can be used in late night only.
Well, a faster but comparable system may not be a problem - the query might run 10 times faster, but it still will be slow (say 40 seconds instead of 8 minutes).
What is a problem is a different I/O system - SSD instead of traditional drives in this case. I have no direct experience with with SSD yet, but AFAIK the seek time is much better compared to regular drives (say 0.1ms instead of 10ms, that is 100-times faster).
So you can't just put on old SATA drive into the test machine?
Where to find script which clones some database in server? Something like CREATE DATABASE newdb AS SELECT * FROM olddb;
>
It would be more convenient to run db cloning script from pgadmin command window. Only way I found is to use SSH with pg_dup/pg_restore. This requires SSH access to server and SSH port opening to public internet.
Yes, using pg_dump | pg_restore is probably the way to clone database. But it will slow down the system, as it has to do a lot of I/O (and as it seems to be a bottleneck already, I don't think this is a good idea).
Or probably try to run CLUSTER command in prod server. Hopefully clustering by product id cannot make things slow too much.
As already noted, CLUSTER command causes exclusive lock on the database. So this is an operation you'd like to do on production server ...
Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
