Message-ID: <448D8940.4050209@aeccom.com>
Date: Mon, 12 Jun 2006 17:33:20 +0200
From: Sven Geisler <sgeisler@aeccom.com>
Organization: AEC/communications GmbH
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: Ruben Rubio Rey <ruben@rentalia.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Posrgres speed problem
References: <448D7C81.1000108@rentalia.com>
In-Reply-To: <448D7C81.1000108@rentalia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Ruben,

Ruben Rubio Rey schrieb:
> 
> Hi,
> 
> Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel 
> 2.6.9-1.667smp)
> 
> I have two similar servers, one in production and another for testing 
> purposes.
> Databases are equal (with a difference of some hours)
> 
> In the testing server, an sql sentence takes arround 1 sec.
> In production server (low server load) takes arround 50 secs, and uses 
> too much resources.
> 
> Explain analyze takes too much load, i had to cancel it!
> 
> Could it be a  it a bug?
> Any ideas?

How do you load the data to the testing server? (Dump, Copy, etc)
As you wrote the difference are some hours. I think you copy something.

It is possible that you production database as too much deleted tuples.
Vacuum full does only rebuild the table an not the index. You may also 
run reindex on certain tables. I guess, this may the issue if you use 
dump/restore to get your production copy.

Is three a huge difference in the result of this queries:
select relname,relpages,reltuples from pg_class order by relpages desc;
and
select relname,relpages,reltuples from pg_class where relname like 
'%index' order by relpages desc;

Cheers Sven.