Re: pgstattuple extension for indexes - Mailing list pgsql-patches

BTIem is no longer in CVS HEAD, though it was in 8.1.X.  Please update
your patch for CVS HEAD.  Thanks.

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

Satoshi Nagayasu wrote:
> Hi all,
>
> Here is a patch to add pgstatindex functions to the pgstattuple module,
> which can work with 8.1.4. Please review and try it. Thanks.
>
>
> Satoshi Nagayasu wrote:
> > Bruce,
> >
> > I'll fix it in this week. Please wait a few days.
> > Thanks.
> >
> > Bruce Momjian wrote:
> >> nagayasu-san,
> >>
> >> This looks good, but we would like the code added to
> >> /contrib/pgstattuple, rather than it being its own /contrib module.  Can
> >> you make that adjustment?  Thanks.
> >>
> >> ---------------------------------------------------------------------------
> >>
> >> satoshi nagayasu wrote:
> >>> Hi folks,
> >>>
> >>> As I said on -PATCHES, I've been working on an utility to get
> >>> a b-tree index information. I'm happy to introduce
> >>> my new functions to you.
> >>>
> >>> pgstattuple module provides a `pgstatindex()`, and other small
> >>> functions, which allow you to get b-tree internal information.
> >>> I believe this module will be helpful to know b-tree index deeply.
> >>>
> >>> So please try it, send comment to me, and have fun.
> >>>
> >>> Thanks,
> >>> --
> >>> NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
> >>>
> >>> -----------------------------------------------------
> >>> pgbench=# \x
> >>> Expanded display is on.
> >>> pgbench=# SELECT * FROM pgstatindex('accounts_pkey');
> >>> -[ RECORD 1 ]------+--------
> >>> version            | 2
> >>> tree_level         | 1
> >>> index_size         | 3588096
> >>> root_block_no      | 3
> >>> internal_pages     | 0
> >>> leaf_pages         | 437
> >>> empty_pages        | 0
> >>> deleted_pages      | 0
> >>> avg_leaf_density   | 59.5
> >>> leaf_fragmentation | 49.89
> >>> -----------------------------------------------------
> >>>
> >>>
> >> [ application/x-gzip is not supported, skipping... ]
> >>
> >>> ---------------------------(end of broadcast)---------------------------
> >>> TIP 4: Have you searched our list archives?
> >>>
> >>>                http://archives.postgresql.org
> >
> >
>
>
> --
> NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
> Phone: +81-3-3523-8122

> diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile
> --- pgstattuple.orig/Makefile    2006-08-10 19:22:47.000000000 +0900
> +++ pgstattuple/Makefile    2006-08-10 19:24:05.000000000 +0900
> @@ -6,7 +6,7 @@
>  #
>  #-------------------------------------------------------------------------
>
> -SRCS        = pgstattuple.c
> +SRCS        = pgstattuple.c pgstatindex.c
>
>  MODULE_big    = pgstattuple
>  OBJS        = $(SRCS:.c=.o)
> diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c
> --- pgstattuple.orig/pgstatindex.c    1970-01-01 09:00:00.000000000 +0900
> +++ pgstattuple/pgstatindex.c    2006-08-11 17:51:26.000000000 +0900
> @@ -0,0 +1,714 @@
> +/*
> + * pgstatindex
> + *
> + * Copyright (c) 2006 Satoshi Nagayasu <nagayasus@nttdata.co.jp>
> + *
> + * Permission to use, copy, modify, and distribute this software and
> + * its documentation for any purpose, without fee, and without a
> + * written agreement is hereby granted, provided that the above
> + * copyright notice and this paragraph and the following two
> + * paragraphs appear in all copies.
> + *
> + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
> + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
> + * OF THE POSSIBILITY OF SUCH DAMAGE.
> + *
> + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
> + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
> + * A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
> + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
> + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> + */
> +
> +#include "postgres.h"
> +
> +#include "fmgr.h"
> +#include "funcapi.h"
> +#include "access/heapam.h"
> +#include "access/itup.h"
> +#include "access/nbtree.h"
> +#include "access/transam.h"
> +#include "catalog/namespace.h"
> +#include "catalog/pg_type.h"
> +#include "utils/builtins.h"
> +#include "utils/inval.h"
> +
> +PG_FUNCTION_INFO_V1(pgstatindex);
> +PG_FUNCTION_INFO_V1(bt_metap);
> +PG_FUNCTION_INFO_V1(bt_page_items);
> +PG_FUNCTION_INFO_V1(bt_page_stats);
> +PG_FUNCTION_INFO_V1(pg_relpages);
> +
> +extern Datum pgstatindex(PG_FUNCTION_ARGS);
> +extern Datum bt_metap(PG_FUNCTION_ARGS);
> +extern Datum bt_page_items(PG_FUNCTION_ARGS);
> +extern Datum bt_page_stats(PG_FUNCTION_ARGS);
> +extern Datum pg_relpages(PG_FUNCTION_ARGS);
> +
> +#define PGSTATINDEX_TYPE "public.pgstatindex_type"
> +#define PGSTATINDEX_NCOLUMNS 10
> +
> +#define BTMETAP_TYPE "public.bt_metap_type"
> +#define BTMETAP_NCOLUMNS 6
> +
> +#define BTPAGEITEMS_TYPE "public.bt_page_items_type"
> +#define BTPAGEITEMS_NCOLUMNS 6
> +
> +#define BTPAGESTATS_TYPE "public.bt_page_stats_type"
> +#define BTPAGESTATS_NCOLUMNS 12
> +
> +
> +#define IS_INDEX(r) ((r)->rd_rel->relkind == 'i')
> +#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
> +
> +#define CHECK_PAGE_OFFSET_RANGE(page, offset) { \
> +        if ( !(FirstOffsetNumber<=(offset) && \
> +                        (offset)<=PageGetMaxOffsetNumber(page)) ) \
> +             elog(ERROR, "Page offset number out of range."); }
> +
> +#define CHECK_RELATION_BLOCK_RANGE(rel, blkno) { \
> +        if ( (blkno)<0 && RelationGetNumberOfBlocks((rel))<=(blkno) ) \
> +             elog(ERROR, "Block number out of range."); }
> +
> +/* ------------------------------------------------
> + * structure for single btree page statistics
> + * ------------------------------------------------
> + */
> +typedef struct BTPageStat {
> +    uint32 blkno;
> +    uint32 live_items;
> +    uint32 dead_items;
> +    uint32 page_size;
> +    uint32 max_avail;
> +    uint32 free_size;
> +    uint32 avg_item_size;
> +    uint32 fragments;
> +    bool is_root;
> +    bool is_internal;
> +    bool is_leaf;
> +    bool is_deleted;
> +    bool is_empty;
> +} BTPageStat;
> +
> +/* ------------------------------------------------
> + * A structure for a whole btree index statistics
> + * used by pgstatindex().
> + * ------------------------------------------------
> + */
> +typedef struct BTIndexStat {
> +    uint32 version;
> +    BlockNumber root_blkno;
> +    uint32 level;
> +
> +    uint32 live_items;
> +    uint32 dead_items;
> +
> +    uint32 root_pages;
> +    uint32 internal_pages;
> +    uint32 leaf_pages;
> +    uint32 empty_pages;
> +    uint32 deleted_pages;
> +
> +    uint32 page_size;
> +    uint32 avg_item_size;
> +
> +    uint32 max_avail;
> +    uint32 free_space;
> +
> +    uint32 fragments;
> +} BTIndexStat;
> +
> +/* -------------------------------------------------
> + * GetBTPageStatistics()
> + *
> + * Collect statistics of single b-tree leaf page
> + * -------------------------------------------------
> + */
> +static bool
> +GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
> +{
> +    Page page           = BufferGetPage(buffer);
> +    PageHeader phdr     = (PageHeader) page;
> +    OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
> +    BTPageOpaque opaque = (BTPageOpaque) PageGetSpecialPointer(page);
> +    int item_size = 0;
> +    int off;
> +
> +    stat->blkno = blkno;
> +
> +    stat->max_avail = BLCKSZ - ( BLCKSZ - phdr->pd_special + SizeOfPageHeaderData );
> +
> +    stat->dead_items = stat->live_items = 0;
> +
> +    stat->page_size = PageGetPageSize(page);
> +
> +    /* page type */
> +    stat->is_root    = false;
> +    stat->is_leaf    = false;
> +    stat->is_deleted = false;
> +    stat->is_empty   = false;
> +
> +    if ( P_ISDELETED(opaque) )
> +    {
> +        stat->is_deleted = true;
> +        return true;
> +    }
> +    else if ( P_IGNORE(opaque) )
> +        stat->is_empty = true;
> +    else if ( P_ISLEAF(opaque) )
> +        stat->is_leaf = true;
> +    else if ( P_ISROOT(opaque) )
> +        stat->is_root = true;
> +    else
> +        stat->is_internal = true;
> +
> +    /*----------------------------------------------
> +     * If a next leaf is on the previous block,
> +     * it means a fragmentation.
> +     *----------------------------------------------
> +     */
> +    stat->fragments = 0;
> +    if ( stat->is_leaf )
> +    {
> +        if ( opaque->btpo_next != P_NONE && opaque->btpo_next < blkno )
> +            stat->fragments++;
> +    }
> +
> +    /* count live and dead tuples, and free space */
> +    for (off=FirstOffsetNumber ; off<=maxoff ; off++)
> +    {
> +        BTItem btitem;
> +        IndexTuple itup;
> +
> +        ItemId id = PageGetItemId(page, off);
> +
> +        btitem = (BTItem)PageGetItem(page, id);
> +
> +        itup = &(btitem->bti_itup);
> +
> +        item_size += IndexTupleSize(itup);
> +
> +        if ( !ItemIdDeleted(id) )
> +            stat->live_items++;
> +        else
> +            stat->dead_items++;
> +    }
> +    stat->free_size = PageGetFreeSpace(page);
> +
> +#ifdef NOT_USED
> +    elog(NOTICE, "%d/%d (%.2f%%)", stat->free_size, stat->max_avail,
> +         (float)stat->free_size/(float)stat->max_avail*100.0);
> +#endif
> +
> +    if ( (stat->live_items + stat->dead_items) > 0 )
> +        stat->avg_item_size = item_size / ( stat->live_items + stat->dead_items );
> +    else
> +        stat->avg_item_size = 0;
> +
> +    return true;
> +}
> +
> +
> +/* ------------------------------------------------------
> + * pgstatindex()
> + *
> + * Usage: SELECT * FROM pgstatindex('t1_pkey');
> + * ------------------------------------------------------
> + */
> +Datum
> +pgstatindex(PG_FUNCTION_ARGS)
> +{
> +    text       *relname = PG_GETARG_TEXT_P(0);
> +    Relation    rel;
> +    RangeVar    *relrv;
> +    Datum        result;
> +    uint32        nblocks;
> +    uint32        blkno;
> +    BTIndexStat indexStat;
> +
> +    relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
> +    rel = relation_openrv(relrv, AccessShareLock);
> +
> +    if ( !IS_INDEX(rel) || !IS_BTREE(rel) )
> +        elog(ERROR, "pgstatindex() can be used only on b-tree index.");
> +
> +    /*-------------------
> +     * Read a metapage
> +     *-------------------
> +     */
> +    {
> +        Buffer buffer = ReadBuffer(rel, 0);
> +        Page page = BufferGetPage(buffer);
> +        BTMetaPageData *metad = BTPageGetMeta(page);
> +
> +//        snprintf(values[j++], 32, "%d", metad->btm_magic);
> +        indexStat.version    = metad->btm_version;
> +        indexStat.root_blkno = metad->btm_root;
> +        indexStat.level      = metad->btm_level;
> +//        snprintf(values[j++], 32, "%d", metad->btm_fastroot);
> +//        snprintf(values[j++], 32, "%d", metad->btm_fastlevel);
> +
> +        ReleaseBuffer(buffer);
> +    }
> +
> +    nblocks = RelationGetNumberOfBlocks(rel);
> +
> +    /* -- init stat -- */
> +    indexStat.fragments      = 0;
> +
> +    indexStat.root_pages     = 0;
> +    indexStat.leaf_pages     = 0;
> +    indexStat.internal_pages = 0;
> +    indexStat.empty_pages    = 0;
> +    indexStat.deleted_pages  = 0;
> +
> +    indexStat.max_avail  = 0;
> +    indexStat.free_space = 0;
> +
> +    /*-----------------------
> +     * Scan all blocks
> +     *-----------------------
> +     */
> +    for (blkno=1 ; blkno<nblocks ; blkno++)
> +    {
> +        Buffer buffer = ReadBuffer(rel, blkno);
> +        BTPageStat stat;
> +
> +        /* scan one page */
> +        stat.blkno = blkno;
> +        GetBTPageStatistics(blkno, buffer, &stat);
> +
> +        /*---------------------
> +         * page status (type)
> +         *---------------------
> +         */
> +        if ( stat.is_deleted )
> +            indexStat.deleted_pages++;
> +        else if ( stat.is_leaf )
> +            indexStat.leaf_pages++;
> +        else if ( stat.is_internal )
> +            indexStat.internal_pages++;
> +        else if ( stat.is_empty )
> +            indexStat.empty_pages++;
> +        else if ( stat.is_root )
> +            indexStat.root_pages++;
> +        else
> +            elog(ERROR, "unknown page status.");
> +
> +        /* -- leaf fragmentation -- */
> +        indexStat.fragments += stat.fragments;
> +
> +        if ( stat.is_leaf )
> +        {
> +            indexStat.max_avail  += stat.max_avail;
> +            indexStat.free_space += stat.free_size;
> +        }
> +
> +        ReleaseBuffer(buffer);
> +    }
> +
> +    relation_close(rel, AccessShareLock);
> +
> +#ifdef NOT_USED
> +    elog(NOTICE, "[index information]");
> +    elog(NOTICE, "version.................: %d", indexStat.version);
> +    elog(NOTICE, "tree level..............: %d", indexStat.level);
> +    elog(NOTICE, "index size..............: %d", (indexStat.root_pages +
> +                                    indexStat.leaf_pages +
> +                                    indexStat.internal_pages +
> +                                    indexStat.deleted_pages +
> +                                    indexStat.empty_pages) * BLCKSZ);
> +
> +    elog(NOTICE, "");
> +    elog(NOTICE, "[page statistics]");
> +    elog(NOTICE, "root block number.......: %d", indexStat.root_blkno);
> +    elog(NOTICE, "internal pages..........: %d", indexStat.internal_pages);
> +    elog(NOTICE, "leaf pages..............: %d", indexStat.leaf_pages);
> +    elog(NOTICE, "empty pages.............: %d", indexStat.empty_pages);
> +    elog(NOTICE, "deleted pages...........: %d", indexStat.deleted_pages);
> +
> +    elog(NOTICE, "");
> +    elog(NOTICE, "[leaf statistics]");
> +    elog(NOTICE, "avg. leaf density.......: %.2f%%", 100.0-(float)indexStat.free_space / (float)indexStat.max_avail
*100.0 ); 
> +    elog(NOTICE, "leaf fragmentation......: %.2f%%", (float)indexStat.fragments / 2.0 / (float)indexStat.leaf_pages
*100.0 ); 
> +#endif
> +
> +    /*----------------------------
> +     * Build a result tuple
> +     *----------------------------
> +     */
> +    {
> +        TupleDesc tupleDesc;
> +        int j;
> +        char *values[PGSTATINDEX_NCOLUMNS];
> +
> +        HeapTupleData tupleData;
> +        HeapTuple tuple = &tupleData;
> +
> +        tupleDesc = RelationNameGetTupleDesc(PGSTATINDEX_TYPE);
> +
> +        j = 0;
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", indexStat.version);
> +        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);
> +
> +        result = TupleGetDatum(TupleDescGetSlot(tupleDesc), tuple);
> +    }
> +
> +    PG_RETURN_DATUM( result );
> +}
> +
> +/* -----------------------------------------------
> + * bt_page()
> + *
> + * Usage: SELECT * FROM bt_page('t1_pkey', 0);
> + * -----------------------------------------------
> + */
> +Datum
> +bt_page_stats(PG_FUNCTION_ARGS)
> +{
> +    text       *relname = PG_GETARG_TEXT_P(0);
> +    uint32       blkno   = PG_GETARG_UINT32(1);
> +    Buffer       buffer;
> +
> +    Relation    rel;
> +    RangeVar    *relrv;
> +    Datum        result;
> +
> +    relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
> +    rel = relation_openrv(relrv, AccessShareLock);
> +
> +    CHECK_RELATION_BLOCK_RANGE(rel, blkno);
> +
> +    buffer = ReadBuffer(rel, blkno);
> +
> +    if ( !IS_INDEX(rel) || !IS_BTREE(rel) )
> +        elog(ERROR, "bt_page_stats() can be used only on b-tree index.");
> +
> +    if ( blkno==0 )
> +        elog(ERROR, "Block 0 is a meta page.");
> +
> +    {
> +        HeapTuple tuple;
> +        TupleDesc tupleDesc;
> +        int j;
> +        char *values[BTPAGESTATS_NCOLUMNS];
> +
> +        BTPageStat stat;
> +
> +        GetBTPageStatistics(blkno, buffer, &stat);
> +
> +        tupleDesc = RelationNameGetTupleDesc(BTPAGESTATS_TYPE);
> +
> +        j = 0;
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.blkno);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.live_items);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.dead_items);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.live_items + stat.dead_items);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.avg_item_size);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.page_size);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.free_size);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", stat.is_deleted);
> +        values[j] = palloc(32);
> +//        snprintf(values[j++], 32, "%d", opaque->btpo_prev);
> +        snprintf(values[j++], 32, "%d", 0); /*-- not implemented yet --*/
> +        values[j] = palloc(32);
> +//        snprintf(values[j++], 32, "%d", opaque->btpo_next);
> +        snprintf(values[j++], 32, "%d", 0); /*-- not implemented yet --*/
> +        values[j] = palloc(32);
> +//        snprintf(values[j++], 32, "%d", opaque->btpo.level);
> +        snprintf(values[j++], 32, "%d", 0); /*-- not implemented yet --*/
> +        values[j] = palloc(32);
> +//        snprintf(values[j++], 32, "%d", opaque->btpo_flags);
> +        snprintf(values[j++], 32, "%d", 0); /*-- not implemented yet --*/
> +
> +        tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
> +                                       values);
> +
> +        result = TupleGetDatum(TupleDescGetSlot(tupleDesc), tuple);
> +    }
> +
> +    ReleaseBuffer(buffer);
> +
> +    relation_close(rel, AccessShareLock);
> +
> +    PG_RETURN_DATUM(result);
> +}
> +
> +/*-------------------------------------------------------
> + * bt_page_items()
> + *
> + * Get IndexTupleData set in a leaf page
> + *
> + * Usage: SELECT * FROM bt_page_items('t1_pkey', 0);
> + *-------------------------------------------------------
> + */
> +/* ---------------------------------------------------
> + * data structure for SRF to hold a scan information
> + * ---------------------------------------------------
> + */
> +struct user_args
> +{
> +    TupleDesc tupd;
> +    Relation rel;
> +    Buffer buffer;
> +    Page page;
> +    uint16 offset;
> +};
> +
> +Datum
> +bt_page_items(PG_FUNCTION_ARGS)
> +{
> +    text       *relname = PG_GETARG_TEXT_P(0);
> +    uint32       blkno   = PG_GETARG_UINT32(1);
> +
> +    RangeVar    *relrv;
> +    Datum result;
> +    char *values[BTPAGEITEMS_NCOLUMNS];
> +    BTPageOpaque opaque;
> +    HeapTuple tuple;
> +    ItemId id;
> +
> +    FuncCallContext *fctx;
> +    MemoryContext mctx;
> +    struct user_args *uargs = NULL;
> +
> +    if ( blkno==0 )
> +        elog(ERROR, "Block 0 is a meta page.");
> +
> +    if ( SRF_IS_FIRSTCALL() )
> +    {
> +        fctx = SRF_FIRSTCALL_INIT();
> +        mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
> +
> +        uargs = palloc(sizeof(struct user_args));
> +
> +        uargs->tupd = RelationNameGetTupleDesc(BTPAGEITEMS_TYPE);
> +        uargs->offset = FirstOffsetNumber;
> +
> +        relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
> +        uargs->rel = relation_openrv(relrv, AccessShareLock);
> +
> +        CHECK_RELATION_BLOCK_RANGE(uargs->rel,  blkno);
> +
> +        uargs->buffer = ReadBuffer(uargs->rel, blkno);
> +
> +        if ( !IS_INDEX(uargs->rel) || !IS_BTREE(uargs->rel) )
> +            elog(ERROR, "bt_page_items() can be used only on b-tree index.");
> +
> +        uargs->page = BufferGetPage(uargs->buffer);
> +
> +        opaque = (BTPageOpaque) PageGetSpecialPointer(uargs->page);
> +
> +        if ( P_ISDELETED(opaque) )
> +            elog(NOTICE, "bt_page_items(): this page is deleted.");
> +
> +        fctx->max_calls = PageGetMaxOffsetNumber(uargs->page);
> +        fctx->user_fctx = uargs;
> +
> +        MemoryContextSwitchTo(mctx);
> +    }
> +
> +    fctx = SRF_PERCALL_SETUP();
> +    uargs = fctx->user_fctx;
> +
> +    if ( fctx->call_cntr < fctx->max_calls )
> +    {
> +        BTItem btitem;
> +        IndexTuple itup;
> +
> +        id = PageGetItemId(uargs->page, uargs->offset);
> +
> +        if ( !ItemIdIsValid(id) )
> +            elog(ERROR, "Invalid ItemId.");
> +
> +        btitem = (BTItem)PageGetItem(uargs->page, id);
> +        itup = &(btitem->bti_itup);
> +
> +        {
> +            int j = 0;
> +
> +            BlockNumber blkno = BlockIdGetBlockNumber(&(itup->t_tid.ip_blkid));
> +
> +            values[j] = palloc(32);
> +            snprintf(values[j++], 32, "%d", uargs->offset);
> +            values[j] = palloc(32);
> +            snprintf(values[j++], 32, "(%u,%u)", blkno, itup->t_tid.ip_posid);
> +            values[j] = palloc(32);
> +            snprintf(values[j++], 32, "%d", IndexTupleSize(itup));
> +            values[j] = palloc(32);
> +            snprintf(values[j++], 32, "%c", IndexTupleHasNulls(itup) ? 't' : 'f');
> +            values[j] = palloc(32);
> +            snprintf(values[j++], 32, "%c", IndexTupleHasVarwidths(itup) ? 't' : 'f');
> +
> +            {
> +                int off;
> +                char *dump;
> +                char *ptr = (char *)itup + IndexInfoFindDataOffset(itup->t_info);
> +
> +                dump = palloc(IndexTupleSize(itup)*3);
> +                memset(dump, 0, IndexTupleSize(itup)*3);
> +
> +                for (off=0 ;
> +                     off < IndexTupleSize(itup)-IndexInfoFindDataOffset(itup->t_info) ;
> +                     off++)
> +                {
> +                    if ( dump[0]=='\0' )
> +                        sprintf(dump, "%02x", *(ptr+off) & 0xff);
> +                    else
> +                    {
> +                        char buf[4];
> +                        sprintf(buf, " %02x", *(ptr+off) & 0xff);
> +                        strcat(dump, buf);
> +                    }
> +                }
> +                values[j] = dump;
> +            }
> +
> +            tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(uargs->tupd), values);
> +            result = TupleGetDatum(TupleDescGetSlot(uargs->tupd), tuple);
> +        }
> +
> +        uargs->offset = uargs->offset + 1;
> +
> +        SRF_RETURN_NEXT(fctx, result);
> +    }
> +    else
> +    {
> +        ReleaseBuffer(uargs->buffer);
> +        relation_close(uargs->rel, AccessShareLock);
> +
> +        SRF_RETURN_DONE(fctx);
> +    }
> +}
> +
> +
> +/* ------------------------------------------------
> + * bt_metap()
> + *
> + * Get a btree meta-page information
> + *
> + * Usage: SELECT * FROM bt_metap('t1_pkey')
> + * ------------------------------------------------
> + */
> +Datum
> +bt_metap(PG_FUNCTION_ARGS)
> +{
> +    text       *relname = PG_GETARG_TEXT_P(0);
> +    Buffer       buffer;
> +
> +    Relation    rel;
> +    RangeVar    *relrv;
> +    Datum        result;
> +
> +    relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
> +    rel = relation_openrv(relrv, AccessShareLock);
> +
> +    if ( !IS_INDEX(rel) || !IS_BTREE(rel) )
> +        elog(ERROR, "bt_metap() can be used only on b-tree index.");
> +
> +    buffer = ReadBuffer(rel, 0);
> +
> +    {
> +        BTMetaPageData *metad;
> +
> +        TupleDesc tupleDesc;
> +        int j;
> +        char *values[BTMETAP_NCOLUMNS];
> +        HeapTuple tuple;
> +
> +        Page page = BufferGetPage(buffer);
> +
> +        metad = BTPageGetMeta(page);
> +
> +        tupleDesc = RelationNameGetTupleDesc(BTMETAP_TYPE);
> +
> +        j = 0;
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", metad->btm_magic);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", metad->btm_version);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", metad->btm_root);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", metad->btm_level);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", metad->btm_fastroot);
> +        values[j] = palloc(32);
> +        snprintf(values[j++], 32, "%d", metad->btm_fastlevel);
> +
> +        tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
> +                                       values);
> +
> +        result = TupleGetDatum(TupleDescGetSlot(tupleDesc), tuple);
> +    }
> +
> +    ReleaseBuffer(buffer);
> +
> +    relation_close(rel, AccessShareLock);
> +
> +    PG_RETURN_DATUM(result);
> +}
> +
> +/* --------------------------------------------------------
> + * pg_relpages()
> + *
> + * Get a number of pages of the table/index.
> + *
> + * Usage: SELECT pg_relpages('t1');
> + *        SELECT pg_relpages('t1_pkey');
> + * --------------------------------------------------------
> + */
> +Datum
> +pg_relpages(PG_FUNCTION_ARGS)
> +{
> +    text       *relname = PG_GETARG_TEXT_P(0);
> +
> +    Relation    rel;
> +    RangeVar    *relrv;
> +    int4  relpages;
> +
> +    relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
> +    rel = relation_openrv(relrv, AccessShareLock);
> +
> +    relpages = RelationGetNumberOfBlocks(rel);
> +
> +    relation_close(rel, AccessShareLock);
> +
> +    PG_RETURN_INT32(relpages);
> +}
> +
> diff -ruN pgstattuple.orig/pgstattuple.sql.in pgstattuple/pgstattuple.sql.in
> --- pgstattuple.orig/pgstattuple.sql.in    2006-08-10 19:22:47.000000000 +0900
> +++ pgstattuple/pgstattuple.sql.in    2006-08-11 17:46:19.000000000 +0900
> @@ -22,3 +22,97 @@
>  RETURNS pgstattuple_type
>  AS 'MODULE_PATHNAME', 'pgstattuplebyid'
>  LANGUAGE 'C' STRICT;
> +
> +--
> +-- pgstatindex
> +--
> +DROP TYPE pgstatindex_type CASCADE;
> +CREATE TYPE pgstatindex_type AS (
> +  version int4,
> +  tree_level int4,
> +  index_size int4,
> +  root_block_no int4,
> +  internal_pages int4,
> +  leaf_pages int4,
> +  empty_pages int4,
> +  deleted_pages int4,
> +  avg_leaf_density float8,
> +  leaf_fragmentation float8
> +);
> +
> +CREATE OR REPLACE FUNCTION pgstatindex(text)
> +RETURNS pgstatindex_type
> +AS 'MODULE_PATHNAME', 'pgstatindex'
> +LANGUAGE 'C' STRICT;
> +
> +--
> +-- bt_metap()
> +--
> +DROP TYPE bt_metap_type CASCADE;
> +CREATE TYPE bt_metap_type AS (
> +  magic int4,
> +  version int4,
> +  root int4,
> +  level int4,
> +  fastroot int4,
> +  fastlevel int4
> +);
> +
> +CREATE OR REPLACE FUNCTION bt_metap(text)
> +RETURNS bt_metap_type
> +AS 'MODULE_PATHNAME', 'bt_metap'
> +LANGUAGE 'C' STRICT;
> +
> +--
> +-- bt_page_items()
> +--
> +DROP TYPE bt_page_items_type CASCADE;
> +CREATE TYPE bt_page_items_type AS (
> +  itemoffset int4,
> +  ctid tid,
> +  itemlen int4,
> +  nulls bool,
> +  vars bool,
> +  data text
> +);
> +
> +DROP FUNCTION bt_page_items(text, int4);
> +
> +CREATE OR REPLACE FUNCTION bt_page_items(text, int4)
> +RETURNS SETOF bt_page_items_type
> +AS 'MODULE_PATHNAME', 'bt_page_items'
> +LANGUAGE 'C' STRICT;
> +
> +--
> +-- bt_page_stats()
> +--
> +DROP TYPE bt_page_stats_type CASCADE;
> +CREATE TYPE bt_page_stats_type AS (
> +  blkno int4,
> +  live_items int4,
> +  dead_items int4,
> +  total_items int4,
> +  avg_item_size float,
> +  page_size int4,
> +  free_size int4,
> +  is_deleted int4,
> +  btpo_prev int4,
> +  btpo_next int4,
> +  btpo_level int4,
> +  btpo_flags int4
> +);
> +
> +DROP FUNCTION bt_page_stats(text, int4);
> +
> +CREATE OR REPLACE FUNCTION bt_page_stats(text, int4)
> +RETURNS bt_page_stats_type
> +AS 'MODULE_PATHNAME', 'bt_page_stats'
> +LANGUAGE 'C' STRICT;
> +
> +--
> +-- pg_relpages()
> +--
> +CREATE OR REPLACE FUNCTION pg_relpages(text)
> +RETURNS int
> +AS 'MODULE_PATHNAME', 'pg_relpages'
> +LANGUAGE 'C' STRICT;
> diff -ruN pgstattuple.orig/test.sh pgstattuple/test.sh
> --- pgstattuple.orig/test.sh    1970-01-01 09:00:00.000000000 +0900
> +++ pgstattuple/test.sh    2006-08-11 17:47:05.000000000 +0900
> @@ -0,0 +1,22 @@
> +#!/bin/sh
> +
> +export PATH=/usr/local/pgsql814/bin:$PATH
> +
> +psql pgbench<<EOF
> +DROP FUNCTION pgstatindex(text);
> +EOF
> +
> +psql -f /usr/local/pgsql814/share/contrib/pgstattuple.sql pgbench
> +
> +psql pgbench<<EOF
> +SELECT * FROM pg_relpages('accounts_pkey');
> +\x
> +SELECT * FROM pgstatindex('accounts_pkey');
> +SELECT * FROM bt_metap('accounts_pkey');
> +\x
> +SELECT * FROM bt_page_items('accounts_pkey', 0);
> +SELECT * FROM bt_page_items('accounts_pkey', 1) limit 20;
> +
> +\x
> +SELECT * FROM bt_page_stats('accounts_pkey', 1);
> +EOF
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

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