contrib/pgstattuple/pgstatindex.c

   1 /*
   2  * contrib/pgstattuple/pgstatindex.c
   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          * Reject attempts to read non-local temporary relations; we would be
 112          * likely to get wrong data since we have no visibility into the owning
 113          * session's local buffers.
 114          */
 115         if (RELATION_IS_OTHER_TEMP(rel))
 116                 ereport(ERROR,
 117                                 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 118                                  errmsg("cannot access temporary tables of other sessions")));
 119
 120         /*
 121          * Read metapage
 122          */
 123         {
 124                 Buffer          buffer = ReadBuffer(rel, 0);
 125                 Page            page = BufferGetPage(buffer);
 126                 BTMetaPageData *metad = BTPageGetMeta(page);
 127
 128                 indexStat.version = metad->btm_version;
 129                 indexStat.level = metad->btm_level;
 130                 indexStat.root_blkno = metad->btm_root;
 131
 132                 ReleaseBuffer(buffer);
 133         }
 134
 135         /* -- init counters -- */
 136         indexStat.root_pages = 0;
 137         indexStat.internal_pages = 0;
 138         indexStat.leaf_pages = 0;
 139         indexStat.empty_pages = 0;
 140         indexStat.deleted_pages = 0;
 141
 142         indexStat.max_avail = 0;
 143         indexStat.free_space = 0;
 144
 145         indexStat.fragments = 0;
 146
 147         /*
 148          * Scan all blocks except the metapage
 149          */
 150         nblocks = RelationGetNumberOfBlocks(rel);
 151
 152         for (blkno = 1; blkno < nblocks; blkno++)
 153         {
 154                 Buffer          buffer;
 155                 Page            page;
 156                 BTPageOpaque opaque;
 157
 158                 /* Read and lock buffer */
 159                 buffer = ReadBuffer(rel, blkno);
 160                 LockBuffer(buffer, BUFFER_LOCK_SHARE);
 161
 162                 page = BufferGetPage(buffer);
 163                 opaque = (BTPageOpaque) PageGetSpecialPointer(page);
 164
 165                 /* Determine page type, and update totals */
 166
 167                 if (P_ISLEAF(opaque))
 168                 {
 169                         int                     max_avail;
 170
 171                         max_avail = BLCKSZ - (BLCKSZ - ((PageHeader) page)->pd_special + SizeOfPageHeaderData);
 172                         indexStat.max_avail += max_avail;
 173                         indexStat.free_space += PageGetFreeSpace(page);
 174
 175                         indexStat.leaf_pages++;
 176
 177                         /*
 178                          * If the next leaf is on an earlier block, it means a
 179                          * fragmentation.
 180                          */
 181                         if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno)
 182                                 indexStat.fragments++;
 183                 }
 184                 else if (P_ISDELETED(opaque))
 185                         indexStat.deleted_pages++;
 186                 else if (P_IGNORE(opaque))
 187                         indexStat.empty_pages++;
 188                 else if (P_ISROOT(opaque))
 189                         indexStat.root_pages++;
 190                 else
 191                         indexStat.internal_pages++;
 192
 193                 /* Unlock and release buffer */
 194                 LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
 195                 ReleaseBuffer(buffer);
 196         }
 197
 198         relation_close(rel, AccessShareLock);
 199
 200         /*----------------------------
 201          * Build a result tuple
 202          *----------------------------
 203          */
 204         {
 205                 TupleDesc       tupleDesc;
 206                 int                     j;
 207                 char       *values[10];
 208                 HeapTuple       tuple;
 209
 210                 /* Build a tuple descriptor for our result type */
 211                 if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
 212                         elog(ERROR, "return type must be a row type");
 213
 214                 j = 0;
 215                 values[j] = palloc(32);
 216                 snprintf(values[j++], 32, "%d", indexStat.version);
 217                 values[j] = palloc(32);
 218                 snprintf(values[j++], 32, "%d", indexStat.level);
 219                 values[j] = palloc(32);
 220                 snprintf(values[j++], 32, INT64_FORMAT,
 221                                  (indexStat.root_pages +
 222                                   indexStat.leaf_pages +
 223                                   indexStat.internal_pages +
 224                                   indexStat.deleted_pages +
 225                                   indexStat.empty_pages) * BLCKSZ);
 226                 values[j] = palloc(32);
 227                 snprintf(values[j++], 32, "%u", indexStat.root_blkno);
 228                 values[j] = palloc(32);
 229                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.internal_pages);
 230                 values[j] = palloc(32);
 231                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.leaf_pages);
 232                 values[j] = palloc(32);
 233                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.empty_pages);
 234                 values[j] = palloc(32);
 235                 snprintf(values[j++], 32, INT64_FORMAT, indexStat.deleted_pages);
 236                 values[j] = palloc(32);
 237                 snprintf(values[j++], 32, "%.2f", 100.0 - (double) indexStat.free_space / (double) indexStat.max_avail * 100.0);
 238                 values[j] = palloc(32);
 239                 snprintf(values[j++], 32, "%.2f", (double) indexStat.fragments / (double) indexStat.leaf_pages * 100.0);
 240
 241                 tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
 242                                                                            values);
 243
 244                 result = HeapTupleGetDatum(tuple);
 245         }
 246
 247         PG_RETURN_DATUM(result);
 248 }
 249
 250 /* --------------------------------------------------------
 251  * pg_relpages()
 252  *
 253  * Get the number of pages of the table/index.
 254  *
 255  * Usage: SELECT pg_relpages('t1');
 256  *                SELECT pg_relpages('t1_pkey');
 257  * --------------------------------------------------------
 258  */
 259 Datum
 260 pg_relpages(PG_FUNCTION_ARGS)
 261 {
 262         text       *relname = PG_GETARG_TEXT_P(0);
 263         int64           relpages;
 264         Relation        rel;
 265         RangeVar   *relrv;
 266
 267         if (!superuser())
 268                 ereport(ERROR,
 269                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 270                                  (errmsg("must be superuser to use pgstattuple functions"))));
 271
 272         relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
 273         rel = relation_openrv(relrv, AccessShareLock);
 274
 275         /* note: this will work OK on non-local temp tables */
 276
 277         relpages = RelationGetNumberOfBlocks(rel);
 278
 279         relation_close(rel, AccessShareLock);
 280
 281         PG_RETURN_INT64(relpages);
 282 }