From: Andrus <kobruleht2@hot.ee>
To: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
Date: Sun, 23 Nov 2008 23:35:41 +0200
Message-ID: <ggcihb$1v26$2@news.hub.org> (raw)
In-Reply-To: <4929A533.5030203@fuzzy.cz>
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>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kobruleht2@hot.ee
Subject: Re: Hash join on int takes 8..114 seconds
In-Reply-To: <ggcihb$1v26$2@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox