X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 0AA1492617A for ; Sun, 1 Jun 2003 08:14:37 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 97323-08 for ; Sun, 1 Jun 2003 08:14:26 -0400 (EDT) Received: from simmts3-srv.bellnexxia.net (simmts3.bellnexxia.net [206.47.199.12]) by developer.postgresql.org (Postfix) with ESMTP id BC1D8925524 for ; Sun, 1 Jun 2003 08:14:25 -0400 (EDT) Received: from [192.168.1.200] ([64.231.215.231]) by simmts3-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030601121424.YMBV3380.simmts3-srv.bellnexxia.net@[192.168.1.200]>; Sun, 1 Jun 2003 08:14:24 -0400 Subject: Re: Index speeds up one row table (why)? From: Rod Taylor To: Dave E Martin XXIII Cc: pgsql-performance@postgresql.org In-Reply-To: <3ED9A923.90500@dave.to> References: <3ED93338.80806@dave.to> <1054434573.11968.42.camel@jester> <3ED9A923.90500@dave.to> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-+4goO8r5posAty0VOel7" Organization: Message-Id: <1054469655.11968.109.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 01 Jun 2003 08:14:15 -0400 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/5 X-Sequence-Number: 2089 --=-+4goO8r5posAty0VOel7 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable sending thread to -performance from -bugs On Sun, 2003-06-01 at 03:20, Dave E Martin XXIII wrote: > Rod Taylor wrote: >=20 > >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 >=20 > Alas, for this application, that means a vacuum once every 5 seconds or= =20 > so. I'll see if I can set up a separate little task to do that (I assume= =20 > at this rate, its better to just keep a connection open, than=20 > setup/teardown). I don't suppose there is a way to get a trigger to do a= =20 > vacuum (which doesn't want to be in a transaction) (thinking it could=20 > check for id mod 100=3D0 or something)? I also assume a few pages isn't= =20 > 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.=20 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. --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-+4goO8r5posAty0VOel7 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA+2e4W6DETLow6vwwRAl1nAJ0TYf5E9vN2fAdk83BY3r/R+Tk6cwCdG4+9 sOLNr9ynUngMSzwCklOLtsE= =IISZ -----END PGP SIGNATURE----- --=-+4goO8r5posAty0VOel7--