SELECT relpages, reltuples FROM pg_class WHERE relname ='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. CLUSTER
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 the
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 it
right now.
CLUSTER users_pk ON users;
Then, configure your autovacuum so it runs often enough. On a small table
like this (once cleaned up) VACUUM will be very fast, 42 pages should take
just a couple tens of ms to vacuum, so you can do it often.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
