query speed depends on lifetime of frozen db?

From: Andriy Tkachuk <ant(at)imt(dot)com(dot)ua>
To: pgsql-general(at)postgresql(dot)org
Subject: query speed depends on lifetime of frozen db?
Date: 2002-09-27 07:58:34
Message-ID: 20020927105515.B21761-100000@pool.imt.com.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hi hackers.

There is ineresting behavior of some select query mentioned in $subj.

In working db this query takes:
real 3m10.219s
user 0m0.074s
sys 0m0.074s

it's interesting that vacuum or analyze or reinex not helpfull, BUT

if dump this db and create it again (whith another name maybe, no matter,
just for testing) and query the same query on this db, it takes:
real 0m6.225s
user 0m0.072s
sys 0m0.074s
(other databases continue running)

There is no end of this story!
With some time (couple of days for example) this the same query overloads
machine on this new test db also! No one working with this db during this
time. Works continued only with real working databases. Vacuuming was
as usual (every 2 hours without -f and with it at night one time) :
as i said this behavior does not depend on any vacuuming.

Have anyone any ideas about this?

db=# SELECT version();
version
-----------------------------------------------------------------------
PostgreSQL 7.2.2 on i386-portbld-freebsd4.6.1, compiled by GCC 2.95.3

Thanks,
Andriy.

--
Because strait is the gate, and narrow is the way, which leadeth unto
<b>life</b>, and few there be that find it. (MAT 7:14)
<b>Ask</b>, and it shall be given you; <b>seek</b>, and ye shall find;
<b>knock</b>, and it shall be opened unto you... (MAT 7:7)

ANT17-RIPE

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shridhar Daithankar 2002-09-27 08:06:26 Re: query speed depends on lifetime of frozen db?
Previous Message Peter Kovacs 2002-09-27 06:52:29 Re: [JDBC] Prepared statement performance...

Browse pgsql-hackers by date

  From Date Subject
Next Message Shridhar Daithankar 2002-09-27 08:06:26 Re: query speed depends on lifetime of frozen db?
Previous Message Markus Bertheau 2002-09-27 06:48:40 Re: [PHP] WebDB Developers Wanted