pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nelson Kotowski <nkotowski@gmail.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
Date: Mon, 23 Apr 2007 12:52:36 -0300
Message-ID: <d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com> (raw)
In-Reply-To: <462C8085.3020602@enterprisedb.com>
References: <d34b24380704210754q53aa86c8l3a63211d81febbf7@mail.gmail.com>
	<462C8085.3020602@enterprisedb.com>

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
>

view thread (6+ messages)  latest in thread

Message-ID: <d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com>
Permalink:  ../d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com/
Also on:    postgresql.org/message-id/d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: nkotowski@gmail.com, heikki@enterprisedb.com
  Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
  In-Reply-To: <d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com>
  References: <d34b24380704210754q53aa86c8l3a63211d81febbf7@mail.gmail.com> <462C8085.3020602@enterprisedb.com> <d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox