> I guess you have backups - take them, restore the database on a different
> machine (preferably with the same / similar hw config) and tune the
> queries on it.
>
> After restoring all the tables / indexes will be 'clean' (not bloated), so
> you'll see if performing VACUUM FULL / CLUSTER is the right solution or if
> you have to change the application internals.
>
> Sure, the times will be slightly different but the performance problems
> should remain the same.
VACUUM FULL has
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.
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.
Or probably try to run CLUSTER command in prod server. Hopefully clustering
by product id cannot make things slow
too much.
Andrus.