From: John A Meinel <john@arbash-meinel.com>
To: SpaceBallOne <space_ball_one@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Can anyone explain this: duplicate dbs.
Date: Tue, 24 May 2005 21:39:15 -0500
Message-ID: <4293E553.6070400@arbash-meinel.com> (raw)
In-Reply-To: <BAY14-DAV38E580C92EFF811F1266ACC0E0@phx.gbl>
References: <BAY14-DAV38E580C92EFF811F1266ACC0E0@phx.gbl>
SpaceBallOne wrote:
> Wondering if someone could explain a pecularity for me:
>
> We have a database which takes 1000ms to perform a certain query on.
>
> If I pg_dump that database then create a new database (e.g. "tempdb")
> and upload the dump file (thus making a duplicate) then the same query
> only takes 190ms !!
> Vacuum, vacuum analyse, and vacuum full analyse does not seem to have
> an impact on these times.
>
> Can anyone explain why this may be occurring and how I might be able
> to keep the original database running at the same speed as "tempdb"?
>
> Thanks in advance,
>
> Dave.
What version of postgres?
There are a few possibilities. If you are having a lot of updates to the
table, you can get index bloat. And vacuum doesn't fix indexes. You have
to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
FULL has, so you need to be a little careful with it.
Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
your table ends up nicer when you are done.
Also, older versions of postgres had a worse time with index bloat. One
thing that caused a lot of problem is a table that you insert into over
time, so that all the values are incrementing. If you are deleting older
entries, that area won't be re-used because they fall at the back end. I
believe newer versions have been fixed.
By the way, I think doing:
CREATE DATABASE tempdb WITH TEMPLATE = originaldb;
Is a much faster way of doing dump and load. I *think* it would recreate
indexes, etc. If it just does a copy it may not show the dump/restore
improvement.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
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: john@arbash-meinel.com, space_ball_one@hotmail.com
Subject: Re: Can anyone explain this: duplicate dbs.
In-Reply-To: <4293E553.6070400@arbash-meinel.com>
* 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