Re: slow query

On Mon, Feb 24, 2003 at 09:27:56AM -0800, Clarence Gardner wrote:
>
> I don't know about the settings you mention, but a frequent vacuum
> does not at all obviate a vacuum full. My database is vacuumed every
> night, but a while ago I found that a vacuum full changed a simple
> single-table query from well over 30 seconds to one or two. We now
> do a vacuum full every night.

This probably means that either some of your FSM settings should be
different, or that you have long-running queries, or both.  _Some_
advantage to vacuum full is expected, but 30 seconds to one or two is
a pretty big deal.

Except in cases where a large percentage of the table is a vacuum
candidate, the standard vacuum should be more than adequate.  But
there are a couple of gotchas.

You need to have room in your free space map to hold information
about the bulk of the to-be-freed tables.  So perhaps your FSM
settings are not big enough, even though you tried to set them
higher.  (Of course, if you're replacing, say, more than half the
table, setting the FSM high enough isn't practical.)

Another possibility is that you have multiple long-running
transactions that are keeping non-blocking vacuum from being very
effective.  Since those transactions are possibly referencing old
versions of a row, when the non-blocking vacuum comes around, it just
skips the "dead" tuples which are nevertheless alive to someone.
(You can see the effect of this by using the contrib/pgstattuple
function).  Blocking vacuum doesn't have this problem, because it
just waits on the table until everything ahead of it has committed or
rolled back.  So you pay in wait time for all transactions during the
vacuum.  (I have encountered this very problem on a table which gets
a lot of update activity on just on just one row.  Even vacuuming
every minute, the table grew and grew, because of another misbehaving
application which was keeping a transaction open when it shouldn't
have.)

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110