|
| 1 | +/* |
| 2 | + * $Header: /cvsroot/pgsql/contrib/pgstattuple/pgstattuple.c,v 1.1 2001/10/01 01:52:38 ishii 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 | +externDatumpgstattuple(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 | +Namep=PG_GETARG_NAME(0); |
| 46 | + |
| 47 | +Relationrel; |
| 48 | +HeapScanDescscan; |
| 49 | +HeapTupletuple; |
| 50 | +BlockNumbernblocks; |
| 51 | +BlockNumberblock=InvalidBlockNumber; |
| 52 | +doubletable_len; |
| 53 | +uint64tuple_len=0; |
| 54 | +uint64dead_tuple_len=0; |
| 55 | +uint32tuple_count=0; |
| 56 | +uint32dead_tuple_count=0; |
| 57 | +doubletuple_percent; |
| 58 | +doubledead_tuple_percent; |
| 59 | + |
| 60 | +Bufferbuffer=InvalidBuffer; |
| 61 | +uint64free_space=0;/* free/reusable space in bytes */ |
| 62 | +doublefree_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 MB */ |
| 121 | + |
| 122 | +free_percent,/* free/available space in % */ |
| 123 | + |
| 124 | +/* overhead in % */ |
| 125 | + (nblocks==0)?0.0:100.0 |
| 126 | +-tuple_percent |
| 127 | +-dead_tuple_percent |
| 128 | +-free_percent); |
| 129 | + |
| 130 | +PG_RETURN_FLOAT8(dead_tuple_percent); |
| 131 | +} |