Re: A tale of two similar databases

On 17 Jan 2006 01:22:20 -0800, kishore(dot)sainath(at)gmail(dot)com
<kishore(dot)sainath(at)gmail(dot)com> wrote:
> Hi All,
>
> I am using PostgreSQL 7.3.2 on a server running Red Hat Linux 9.0.
>
> I have two databases ( identical schema and similar data ).
>
> One database, D1 contains the actual data of a Production Application.
> The other D2 contains dummy data which is used during development and
> testing of the application.
>
> D2 actually contains a recent snapshot of D1 and hence contain almost
> the same data.
>
> The strange thing is that D1 is extremely fast whereas D2 is relatively
> slow for any given query.
> (Thank God, it isnt the other way round :) )
>
> Eg,
> An extensive Statistics query returns in a matter of seconds on D1 but
> takes close to a minute on D2
>
> I would like to know if anybody can answer why it is so.

I am making the assumption that you have checked your query plan on
both databases to make sure that they are the same i.e.

on DB1
explain "big query";

on DB2
explain "big query"

If these are not almost identical then you need to investigate the
reasons for the difference ie bad stats on the dev database or missing
index's etc. Have you vacuum analyzed D2?

One other possible reason is that D1 is mostly in cache and D2 isn't.
If you run the query twice on D2 immediately after each other is the
second query much faster. If this is the case what you might be seeing
is D1 being in constant use is forcing the D2 data back onto the disk
and out of the cache.

--
Harry
http://www.hjackson.org
http://www.uklug.co.uk