Re: Postgresql is very slow

On Mon, Jun 23, 2008 at 11:48 PM, bijayant kumar <bijayant4u(at)yahoo(dot)com> wrote:

OK, you don't have a ton of updates each day, but they add up over time.

> 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.

> Is this the problem of slow database? One more thing if i recreate the database, will it help?

Most likely. What does

vacuum verbose;

on the main database say?

> The output of ANALYZE
>
> ANALYZE verbose USERS;
> INFO: analyzing "public.USERS"
> INFO: "USERS": scanned 3000 of 54063 pages, containing 128 live rows and 1 dead rows; 128 rows in sample, 2307 estimated total rows
> ANALYZE

So, 54963 pages hold 128 live database rows. A page is 8k. that
means you're storing 128 live rows in approximately a 400+ megabyte
file.

> 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.

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