Re: Postgresql is very slow

>> I was not aware of the VACUUM functionality earlier, but some times back
>> i read and run this on the server but i did not achieve anything in
>> terms of performance. The server is running from 1 to 1.5 years and we
>> have done VACUUM only once.
>
> vacuuming isn't so much about performance as about maintenance.  You
> don't change the oil in your car to make it go faster, you do it to
> keep it running smoothly.  Don't change it for 1.5 years and you could
> have problems.  sludge build up / dead tuple build up.  Kinda similar.
>

I have to disagree - the VACUUM is a maintenance task, but with a direct
impact on performance. The point is that Postgresql holds dead rows (old
versions, deleted, etc.) until freed by vacuum, and these rows need to be
checked every time (are they still visible to the transaction?). So on a
heavily modified table you may easily end up with most of the tuples being
dead and table consisting of mostly dead tuples.

>> The output of EXPLAIN query;
>>
>> select * from USERS where email like '%bijayant.kumar%';
>> This simplest query tooks 10 minutes and server loads goes from 0.35 to
>> 16.94.
>>
>> EXPLAIN select * from USERS where email like '%bijayant.kumar%';
>>                          QUERY PLAN
>> --------------------------------------------------------------
>>  Seq Scan on USERS  (cost=0.00..54091.84 rows=1 width=161)
>>   Filter: ((email)::text ~~ '%bijayant.kumar%'::text)
>> (2 rows)
>
> You're scanning ~ 54094 sequential pages to retrieve 1 row.   Note
> that explain analyze is generally a better choice, it gives more data
> useful for troubleshooting.

Not necessarily, the 'cost' depends on seq_page_cost and there might be
other value than 1 (which is the default). A better approach is

SELECT relpages, reltuples FROM pg_class WHERE relname = 'users';

which reads the values from system catalogue.

> Definitely need a vacuum full on this table, likely followed by a reindex.

Yes, that's true. I guess the table holds a lot of dead tuples. I'm not
sure why this happens on one server (the new one) and not on the other
one. I guess the original one uses some automatic vacuuming (autovacuum,
cron job, or something like that).

As someone already posted, clustering the table (by primary key for
example) should be much faster than vacuuming and give better performance
in the end. See

http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html

The plain reindex won't help here - it won't remove dead tuples.

Tomas