Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A16A69FB686 for ; Wed, 8 Aug 2007 16:13:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 19732-04 for ; Wed, 8 Aug 2007 16:13:15 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 723A39FB4EF for ; Wed, 8 Aug 2007 16:12:47 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so74675nfh for ; Wed, 08 Aug 2007 12:12:45 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=chxfICTCHspJMtdmm7jBDlj0ERfVMGhFAL5L4KuQJCkkJz1URvTQ8uLhKb3S5s+yG+wfB7do7rIHXv5k0bzuv5Ijo0BQrL1lcpCDWKbfpSOPu2CgU3icz0bmEgdkXapRmA4OQo1baneQ29kgINGLZsdhRZUwqU8cqs9eDgb3Oe8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=oFATMVzcq+yckogQipVtXZomLab7bQfGJWDte2LQjzLv0iY2Q0GwUplLtR7cJ8lQsxjnu+GG2iKqk3nP7yeb2nRZFFfFFJ3I5yoMJtMnf8dqE2TLvpteyYT8JukClevJBEM6WetFqzimPOHGwFg1fk4mFhU3LqZYpOAxf6y+dEA= Received: by 10.78.185.15 with SMTP id i15mr605115huf.1186600364350; Wed, 08 Aug 2007 12:12:44 -0700 (PDT) Received: by 10.78.135.11 with HTTP; Wed, 8 Aug 2007 12:12:44 -0700 (PDT) Message-ID: <357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com> Date: Wed, 8 Aug 2007 15:12:44 -0400 From: "Steven Flatt" To: "Vivek Khera" Subject: Re: When/if to Reindex Cc: "Pgsql performance" In-Reply-To: <38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_99250_10763931.1186600364311" References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com> <38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/70 X-Sequence-Number: 26200 ------=_Part_99250_10763931.1186600364311 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/8/07, Vivek Khera wrote: > > If all you ever did was insert into that table, then you probably > don't need to reindex. If you did mass updates/deletes mixed with > your inserts, then perhaps you do. > > Do some experiments comparing pg_class.relpages for your table and > its indexes before and after a reindex. Decide if the number of > pages you save on the index is worth the trouble. If it shaves off > just a handful of pages, I'd vote no... What's interesting is that an insert-only table can benefit significantly from reindexing after the table is fully loaded. I had done experiments exactly as you suggest (looking at pg_class.relpages), and determined that reindexing results in about a 30% space savings for all indexes except the PK index. The PK index (integer based on a sequence) does not benefit at all. By setting fillfactor=100 on the index prior to reindexing, I get another 10% space savings on all the indexes. Not to mention the general performance improvements when reading from the table... So, we decided that reindexing partitions after they're fully loaded *was* worth it. Steve ------=_Part_99250_10763931.1186600364311 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 8/8/07, Vivek Khera <vivek@khera.org> wrote:
If all you ever did was insert into that table, then you probably
don't need to reindex.  If you did mass updates/deletes mixed with
your inserts, then perhaps you do.

Do some experiments comparing pg_class.relpages for your table and
its indexes before and after a reindex.  Decide if the number of
pages you save on the index is worth the trouble.  If it shaves off
just a handful of pages, I'd vote no...
 
What's interesting is that an insert-only table can benefit significantly from reindexing after the table is fully loaded.  I had done experiments exactly as you suggest (looking at pg_class.relpages), and determined that reindexing results in about a 30% space savings for all indexes except the PK index.  The PK index (integer based on a sequence) does not benefit at all.  By setting fillfactor=100 on the index prior to reindexing, I get another 10% space savings on all the indexes.
 
Not to mention the general performance improvements when reading from the table...
 
So, we decided that reindexing partitions after they're fully loaded *was* worth it.
 
Steve
 
------=_Part_99250_10763931.1186600364311--