X-Original-To: pgsql-bugs@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id B15609261ED for ; Sun, 1 Jun 2003 15:02:01 -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 73786-01 for ; Sun, 1 Jun 2003 15:01:49 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by developer.postgresql.org (Postfix) with SMTP id DC1A592622B for ; Sun, 1 Jun 2003 15:01:48 -0400 (EDT) Received: (qmail 7892 invoked by uid 500); 1 Jun 2003 19:04:09 -0000 Date: Sun, 1 Jun 2003 14:04:09 -0500 From: Bruno Wolff III To: Dave E Martin XXIII Cc: pgsql-bugs@postgresql.org Subject: Re: Index speeds up one row table (why)? Message-ID: <20030601190409.GA7853@wolff.to> Mail-Followup-To: Dave E Martin XXIII , pgsql-bugs@postgresql.org References: <3ED93338.80806@dave.to> <1054434573.11968.42.camel@jester> <3ED9A923.90500@dave.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3ED9A923.90500@dave.to> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/11 X-Sequence-Number: 6106 On Sun, Jun 01, 2003 at 01:20:03 -0600, 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). Maybe you should reconsider how badly you want the app to be totally database agnostic? Using a sequence might be less of a contortion than using vacuum a few times a minute. You are likely to have similar performance issues with other databases, so this section of code may not turn out to be very portable in any case.