Message-ID: <4293E553.6070400@arbash-meinel.com>
Date: Tue, 24 May 2005 21:39:15 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: SpaceBallOne <space_ball_one@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Can anyone explain this: duplicate dbs.
References: <BAY14-DAV38E580C92EFF811F1266ACC0E0@phx.gbl>
In-Reply-To: <BAY14-DAV38E580C92EFF811F1266ACC0E0@phx.gbl>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigED7884077674065F15DD39B9"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigED7884077674065F15DD39B9
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
=:->


--------------enigED7884077674065F15DD39B9
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCk+VTJdeBCYSNAAMRAiw1AKDI0lX0lcNbKRhISBsKsuX44PlMKACgw7HL
GZx7dCOTb0xRh+lzhx5b0ds=
=WYPz
-----END PGP SIGNATURE-----

--------------enigED7884077674065F15DD39B9--