Re: How to remove CLUSTERs and 'partitioning tables' - Mailing list pgsql-general

From Christopher Browne
Subject Re: How to remove CLUSTERs and 'partitioning tables'
Date
Msg-id m3r82ix1wv.fsf@wolfe.cbbrowne.com
Whole thread Raw
In response to How to remove CLUSTERs and 'partitioning tables'  (Gabriele Bartolini <gbartolini@optusnet.com.au>)
List pgsql-general
After a long battle with technology,gbartolini@optusnet.com.au (Gabriele Bartolini), an earthling, wrote:
>    just a quick and probably stupid question. When I make a cluster
> based on an index on a table, how can I remove it later? Should I
> 'create' a new one by using the primary key index?

There are two options:

1.  If there is something more relevant to cluster on, you might
    cluster on that instead.

2.  If there is nothing relevant to cluster on, then you could leave
    it alone.

In either case, as _further_ changes are made to the table, the
clustering is likely to lose its effect as new entries will not
necessarily be made in the order the clustering indicates.

>    Also, can I have more than 1 cluster on my table? Let me explain
> ...  can I put for instance the record with, let's say, age between
> 10 and 20 on a specific cluster, from 20 to 30 on another one, and
> so on?

No, that idea doesn't make much sense.

CLUSTER reorganizes the table base based an index that you select.
You get to pick one index.

Mind you, if you created a functional index, you might get something
sort of like this.

I'm not sure of the ideal syntax for this, but let's say you made up
an index:

  create index age_clusters on tbl1 (round(age, -1));

(Which might or might not resemble how you'd round AGE to the nearest
10 :-)).

Cluster on that index and you might get the desired result.  But the
point is that you need to be able to express it as ONE index, not as 5
of them...
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/
Rule of Scarcity
"If it's not allocated by a market, then it's more expensive than
money."
-- Mark Miller

pgsql-general by date:

Previous
From: Ron Johnson
Date:
Subject: Re: need for in-place upgrades (was Re: State of
Next
From: Christopher Browne
Date:
Subject: Re: need for in-place upgrades (was Re: State of