|
| 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 | +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 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 | +} |
0 commit comments