granicus.if.org Git - postgresql/blob - contrib/pgstattuple/pgstattuple.c

   1 /*
   2  * $Header: /cvsroot/pgsql/contrib/pgstattuple/pgstattuple.c,v 1.2 2001/10/25 05:49:20 momjian Exp $
   3  *
   4  * Copyright (c) 2001  Tatsuo Ishii
   5  *
   6  * Permission to use, copy, modify, and distribute this software and
   7  * its documentation for any purpose, without fee, and without a
   8  * written agreement is hereby granted, provided that the above
   9  * copyright notice and this paragraph and the following two
  10  * paragraphs appear in all copies.
  11  *
  12  * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
  13  * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
  14  * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
  15  * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
  16  * OF THE POSSIBILITY OF SUCH DAMAGE.
  17  *
  18  * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
  19  * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  20  * A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
  21  * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
  22  * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
  23  */
  24
  25 #include "postgres.h"
  26 #include "fmgr.h"
  27 #include "access/heapam.h"
  28 #include "access/transam.h"
  29
  30 PG_FUNCTION_INFO_V1(pgstattuple);
  31
  32 extern Datum pgstattuple(PG_FUNCTION_ARGS);
  33
  34 /* ----------
  35  * pgstattuple:
  36  * returns the percentage of dead tuples
  37  *
  38  * C FUNCTION definition
  39  * pgstattuple(NAME) returns FLOAT8
  40  * ----------
  41  */
  42 Datum
  43 pgstattuple(PG_FUNCTION_ARGS)
  44 {
  45         Name            p = PG_GETARG_NAME(0);
  46
  47         Relation        rel;
  48         HeapScanDesc scan;
  49         HeapTuple       tuple;
  50         BlockNumber nblocks;
  51         BlockNumber block = InvalidBlockNumber;
  52         double          table_len;
  53         uint64          tuple_len = 0;
  54         uint64          dead_tuple_len = 0;
  55         uint32          tuple_count = 0;
  56         uint32          dead_tuple_count = 0;
  57         double          tuple_percent;
  58         double          dead_tuple_percent;
  59
  60         Buffer          buffer = InvalidBuffer;
  61         uint64          free_space = 0; /* free/reusable space in bytes */
  62         double          free_percent;   /* free/reusable space in % */
  63
  64         rel = heap_openr(NameStr(*p), NoLock);
  65         nblocks = RelationGetNumberOfBlocks(rel);
  66         scan = heap_beginscan(rel, false, SnapshotAny, 0, NULL);
  67
  68         while ((tuple = heap_getnext(scan, 0)))
  69         {
  70                 if (HeapTupleSatisfiesNow(tuple->t_data))
  71                 {
  72                         tuple_len += tuple->t_len;
  73                         tuple_count++;
  74                 }
  75                 else
  76                 {
  77                         dead_tuple_len += tuple->t_len;
  78                         dead_tuple_count++;
  79                 }
  80
  81                 if (!BlockNumberIsValid(block) ||
  82                         block != BlockIdGetBlockNumber(&tuple->t_self.ip_blkid))
  83                 {
  84                         block = BlockIdGetBlockNumber(&tuple->t_self.ip_blkid);
  85                         buffer = ReadBuffer(rel, block);
  86                         free_space += PageGetFreeSpace((Page) BufferGetPage(buffer));
  87                         ReleaseBuffer(buffer);
  88                 }
  89         }
  90         heap_endscan(scan);
  91         heap_close(rel, NoLock);
  92
  93         table_len = (double) nblocks *BLCKSZ;
  94
  95         if (nblocks == 0)
  96         {
  97                 tuple_percent = 0.0;
  98                 dead_tuple_percent = 0.0;
  99                 free_percent = 0.0;
 100         }
 101         else
 102         {
 103                 tuple_percent = (double) tuple_len *100.0 / table_len;
 104                 dead_tuple_percent = (double) dead_tuple_len *100.0 / table_len;
 105                 free_percent = (double) free_space *100.0 / table_len;
 106         }
 107
 108         elog(NOTICE, "physical length: %.2fMB live tuples: %u (%.2fMB, %.2f%%) dead tuples: %u (%.2fMB, %.2f%%) free/reusable space: %.2fMB (%.2f%%) overhead: %.2f%%",
 109
 110                  table_len / 1024 / 1024,               /* phsical length in MB */
 111
 112                  tuple_count,                   /* number of live tuples */
 113                  (double) tuple_len / 1024 / 1024,              /* live tuples in MB */
 114                  tuple_percent,                 /* live tuples in % */
 115
 116                  dead_tuple_count,              /* number of dead tuples */
 117                  (double) dead_tuple_len / 1024 / 1024, /* dead tuples in MB */
 118                  dead_tuple_percent,    /* dead tuples in % */
 119
 120                  (double) free_space / 1024 / 1024,             /* free/available space in
 121                                                                                                  * MB */
 122
 123                  free_percent,                  /* free/available space in % */
 124
 125         /* overhead in % */
 126                  (nblocks == 0) ? 0.0 : 100.0
 127                  - tuple_percent
 128                  - dead_tuple_percent
 129                  - free_percent);
 130
 131         PG_RETURN_FLOAT8(dead_tuple_percent);
 132 }