Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8457264FC77 for ; Tue, 30 Dec 2008 05:21:21 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 61751-03 for ; Tue, 30 Dec 2008 05:21:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sd-2317.dedibox.fr (sd-2317.dedibox.fr [88.191.17.192]) by mail.postgresql.org (Postfix) with ESMTP id DCEF664FC57 for ; Tue, 30 Dec 2008 05:21:17 -0400 (AST) Received: from [192.168.10.65] (ASte-Genev-Bois-153-1-66-190.w86-198.abo.wanadoo.fr [86.198.36.190]) by sd-2317.dedibox.fr (Postfix) with ESMTP id 6AE7C25CB; Tue, 30 Dec 2008 10:21:15 +0100 (CET) Message-ID: <4959E807.1090107@lelarge.info> Date: Tue, 30 Dec 2008 10:21:11 +0100 From: Guillaume Lelarge User-Agent: Thunderbird 2.0.0.18 (X11/20081125) MIME-Version: 1.0 To: Laszlo Nagy CC: "pgsql-performance@postgresql.org" Subject: Re: Big index sizes References: <4959E477.9050700@shopzeus.com> In-Reply-To: <4959E477.9050700@shopzeus.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/225 X-Sequence-Number: 32261 Laszlo Nagy a écrit : > We have serveral table where the index size is much bigger than the > table size. > > Example: > > select count(*) from product_price -- 2234244 > > Table size: 400 MB > Index size: 600 MB > > After executing "reindex table product_price", index size reduced to 269MB. > > I believe this affects performance. > > Vacuuming a table does not rebuild the indexes, am I right? Neither VACUUM nor VACUUM FULL rebuild the indexes. CLUSTER and REINDEX do. > I'm not sure > if I need to do this manually, or is this the result of another problem? You need to do this manually. > (For example, too many open transactions, frequent updates?) > Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com