contrib/pgstattuple/pgstatindex.c

   1 /*
   2  * $PostgreSQL:$
   3  *
   4  *
   5  * pgstatindex
   6  *
   7  * Copyright (c) 2006 Satoshi Nagayasu <nagayasus@nttdata.co.jp>
   8  *
   9  * Permission to use, copy, modify, and distribute this software and
  10  * its documentation for any purpose, without fee, and without a
  11  * written agreement is hereby granted, provided that the above
  12  * copyright notice and this paragraph and the following two
  13  * paragraphs appear in all copies.
  14  *
  15  * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
  16  * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
  17  * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
  18  * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
  19  * OF THE POSSIBILITY OF SUCH DAMAGE.
  20  *
  21  * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
  22  * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  23  * A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
  24  * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
  25  * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
  26  */
  27
  28 #include "postgres.h"
  29
  30 #include "access/heapam.h"
  31 #include "access/nbtree.h"
  32 #include "catalog/namespace.h"
  33 #include "funcapi.h"
  34 #include "miscadmin.h"
  35 #include "storage/bufmgr.h"
  36 #include "utils/builtins.h"
  37
  38
  39 extern Datum pgstatindex(PG_FUNCTION_ARGS);
  40 extern Datum pg_relpages(PG_FUNCTION_ARGS);
  41
  42 PG_FUNCTION_INFO_V1(pgstatindex);
  43 PG_FUNCTION_INFO_V1(pg_relpages);
  44
  45 #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
  46 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
  47
  48 #define CHECK_PAGE_OFFSET_RANGE(pg, offnum) { \
  49                 if ( !(FirstOffsetNumber <= (offnum) && \
  50                                                 (offnum) <= PageGetMaxOffsetNumber(pg)) ) \
  51                          elog(ERROR, "page offset number out of range"); }
  52
  53 /* note: BlockNumber is unsigned, hence can't be negative */
  54 #define CHECK_RELATION_BLOCK_RANGE(rel, blkno) { \
  55                 if ( RelationGetNumberOfBlocks(rel) <= (BlockNumber) (blkno) ) \
  56                          elog(ERROR, "block number out of range"); }
  57
  58 /* ------------------------------------------------
  59  * A structure for a whole btree index statistics
  60  * used by pgstatindex().
  61  * ------------------------------------------------
  62  */
  63 typedef struct BTIndexStat
  64 {
  65         uint32          version;
  66         uint32          level;
  67         BlockNumber root_blkno;
  68
  69         uint64          root_pages;
  70         uint64          internal_pages;
  71         uint64          leaf_pages;
  72         uint64          empty_pages;
  73         uint64          deleted_pages;
  74
  75         uint64          max_avail;
  76         uint64          free_space;
  77
  78         uint64          fragments;
  79 }       BTIndexStat;
  80
  81 /* ------------------------------------------------------
  82  * pgstatindex()
  83  *
  84  * Usage: SELECT * FROM pgstatindex('t1_pkey');
  85  * ------------------------------------------------------
  86  */
  87 Datum
  88 pgstatindex(PG_FUNCTION_ARGS)
  89 {
  90         text       *relname = PG_GETARG_TEXT_P(0);
  91         Relation        rel;
  92         RangeVar   *relrv;
  93         Datum           result;
  94         BlockNumber     nblocks;
  95         BlockNumber     blkno;
  96         BTIndexStat indexStat;
  97
  98         if (!superuser())
  99                 ereport(ERROR,
 100                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 101                                  (errmsg("must be superuser to use pgstattuple functions"))));
 102
 103         relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
 104         rel = relation_openrv(relrv, AccessShareLock);
 105
 106         if (!IS_INDEX(rel) || !IS_BTREE(rel))
 107                 elog(ERROR, "relation \"%s\" is not a btree index",
 108                          RelationGetRelationName(rel));
 109
 110         /*
 111          * Read metapage
 112          */
 113         {
 114                 Buffer          buffer = ReadBuffer(rel, 0);
 115                 Page            page = BufferGetPage(buffer);
 116                 BTMetaPageData *metad = BTPageGetMeta(page);
 117
 118                 indexStat.version = metad->btm_version;
 119                 indexStat.level = metad->btm_level;
 120                 indexStat.root_blkno = metad->btm_root;
 121
 122                 ReleaseBuffer(buffer);
 123         }
 124
 125         /* -- init counters -- */
 126         indexStat.root_pages = 0;
 127         indexStat.internal_pages = 0;
 128         indexStat.leaf_pages = 0;
 129         indexStat.empty_pages = 0;
 130         indexStat.deleted_pages = 0;
 131
 132         indexStat.max_avail = 0;
 133         indexStat.free_space = 0;
 134
 135         indexStat.fragments = 0;
 136
 137         /*
 138          * Scan all blocks except the metapage
 139          */
 140         nblocks = RelationGetNumberOfBlocks(rel);
 141
 142         for (blkno = 1; blkno < nblocks; blkno++)
 143         {
 144                 Buffer          buffer;
 145                 Page            page;
 146                 BTPageOpaque opaque;
 147
 148                 /* Read and lock buffer */
 149                 buffer = ReadBuffer(rel, blkno);
 150                 LockBuffer(buffer, BUFFER_LOCK_SHARE);
 151
 152                 page = BufferGetPage(buffer);
 153                 opaque = (BTPageOpaque) PageGetSpecialPointer(page);
 154
 155                 /* Determine page type, and update totals */
 156
 157                 if (P_ISLEAF(opaque))
 158                 {
 159                         int                     max_avail;
 160
 161                         max_avail = BLCKSZ - (BLCKSZ - ((PageHeader) page)->pd_special + SizeOfPageHeaderData);
 162                         indexStat.max_avail += max_avail;
 163                         indexStat.free_space += PageGetFreeSpace(page);
 164
 165                         indexStat.leaf_pages++;
 166
 167                         /*
 168                          * If the next leaf is on an earlier block, it means a
 169                          * fragmentation.
 170                          */
 171                         if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno)
 172                                 indexStat.fragments++;
 173                 }
 174                 else if (P_ISDELETED(opaque))
 175                         indexStat.deleted_pages++;
 176                 else if (P_IGNORE(opaque))
 177                         indexStat.empty_pages++;
 178                 else if (P_ISROOT(opaque))
 179                         indexStat.root_pages++;
 180                 else
 181                         indexStat.internal_pages++;
 182
 183                 /* Unlock and release buffer */
 184                 LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
 185                 ReleaseBuffer(buffer);
 186         }
 187
 188         relation_close(rel, AccessShareLock);
 189
 190         /*----------------------------
 191          * Build a result tuple
 192          *----------------------------
 193          */
 194         {
 195                 TupleDesc       tupleDesc;
 196                 int                     j;
 197                 char       *values[10];
 198                 HeapTuple       tuple;
 199
 200                 /* Build a tuple descriptor for our result type */
 201                 if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
 202                         elog(ERROR, "return type must be a row type");
 203
 204                 j = 0;
 205                 values[j] = palloc(32);
 206                 snprintf(values[j++], 32, "%d", indexStat.version);
 207                 values[j] = palloc(32);
 208                 snprintf(values[j++], 32, "%d", indexStat.level);
 209                 values[j] = palloc(32);
 210                 snprintf(values[j++], 32, INT64_FORMAT,
 211                                  (indexStat.root_pages +
 212                                   indexStat.leaf_pages +
 213                                   indexStat.internal_pages +
 214                                   indexStat.deleted_pages +
 215                                   indexStat.empty_pages) * BLCKSZ);
 216                 values[j] = palloc(32);
 217                 snprintf(values[j++], 32, "%u", indexStat.root_blkno);
 218                 values[j] = palloc(32);
 219                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.internal_pages);
 220                 values[j] = palloc(32);
 221                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.leaf_pages);
 222                 values[j] = palloc(32);
 223                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.empty_pages);
 224                 values[j] = palloc(32);
 225                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.deleted_pages);
 226                 values[j] = palloc(32);
 227                 snprintf(values[j++], 32, "%.2f", 100.0 - (double) indexStat.free_space / (double) indexStat.max_avail * 100.0);
 228                 values[j] = palloc(32);
 229                 snprintf(values[j++], 32, "%.2f", (double) indexStat.fragments / (double) indexStat.leaf_pages * 100.0);
 230
 231                 tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
 232                                                                            values);
 233
 234                 result = HeapTupleGetDatum(tuple);
 235         }
 236
 237         PG_RETURN_DATUM(result);
 238 }
 239
 240 /* --------------------------------------------------------
 241  * pg_relpages()
 242  *
 243  * Get the number of pages of the table/index.
 244  *
 245  * Usage: SELECT pg_relpages('t1');
 246  *                SELECT pg_relpages('t1_pkey');
 247  * --------------------------------------------------------
 248  */
 249 Datum
 250 pg_relpages(PG_FUNCTION_ARGS)
 251 {
 252         text       *relname = PG_GETARG_TEXT_P(0);
 253         int64           relpages;
 254         Relation        rel;
 255         RangeVar   *relrv;
 256
 257         if (!superuser())
 258                 ereport(ERROR,
 259                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 260                                  (errmsg("must be superuser to use pgstattuple functions"))));
 261
 262         relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
 263         rel = relation_openrv(relrv, AccessShareLock);
 264
 265         relpages = RelationGetNumberOfBlocks(rel);
 266
 267         relation_close(rel, AccessShareLock);
 268
 269         PG_RETURN_INT64(relpages);
 270 }