Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8D65B9FB1B7 for ; Mon, 23 Apr 2007 12:52:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 55744-04 for ; Mon, 23 Apr 2007 12:52:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.246]) by postgresql.org (Postfix) with ESMTP id 003939FA440 for ; Mon, 23 Apr 2007 12:52:42 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1940653ana for ; Mon, 23 Apr 2007 08:52:42 -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=oWbgbJA+GLIlf5MMZr4puH27XEZbIo1Fw6ydCOc78PkoPP1iAsco9RRPxX/CyGUAr/TUybRdMgIoreKzlbvpPabMKL+MCPqxzPWaYyl8fEEyCMbsQjFBu4elloYVJAzCC/3s81iqzaiD3WHU7DBXHiLFQOKdrWDtmWNteRiwrcw= 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=ahYxkkxU5zLDEuXZwX8RsVo4Q39NLI64NnghWoie1NlRXSKzGqcn4m+I02v30AxvP11U+sn0FWWw57unmMmoXkgX3IhhZe89BVZGbCtcBmOIAHcpThUxqkT9t5NEU43b8AdJcXgpwcLbvw7/W9xNU8q0RyTtLXwOdCeg18Ox2co= Received: by 10.101.66.11 with SMTP id t11mr982499ank.1177343556729; Mon, 23 Apr 2007 08:52:36 -0700 (PDT) Received: by 10.100.12.6 with HTTP; Mon, 23 Apr 2007 08:52:36 -0700 (PDT) Message-ID: Date: Mon, 23 Apr 2007 12:52:36 -0300 From: "Nelson Kotowski" To: "Heikki Linnakangas" Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command Cc: pgsql-performance@postgresql.org In-Reply-To: <462C8085.3020602@enterprisedb.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_190688_26491006.1177343556541" References: <462C8085.3020602@enterprisedb.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/392 X-Sequence-Number: 24269 ------=_Part_190688_26491006.1177343556541 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Heikki, Thanks for answering! :) I don't get how creating only the indexes i cluster on would improve my cluster command perfomance. I believed that all other indexes wouldn't interfere because so far they're created in a fashionable time and they don't refer to any field/column in the orders/lineitem table. Could you explain me again? As for the load, when you say the right order to start, you mean i should order the load file by the index field in the table before loading it? Thanks in advance, Nelson P Kotowski Filho. On 4/23/07, Heikki Linnakangas wrote: > > Nelson Kotowski wrote: > > So far, i need to do it in three different scale factors (1, 2 and 5GB > > databases). > > > > My build process comprehends creating the tables without any foreign > keys, > > indexes, etc. - Running OK! > > Then, i load the data from the flat files generated through DBGEN > software > > into these tables. - Running OK! > > > > Finally, i run a "optimize" script that does the following: > > > > - Alter the tables to add the mandatory foreign keys; > > - Create all mandatory indexes; > > - Cluster the orders table by the orders table index; > > - Cluster the lineitem table by the lineitem table index; > > - Vacuum the database; > > - Analyze statistics. > > Cluster will completely rewrite the table and indexes. On step 2, you > should only create the indexes you're clustering on, and create the rest > of them after clustering. > > Or even better, generate and load the data in the right order to start > with, so you don't need to cluster at all. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > ------=_Part_190688_26491006.1177343556541 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Heikki,

Thanks for answering! :)

 I don't get how creating only the indexes i cluster on would improve my cluster command perfomance. I believed that all other indexes wouldn't interfere because so far they're created in a fashionable time and they don't refer to any field/column in the orders/lineitem table. Could you explain me again?

As for the load, when you say the right order to start, you mean i should order the load file by the index field in the table before loading it?

Thanks in advance,
Nelson P Kotowski Filho.

On 4/23/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
Nelson Kotowski wrote:
> So far, i need to do it in three different scale factors (1, 2 and 5GB
> databases).
>
> My build process comprehends creating the tables without any foreign keys,
> indexes, etc. - Running OK!
> Then, i load the data from the flat files generated through DBGEN software
> into these tables. - Running OK!
>
> Finally, i run a "optimize" script that does the following:
>
> - Alter the tables to add the mandatory foreign keys;
> - Create all mandatory indexes;
> - Cluster the orders table by the orders table index;
> - Cluster the lineitem table by the lineitem table index;
> - Vacuum the database;
> - Analyze statistics.

Cluster will completely rewrite the table and indexes. On step 2, you
should only create the indexes you're clustering on, and create the rest
of them after clustering.

Or even better, generate and load the data in the right order to start
with, so you don't need to cluster at all.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

------=_Part_190688_26491006.1177343556541--