X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EA568D1B509 for ; Sun, 19 Oct 2003 05:10:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11251-07 for ; Sun, 19 Oct 2003 02:09:47 -0300 (ADT) Received: from slgan.com (c-67-163-28-202.client.comcast.net [67.163.28.202]) by svr1.postgresql.org (Postfix) with ESMTP id BACA4D1B4E3 for ; Sun, 19 Oct 2003 02:09:45 -0300 (ADT) Received: from [192.168.10.52] ([192.168.10.52]) by slgan.com (8.10.2/8.10.2) with ESMTP id h9J5Btr20650; Sun, 19 Oct 2003 00:12:10 -0500 Mime-Version: 1.0 X-Sender: slgan@192.168.10.10 Message-Id: In-Reply-To: <16213.1066530060@sss.pgh.pa.us> References: <200310181458.15575.josh@agliodbs.com> <16213.1066530060@sss.pgh.pa.us> Date: Sun, 19 Oct 2003 00:11:13 -0500 To: Tom Lane From: Seum-Lim Gan Subject: Re: index file bloating still in 7.4 ? Cc: josh@agliodbs.com, pgsql-performance@postgresql.org Content-Type: text/plain; charset="us-ascii" ; format="flowed" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/493 X-Sequence-Number: 4241 Hi Tom, I did that when I have stopped my updates. Now, I am doing updates below is the output of vacuum. After doing the vacuum verbose analyze, it reported the following : INFO: vacuuming "craft.dsperf_rda_or_key" INFO: index "hello242_1105" now contains 1792276 row versions in 6237 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.61s/0.36u sec elapsed 17.92 sec. INFO: "hello_rda_or_key": found 0 removable, 1791736 nonremovable row versions in 30892 pages DETAIL: 1492218 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 1.95s/1.99u sec elapsed 26.95 sec. INFO: analyzing "craft.dsperf_rda_or_key" INFO: "hello_rda_or_key": 30909 pages, 3000 rows sampled, 93292 estimated total rows VACUUM Gan At 10:21 pm -0400 2003/10/18, Tom Lane wrote: >Seum-Lim Gan writes: >> Sample verbose analyze: > >> VACUUM VERBOSE ANALYZE hello_rda_or_key; >> INFO: vacuuming "craft.hello_rda_or_key" >> INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages > >So what's the problem? That doesn't seem like a particularly bloated >index. You didn't say what datatype the index is on, but making the >most optimistic assumptions, index entries must use at least 16 bytes >each. You're getting about 300 entries per page, compared to the >theoretical limit of 512 ... actually more, since I'm not allowing for >upper btree levels in this calculation ... which says to me that the >page loading is right around the expected btree loading of 2/3. > > regards, tom lane -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+