Re: Hash join on int takes 8..114 seconds

> 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