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

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