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

   1 /*
   2  * $Header: /cvsroot/pgsql/contrib/pgstattuple/pgstattuple.c,v 1.3 2001/12/19 20:28:41 tgl 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
  27 #include "fmgr.h"
  28 #include "access/heapam.h"
  29 #include "access/transam.h"
  30
  31 PG_FUNCTION_INFO_V1(pgstattuple);
  32
  33 extern Datum pgstattuple(PG_FUNCTION_ARGS);
  34
  35 /* ----------
  36  * pgstattuple:
  37  * returns the percentage of dead tuples
  38  *
  39  * C FUNCTION definition
  40  * pgstattuple(NAME) returns FLOAT8
  41  * ----------
  42  */
  43 Datum
  44 pgstattuple(PG_FUNCTION_ARGS)
  45 {
  46         Name            p = PG_GETARG_NAME(0);
  47
  48         Relation        rel;
  49         HeapScanDesc scan;
  50         HeapTuple       tuple;
  51         BlockNumber nblocks;
  52         BlockNumber block = 0;          /* next block to count free space in */
  53         BlockNumber tupblock;
  54         Buffer          buffer;
  55         double          table_len;
  56         uint64          tuple_len = 0;
  57         uint64          dead_tuple_len = 0;
  58         uint64          tuple_count = 0;
  59         uint64          dead_tuple_count = 0;
  60         double          tuple_percent;
  61         double          dead_tuple_percent;
  62
  63         uint64          free_space = 0; /* free/reusable space in bytes */
  64         double          free_percent;   /* free/reusable space in % */
  65
  66         rel = heap_openr(NameStr(*p), AccessShareLock);
  67         nblocks = RelationGetNumberOfBlocks(rel);
  68         scan = heap_beginscan(rel, false, SnapshotAny, 0, NULL);
  69
  70         while ((tuple = heap_getnext(scan, 0)))
  71         {
  72                 if (HeapTupleSatisfiesNow(tuple->t_data))
  73                 {
  74                         tuple_len += tuple->t_len;
  75                         tuple_count++;
  76                 }
  77                 else
  78                 {
  79                         dead_tuple_len += tuple->t_len;
  80                         dead_tuple_count++;
  81                 }
  82
  83                 /*
  84                  * To avoid physically reading the table twice, try to do the
  85                  * free-space scan in parallel with the heap scan.  However,
  86                  * heap_getnext may find no tuples on a given page, so we cannot
  87                  * simply examine the pages returned by the heap scan.
  88                  */
  89                 tupblock = BlockIdGetBlockNumber(&tuple->t_self.ip_blkid);
  90
  91                 while (block <= tupblock)
  92                 {
  93                         buffer = ReadBuffer(rel, block);
  94                         free_space += PageGetFreeSpace((Page) BufferGetPage(buffer));
  95                         ReleaseBuffer(buffer);
  96                         block++;
  97                 }
  98         }
  99         heap_endscan(scan);
 100
 101         while (block < nblocks)
 102         {
 103                 buffer = ReadBuffer(rel, block);
 104                 free_space += PageGetFreeSpace((Page) BufferGetPage(buffer));
 105                 ReleaseBuffer(buffer);
 106                 block++;
 107         }
 108
 109         heap_close(rel, AccessShareLock);
 110
 111         table_len = (double) nblocks *BLCKSZ;
 112
 113         if (nblocks == 0)
 114         {
 115                 tuple_percent = 0.0;
 116                 dead_tuple_percent = 0.0;
 117                 free_percent = 0.0;
 118         }
 119         else
 120         {
 121                 tuple_percent = (double) tuple_len *100.0 / table_len;
 122                 dead_tuple_percent = (double) dead_tuple_len *100.0 / table_len;
 123                 free_percent = (double) free_space *100.0 / table_len;
 124         }
 125
 126         elog(NOTICE, "physical length: %.2fMB live tuples: %.0f (%.2fMB, %.2f%%) dead tuples: %.0f (%.2fMB, %.2f%%) free/reusable space: %.2fMB (%.2f%%) overhead: %.2f%%",
 127
 128                  table_len / (1024 * 1024),             /* physical length in MB */
 129
 130                  (double) tuple_count,  /* number of live tuples */
 131                  (double) tuple_len / (1024 * 1024),            /* live tuples in MB */
 132                  tuple_percent,                 /* live tuples in % */
 133
 134                  (double) dead_tuple_count,     /* number of dead tuples */
 135                  (double) dead_tuple_len / (1024 * 1024), /* dead tuples in MB */
 136                  dead_tuple_percent,    /* dead tuples in % */
 137
 138                  (double) free_space / (1024 * 1024), /* free/available space in
 139                                                                                            * MB */
 140
 141                  free_percent,                  /* free/available space in % */
 142
 143         /* overhead in % */
 144                  (nblocks == 0) ? 0.0 : 100.0
 145                  - tuple_percent
 146                  - dead_tuple_percent
 147                  - free_percent);
 148
 149         PG_RETURN_FLOAT8(dead_tuple_percent);
 150 }