Re: Index speeds up one row table (why)?

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(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc