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 7A65692595B for ; Sat, 31 May 2003 22:29:51 -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 80324-09 for ; Sat, 31 May 2003 22:29:41 -0400 (EDT) Received: from simmts5-srv.bellnexxia.net (simmts5.bellnexxia.net [206.47.199.163]) by developer.postgresql.org (Postfix) with ESMTP id 67481925942 for ; Sat, 31 May 2003 22:29:40 -0400 (EDT) Received: from [192.168.1.200] ([64.231.215.231]) by simmts5-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030601022937.UMIA7058.simmts5-srv.bellnexxia.net@[192.168.1.200]>; Sat, 31 May 2003 22:29:37 -0400 Subject: Re: Index speeds up one row table (why)? From: Rod Taylor To: Dave E Martin XXIII Cc: pgsql-bugs@postgresql.org In-Reply-To: <3ED93338.80806@dave.to> References: <3ED93338.80806@dave.to> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-GMB/VZzTWx+23wqx8UuU" Organization: Message-Id: <1054434573.11968.42.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 31 May 2003 22:29:33 -0400 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/131 X-Sequence-Number: 6092 --=-GMB/VZzTWx+23wqx8UuU Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > vacuum verbose bigint_unique_ids; > INFO: --Relation public.bigint_unique_ids-- > INFO: Index bigint_unique_ids__table_name: Pages 29; Tuples 1: Deleted= =20 > 5354. > CPU 0.01s/0.04u sec elapsed 0.05 sec. > INFO: Removed 11348 tuples in 79 pages. > CPU 0.00s/0.02u sec elapsed 0.02 sec. > INFO: Pages 79: Changed 1, Empty 0; Tup 1: Vac 11348, Keep 0, UnUsed 0. > Total CPU 0.03s/0.06u sec elapsed 0.14 sec. Vacuum (regular, not full) frequently enough that the 'Pages' value doesn't increase past 1 and you'll be fine. A sequential scan on a very small table is what you want to have. In this particular case, vacuum removed over 11000 dead versions of the tuple. 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 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-GMB/VZzTWx+23wqx8UuU 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+2WUN6DETLow6vwwRAqBYAJ9T/4JelT/VgC/OdzozMkcE7dqu5QCcC9lV gJN0G1B/1iECZKDcyFuPFJc= =lPHq -----END PGP SIGNATURE----- --=-GMB/VZzTWx+23wqx8UuU--