Re: Fix pgstatindex using for large indexes

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Tatsuhito Kasahara wrote:
> Hi.
>
> Tom Lane wrote:
> >> I think that max_avail and free_space should be uint64.
> > Most places where we've dealt with this before, we use double, which is
> > guaranteed to be available whereas uint64 is not ...
> Oh I see.
>
> I fix the patch.
> # I changed "max_avail" and "free_space" to double.
>
> Best regards.
>
> --
> NTT OSS Center
> Tatsuhito Kasahara
>
> kasahara.tatsuhito _at_ oss.ntt.co.jp
>
>

> *** postgresql-8.3.0.org/contrib/pgstattuple/pgstatindex.c 2007-11-16 06:14:31.000000000 +0900
> --- postgresql-8.3.0/contrib/pgstattuple/pgstatindex.c 2008-02-24 19:35:09.000000000 +0900
> ***************
> *** 68,75 ****
> uint32 empty_pages;
> uint32 deleted_pages;
>
> ! uint32 max_avail;
> ! uint32 free_space;
>
> uint32 fragments;
> } BTIndexStat;
> --- 68,75 ----
> uint32 empty_pages;
> uint32 deleted_pages;
>
> ! double max_avail;
> ! double free_space;
>
> uint32 fragments;
> } BTIndexStat;
> ***************
> *** 87,94 ****
> Relation rel;
> RangeVar *relrv;
> Datum result;
> ! uint32 nblocks;
> ! uint32 blkno;
> BTIndexStat indexStat;
>
> if (!superuser())
> --- 87,94 ----
> Relation rel;
> RangeVar *relrv;
> Datum result;
> ! BlockNumber nblocks;
> ! BlockNumber blkno;
> BTIndexStat indexStat;
>
> if (!superuser())
> ***************
> *** 207,231 ****
> values[j] = palloc(32);
> snprintf(values[j++], 32, "%d", indexStat.level);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%d", (indexStat.root_pages +
> ! indexStat.leaf_pages +
> ! indexStat.internal_pages +
> ! indexStat.deleted_pages +
> ! indexStat.empty_pages) * BLCKSZ);
> values[j] = palloc(32);
> snprintf(values[j++], 32, "%d", indexStat.root_blkno);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%d", indexStat.internal_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%d", indexStat.leaf_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%d", indexStat.empty_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%d", indexStat.deleted_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%.2f", 100.0 - (float) indexStat.free_space / (float) indexStat.max_avail * 100.0);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%.2f", (float) indexStat.fragments / (float) indexStat.leaf_pages * 100.0);
>
> tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
> values);
> --- 207,231 ----
> values[j] = palloc(32);
> snprintf(values[j++], 32, "%d", indexStat.level);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%.0f", ( (double) indexStat.root_pages +
> ! (double) indexStat.leaf_pages +
> ! (double) indexStat.internal_pages +
> ! (double) indexStat.deleted_pages +
> ! (double) indexStat.empty_pages) * BLCKSZ);
> values[j] = palloc(32);
> snprintf(values[j++], 32, "%d", indexStat.root_blkno);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%u", indexStat.internal_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%u", indexStat.leaf_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%u", indexStat.empty_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%u", indexStat.deleted_pages);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%.2f", 100.0 - indexStat.free_space / indexStat.max_avail * 100.0);
> values[j] = palloc(32);
> ! snprintf(values[j++], 32, "%.2f", (double) indexStat.fragments / (double) indexStat.leaf_pages * 100.0);
>
> tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
> values);
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +