Re: Can anyone explain this: duplicate dbs.

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