Message-ID: <4929E32C.8080400@fuzzy.cz>
Date: Mon, 24 Nov 2008 00:11:40 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Thunderbird 2.0.0.9 (X11/20080213)
MIME-Version: 1.0
CC: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
References: <gg1sv3$27fr$1@news.hub.org> <49253FE1.3070706@archonet.com>
	<0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
	<op.ukw669uvcigqcu@soyouz>
	<75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook>
	<op.ukze24t0cigqcu@soyouz>
	<E3AFB54F617D47F4BE652E85ECC67153@andrusnotebook>
	<op.uk1nbqcgcigqcu@soyouz>
	<AD6DEEFF0CE54A3982082A2CA7C2F722@andrusnotebook>
	<4929A533.5030203@fuzzy.cz> <ggcihb$1v26$2@news.hub.org>
In-Reply-To: <ggcihb$1v26$2@news.hub.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

> 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