Skip to content

Commit 9a23885

Browse files
tatsuo-ishiitatsuo-ishii
committed
Add pgstattuple
1 parent 95d4821 commit 9a23885

File tree

7 files changed

+280
-1
lines changed

7 files changed

+280
-1
lines changed

‎contrib/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.28 2001/09/29 03:11:58 momjian Exp $
1+
# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.29 2001/10/01 01:52:38 ishii Exp $
22

33
subdir = contrib
44
top_builddir = ..
@@ -27,6 +27,7 @@ WANTED_DIRS = \
2727
pg_resetxlog \
2828
pgbench \
2929
pgcrypto \
30+
pgstattuple \
3031
rserv \
3132
rtree_gist \
3233
seg \

‎contrib/README

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -137,6 +137,10 @@ pgcrypto -
137137
Cryptographic functions
138138
by Marko Kreen <marko@l-t.ee>
139139

140+
pgstattuple -
141+
A function returns the percentage of "dead" tuples in a table
142+
by Tatsuo Ishii <t-ishii@sra.co.jp>
143+
140144
retep -
141145
tools to build retep tools packages
142146
by Peter T Mount <peter@retep.org.uk>

‎contrib/pgstattuple/Makefile

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
#-------------------------------------------------------------------------
2+
#
3+
# pgstattuple Makefile
4+
#
5+
# $Id: Makefile,v 1.1 2001/10/01 01:52:38 ishii Exp $
6+
#
7+
#-------------------------------------------------------------------------
8+
9+
subdir = contrib/pgstattuple
10+
top_builddir = ../..
11+
include $(top_builddir)/src/Makefile.global
12+
13+
MODULE_big := pgstattuple
14+
SRCS += pgstattuple.c
15+
OBJS := $(SRCS:.c=.o)
16+
DOCS := README.pgstattuple README.pgstattuple.euc_jp
17+
DATA_built := pgstattuple.sql
18+
19+
PG_CPPFLAGS :=
20+
SHLIB_LINK :=
21+
22+
include $(top_srcdir)/contrib/contrib-global.mk

‎contrib/pgstattuple/README.pgstattuple

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
pgstattuple README 2001/10/01 Tatsuo Ishii
2+
3+
1. What is pgstattuple?
4+
5+
pgstattuple returns the percentage of the "dead" tuples of a
6+
table. This will help users to judge if vacuum is needed.
7+
8+
In addition, pgstattuple prints more detailed information using
9+
NOTICE.
10+
11+
test=# select pgstattuple('tellers');
12+
NOTICE: physical length: 0.08MB live tuples: 20 (0.00MB, 1.17%) dead tuples: 320 (0.01MB, 18.75%) free/reusable space: 0.01MB (18.06%) overhead: 62.02%
13+
pgstattuple
14+
-------------
15+
18.75
16+
(1 row)
17+
18+
Above example shows tellers tables includes 18.75% dead tuples.
19+
20+
physical length physical size of the table in MB
21+
live tuples information on the live tuples
22+
dead tuples information on the dead tuples
23+
free/reusable space available space
24+
overhead overhead space
25+
26+
2. Installing pgstattuple
27+
28+
$ make
29+
$ make install
30+
$ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test
31+
32+
3. Using pgstattuple
33+
34+
pgstattuple can be called as a function:
35+
36+
pgstattuple(NAME) RETURNS FLOAT8
37+
38+
The argument is the table name. pgstattuple returns the percentage
39+
of the "dead" tuples of a table.
40+
41+
4. Notes
42+
43+
pgstattuple does not lock the target table at all. So concurrent
44+
update may affect the result.
45+
46+
pgstattuple judges a tuple is "dead" if HeapTupleSatisfiesNow()
47+
returns false.

‎contrib/pgstattuple/README.pgstattuple.euc_jp

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
$Id: README.pgstattuple.euc_jp,v 1.1 2001/10/01 01:52:38 ishii Exp $
2+
3+
pgstattuple README 2001/10/01 �а�ã��
4+
5+
1. pgstattuple�Ȥ�
6+
7+
pgstattuple�ϡ�UPDATE��DELETE�Ǻ��줿�ơ��֥�Υ����ΰ���礭����
8+
�ơ��֥뼫�Τ�ʪ��Ū���礭�����Ф���ѡ�����ơ������ֵѤ��ޤ�����
9+
�ޤꡤ�ֵ��ͤ��礭����С�����������ߤ�¿���Τǡ�vacuum�򤫤���ɬ
10+
�פ�����Ȥ���Ƚ�Ǥν����ˤʤ�櫓�Ǥ���
11+
12+
��������ǤϾ����̤����ʤ��Τǡ�NOTICE��å������Ǥ�����ʾ�����
13+
���Ǥ˽��Ϥ��ޤ���
14+
15+
test=# select pgstattuple('tellers');
16+
NOTICE: physical length: 0.08MB live tuples: 20 (0.00MB, 1.17%) dead tuples: 320 (0.01MB, 18.75%) free/reusable space: 0.01MB (18.06%) overhead: 62.02%
17+
pgstattuple
18+
-------------
19+
18.75
20+
(1 row)
21+
22+
���μ¹���Ǥϡ�19%�ۤɤ����ߤˤʤäƤ��뤳�Ȥ��Ǥ��ޤ���NOTICE���
23+
�������θ�����񤤤Ƥ����ޤ���
24+
25+
physical length �ơ��֥��ʪ��Ū�ʥ�������MBñ�̤�ɽ��
26+
live tuples ���ߤǤϤʤ����ץ�˴ؤ�����󡥥��ץ������
27+
���ץ뤬�����ΰ�ι�ס��ơ��֥����Τ��Ф���
28+
��Ψ��ɽ�����ޤ���
29+
dead tuples ���ߤˤʤä����ץ�˴ؤ������
30+
free/reusable space ���Ѳ�ǽ��̤�����ΰ������Ѳ�ǽ�ΰ�
31+
overhead �����Τ�����ΰ褬�ơ��֥����Τ�������Ψ
32+
33+
2. pgstattuple�Υ��󥹥ȡ���
34+
35+
PostgreSQL��/usr/local/pgsql�˥��󥹥ȡ���ѤǤ��ꡤtest�ǡ����١�
36+
����pgstattuple�򥤥󥹥ȡ��뤹����μ��򼨤��ޤ���
37+
38+
$ make
39+
$ make install
40+
41+
�桼������ؿ�����Ͽ���ޤ���
42+
43+
$ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test
44+
45+
46+
3. pgstattuple�λȤ���
47+
48+
pgstattuple�θƤӽФ������ϰʲ��Ǥ���
49+
50+
pgstattuple(NAME) RETURNS FLOAT8
51+
52+
������: �ơ��֥�̾
53+
54+
�ؿ�������UPDATE��DELETE�Ǻ��줿�ơ��֥�Υ����ΰ���礭���ǡ�
55+
�ơ��֥��ʪ��Ū���礭�����Ф�����(�ѡ������)���ֵѤ��ޤ���
56+
57+
�ʤ���pgstattuple�ϥơ��֥�˰��ڥ��å��򤫤��ʤ��Τǡ�pgstattuple
58+
��¹���˳����ơ��֥�˹���������ȯ������ȡ��������ʤ���̤���
59+
����ǽ��������ޤ���
60+
61+
4. pgstattuple�Υ饤���󥹾��ˤĤ���
62+
63+
pgstattuple.c����Ƭ�˽񤤤Ƥ����̤�Ǥ����ޤ���pgstattuple �ϴ�����̵��
64+
�ڤǤ���pgstattuple ����Ѥ������Ȥˤ�ä������뤤���ʤ��̤˴ؤ���
65+
����Ǥ���餤�ޤ���
66+
67+
5. ��������
68+
69+
2001/10/01 PostgreSQL 7.2 ��contrib module����Ͽ
70+
2001/08/30 pgstattuple �С������ 0.1��꡼��

‎contrib/pgstattuple/pgstattuple.c

Lines changed: 131 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,131 @@
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+
}

‎contrib/pgstattuple/pgstattuple.sql.in

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
DROP FUNCTION pgstattuple(NAME);
2+
CREATE FUNCTION pgstattuple(NAME) RETURNS FLOAT8
3+
AS 'MODULE_PATHNAME', 'pgstattuple'
4+
LANGUAGE 'c' WITH (isstrict);

0 commit comments

Comments
 (0)