query performance

From: Brian Cox <brian(dot)cox(at)ca(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: query performance
Date: 2008-04-23 19:37:35
Message-ID: 480F8FFF.8080904@ca.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a largish (pg_dump output is 4G) database. The query:

select count(*) from some-table

was taking 120 secs to report that there were 151,000+ rows.
This seemed very slow. This db gets vacuum'd regularly (at least once
per day). I also did a manual 'vacuum analyze', but after it completed,
the query ran no faster. However, after dumping the database and
recreating it
from the backup, the same query takes 2 secs.

Why the dramatic decrease? Would 'vacuum full' have achieved the
same performance improvements? Is there anything else that needs to be done
regularly to prevent this performance degradation?

postgresql 8.1.3 running on redhat es 4.

Thanks,
Brian

Responses

Browse pgsql-general by date

  From Date Subject
Next Message xaviergxf 2008-04-23 19:40:15 Table with differerent Data Types
Previous Message Roberts, Jon 2008-04-23 19:33:35 Re: query question really cant give a summary here so read the body ; -)