From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Heikki Linnakangas <heikki@enterprisedb.com>
To: Nelson Kotowski <nkotowski@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
Date: Mon, 23 Apr 2007 13:26:07 -0500
Message-ID: <462CB3EE.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com>
References: <d34b24380704210754q53aa86c8l3a63211d81febbf7@mail.gmail.com>
<462C8085.3020602@enterprisedb.com>
<d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com>
>>> On Mon, Apr 23, 2007 at 10:52 AM, in message
<d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com>, "Nelson
Kotowski" <nkotowski@gmail.com> wrote:
>
> 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?
What a CLUSTER command does is to read through the table in the sequence specified by the index (using the index) and copy the data into a new copy of the table. It then applies all of the permissions, constraints, etc. from the original table to the copy and builds all the same indexes as were on the original table. (You can't use the same indexes, because the data is shifted around to new spots.) The new copy of the table then takes the place of the original. If you build indexes and then cluster, you throw away the results of the work from the original build, and do it all over 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?
If you load the rows in the same order that the index would read them during the cluster, there is no need to cluster and no benefit from doing so.
-Kevin
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, heikki@enterprisedb.com, nkotowski@gmail.com
Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
In-Reply-To: <462CB3EE.EE98.0025.0@wicourts.gov>
* 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