Date: Tue, 24 Jun 2008 13:25:45 +0200
To: bijayant4u@yahoo.com, pgsql-performance@postgresql.org
Subject: Re: Postgresql is very slow
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.uc83c7m2cigqcu@apollo13.peufeu.com>
In-Reply-To: <473587.97248.qm@web32701.mail.mud.yahoo.com>
User-Agent: Opera Mail/9.24 (Linux)


> SELECT relpages, reltuples FROM pg_class WHERE relname =3D'users';
>  relpages | reltuples
> ----------+-----------
>     54063 |      2307
> (1 row)

	This is a horribly bloated table.

> The Output of query on the old server which is fast
>
>  relpages | reltuples
> ----------+-----------
>        42 |      1637


	This is a healthy table.

	You need to clean up the users table.
	For this the easiest way is either to VACUUM FULL or CLUSTER it. CLUSTE=
R  =

will be faster in your case. Use whatever index makes sense, or even the=
  =

PK.

> The Slow server load increases whenever i run a simple query, is it th=
e  =

> good idea to run VACUUM full on the live server's database now or it  =

> should be run when the traffic is very low may be in weekend.

	Use CLUSTER.
	It is blocking so your traffic will suffer during the operation, which =
 =

should not take very long. Since you have very few rows, most of the  =

needed time will be reading the table from disk. I would suggest to do i=
t  =

right now.

	CLUSTER users_pk ON users;

	Then, configure your autovacuum so it runs often enough. On a small tab=
le  =

like this (once cleaned up) VACUUM will be very fast, 42 pages should ta=
ke  =

just a couple tens of ms to vacuum, so you can do it often.