Re: clustering by partial indexes

On Tue, Nov 15, 2005 at 08:41:06PM -0500, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > Well, currently you can only cluster on a single index per table, and I
> > really doubt that will change. In any case, if someone's going to work
> > on clustered indexes I think it would be much more worthwhile to make
> > them self-maintaining (or at least more self-maintaining).
>
> Wow, imagine if we could cluster by partial indexes, and then imagine we
> could allow multiple clustering per table if the partial indexes did not
> overlap --- that is a massive project.

Particularly the bit about proving they didn't overlap. Only a full
table scan will prove one way or the other in the general case.

ISTM that the only use for clustering on a partial index is as an
indication of the actual order. So, if you have the index:

CREATE INDEX myindex ON mytable (col1,col2) WHERE col3 = true;

Then clustering on it would be equivalent to:

CREATE INDEX dummyindex ON mytable (col1,col2,col3=true);
CLUSTER mytable ON dummyindex;
DROP INDEX dummyindex;

Although you'd probably use a more efficient SELECT INTO construct.

Additionally, clustering by multiple indexes would be simply be an
indication of the order, concatinate the fields. All that is needed is
a cluster method that doesn't use an INDEX, the rest is just
sugercoating.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.