From: Rod Taylor <rbt@rbt.ca>
To: Dave E Martin XXIII <postgresql-to.dave@dave.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index speeds up one row table (why)?
Date: 01 Jun 2003 08:14:15 -0400
Message-ID: <1054469655.11968.109.camel@jester> (raw)
In-Reply-To: <3ED9A923.90500@dave.to>
References: <3ED93338.80806@dave.to>
<1054434573.11968.42.camel@jester>
<3ED9A923.90500@dave.to>
sending thread to -performance from -bugs
On Sun, 2003-06-01 at 03:20, Dave E Martin XXIII wrote:
> Rod Taylor wrote:
>
> >An 8 k page will hold approx 140 tuples based on your structure. So,
> >for every ~100 updates you'll want to run vacuum (regular, not full) on
> >the table
>
> Alas, for this application, that means a vacuum once every 5 seconds or
> so. I'll see if I can set up a separate little task to do that (I assume
> at this rate, its better to just keep a connection open, than
> setup/teardown). I don't suppose there is a way to get a trigger to do a
> vacuum (which doesn't want to be in a transaction) (thinking it could
> check for id mod 100=0 or something)? I also assume a few pages isn't
> going to be that bad (just don't let it get to 11000 8).
Sorry... Vacuum cannot be triggered -- nor would you want it to be.
There really isn't anything wrong with vacuuming once every 5 seconds or
so, as it'll take a very short time if there is only a page or so to
deal with.
Setup a script to connect, issue a vacuum, count to 5, issue a vacuum,
count to 5, etc.
More than one page and you will want an index. Having an index is only
going to slow things down in the long run as indexes will not shrink
with a vacuum in 7.3 (7.4 puts an effort towards correcting this). This
means you'll be running REINDEX every couple of minutes, which of course
locks the table, where standard vacuum does not.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.ascAttachments:
signature.asc (application/pgp-signature, 186B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: rbt@rbt.ca, postgresql-to.dave@dave.to
Subject: Re: Index speeds up one row table (why)?
In-Reply-To: <1054469655.11968.109.camel@jester>
References: <3ED93338.80806@dave.to> <1054434573.11968.42.camel@jester> <3ED9A923.90500@dave.to> <1054469655.11968.109.camel@jester>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox