Thread: pg_index.isclustered can work
I can easily have pg_index.indisclustered updated to 'true' if you ever CLUSTER the index. Is that useful to anyone? Remember, clustering doesn't remain if you modify the table. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can easily have pg_index.indisclustered updated to 'true' if you ever
> CLUSTER the index. Is that useful to anyone? Remember, clustering
> doesn't remain if you modify the table.
I don't see any value in it as long as CLUSTER is in the disreputable
shape it's in. I don't really like giving people the impression that
CLUSTER is a supported operation ;-)
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I can easily have pg_index.indisclustered updated to 'true' if you ever > > CLUSTER the index. Is that useful to anyone? Remember, clustering > > doesn't remain if you modify the table. > > I don't see any value in it as long as CLUSTER is in the disreputable > shape it's in. I don't really like giving people the impression that > CLUSTER is a supported operation ;-) OK, I have an idea! 1) Set pg_index.indisclustered during CLUSTER 2) Clear pg_index.indisclustered during vacuum if any tuples are expired 3) or, have vacuum auto-CLUSTER the table as part of vacuum 4) Use pg_index.indisclustered in the optimizer Of course, this assumes we have all the CLUSTER problems fixed. FYI, we now have a CLUSTER section in the TODO list which says: * CLUSTER * cluster all tables at once * prevent lose of indexes, permissions, inheritance * Automaticallykeep clustering on a table * Keep statistics about clustering, perhaps during VACUUM ANALYZE [optimizer] Doesn't look too bad. FYI, the reference to pg_index.indisclustered in ODBC was assuming it meant it was a hash index, which is just plain wrong, so that code is not coming back. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: [snip] > > FYI, the reference to pg_index.indisclustered in ODBC was assuming it > meant it was a hash index, Hmm where could I see it ? > which is just plain wrong, so that code is > not coming back. > regards, Hiroshi Inoue
[ Charset US-ASCII unsupported, converting... ]
> Bruce Momjian wrote:
> >
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> [snip]
>
> >
> > FYI, the reference to pg_index.indisclustered in ODBC was assuming it
> > meant it was a hash index,
>
> Hmm where could I see it ?
>
> > which is just plain wrong, so that code is
> > not coming back.
> >
It is in info.c, SQLStatistics():
/* * Clustered index? I think non-clustered should be type * OTHER not
HASHED */ set_tuplefield_int2(&row->tuple[6], (Int2) (atoi(isclustered) ?
SQL_INDEX_CLUSTERED: SQL_INDEX_OTHER));
The HASH mention has me confused. Is that code valid? Maybe so. What
does ODBC think the column means, HASH or CLUSTER?
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
[ Charset US-ASCII unsupported, converting... ] > Bruce Momjian wrote: > > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > [snip] > > > > > FYI, the reference to pg_index.indisclustered in ODBC was assuming it > > meant it was a hash index, > > Hmm where could I see it ? > > > which is just plain wrong, so that code is > > not coming back. > > I now think the original ODBC code was right. It has defined as possible values:#define SQL_TABLE_STAT 0#define SQL_INDEX_CLUSTERED 1#define SQL_INDEX_HASHED 2#define SQL_INDEX_OTHER 3 Not sure what SQL_TABLE_STAT is for, perhaps we should flag for pg_statistics? Anyway, the test of the flag looks correct to me. Why they would care only about HASH and CLUSTERED, I don't know. I will restore the code, and fix the HASH while I am at it. Of course, the cluster field is still alway false, but it will be ready if we ever get it working. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026