Skip site navigation (1) Skip section navigation (2)

Re: query performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brian Cox <brian(dot)cox(at)ca(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: query performance
Date: 2008-04-23 20:42:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Brian Cox <brian(dot)cox(at)ca(dot)com> writes:
> 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?

Presumably, the table was really bloated (lots of unused space).

> Would 'vacuum full' have achieved the
> same performance improvements?

It would've compacted the table all right, but probably left the indexes
worse off.

> Is there anything else that needs to be done
> regularly to  prevent this performance degradation?

I suspect that your FSM settings are too low, causing free space found
by VACUUM to be forgotten about.  You might also need to consider
vacuuming more than once a day (there's a tradeoff between how often
you vacuum and how much FSM space you need).

			regards, tom lane

In response to

pgsql-general by date

Next:From: Colin WetherbeeDate: 2008-04-23 20:52:37
Subject: Re: query question really cant give a summary here so read the body ;-)
Previous:From: Roberts, JonDate: 2008-04-23 20:41:50
Subject: Re: query question really cant give a summary here so read the body ;-)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group