pgstattuple README2001/10/01 Tatsuo Ishii

pgstattuple README2002/08/22 Tatsuo Ishii

1. What is pgstattuple?

pgstattuple returns the percentage of the "dead" tuples of a

table. This will help users to judge if vacuum is needed.

In addition, pgstattuple prints more detailed information using

NOTICE.

test=# select pgstattuple('tellers');

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%

pgstattuple

-------------

18.75

(1 row)

Above example shows tellers table includes 18.75% dead tuples.

physical lengthphysical size of the table in MB

live tuplesinformation on the live tuples

dead tuplesinformation on the dead tuples

free/reusable spaceavailable space

overheadoverhead space

pgstattuple returns the table length, percentage of the "dead"

tuples of a table and other info. This may help users to determine

whether vacuum is necessary or not. Here is an example session:

test=# \x

Expanded display is on.

test=# select * from pgstattuple('pg_proc');

-[ RECORD 1 ]------+-------

table_len | 458752

tuple_count | 1470

tuple_len | 438896

tuple_percent | 95.67

dead_tuple_count | 11

dead_tuple_len | 3157

dead_tuple_percent | 0.69

free_space | 8932

free_percent | 1.95

Here are explanations for each column:

table_len-- physical table length in bytes

tuple_count-- number of live tuples

tuple_len-- total tuples length in bytes

tuple_percent-- live tuples in %

dead_tuple_len-- total dead tuples length in bytes

dead_tuple_percent-- dead tuples in %

free_space-- free space in bytes

free_percent-- free space in %

2. Installing pgstattuple

3. Using pgstattuple

pgstattuple can be called as a function:

pgstattuple may be called as a SRF (set returning function) and is

defined as follows:

pgstattuple(TEXT) RETURNS FLOAT8

CREATE OR REPLACE FUNCTION pgstattuple(text) RETURNS SETOF pgstattuple_view

AS 'MODULE_PATHNAME', 'pgstattuple'

LANGUAGE 'c' WITH (isstrict);

The argument is the table name.pgstattuple returns the percentage

of the "dead" tuples of a table.

The argument is the table name.Note that pgstattuple never

returns more than 1 tuple.

4. Notes

$Id: README.pgstattuple.euc_jp,v 1.2 2001/12/20 01:56:08 ishii Exp $

$Id: README.pgstattuple.euc_jp,v 1.3 2002/08/23 08:19:49 ishii Exp $

pgstattuple README2001/10/01 �а�ã��

pgstattuple README2002/08/22 �а�ã��

1. pgstattuple�Ȥ�

pgstattuple�ϡ�UPDATE��DELETE�Ǻ��줿�ơ��֥�Υ����ΰ���礭����

�ơ��֥뼫�Τ�ʪ��Ū���礭�����Ф���ѡ�����ơ������ֵѤ��ޤ�����

�ޤꡤ�ֵ��ͤ��礭����С�����������ߤ�¿���Τǡ�vacuum�򤫤���ɬ

�פ�����Ȥ���Ƚ�Ǥν����ˤʤ�櫓�Ǥ���

��������ǤϾ����̤����ʤ��Τǡ�NOTICE��å������Ǥ�����ʾ�����

���Ǥ˽��Ϥ��ޤ���

test=# select pgstattuple('tellers');

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%

pgstattuple

-------------

18.75

(1 row)

���μ¹���Ǥϡ�19%�ۤɤ����ߤˤʤäƤ��뤳�Ȥ��Ǥ��ޤ���NOTICE���

�������θ�����񤤤Ƥ����ޤ���

physical length�ơ��֥��ʪ��Ū�ʥ�������MBñ�̤�ɽ��

live tuples���ߤǤϤʤ����ץ�˴ؤ�����󡥥��ץ������

���ץ뤬�����ΰ�ι�ס��ơ��֥����Τ��Ф���

��Ψ��ɽ�����ޤ���

dead tuples���ߤˤʤä����ץ�˴ؤ������

free/reusable space���Ѳ�ǽ��̤�����ΰ������Ѳ�ǽ�ΰ�

overhead�����Τ�����ΰ褬�ơ��֥����Τ�������Ψ

�פ�����Ȥ���Ƚ�Ǥν����ˤʤ�櫓�Ǥ�������ʳ��ˤ⤤�������ʾ���

���֤�ޤ���

test=# \x

Expanded display is on.

test=# select * from pgstattuple('pg_proc');

-[ RECORD 1 ]------+-------

table_len | 458752

tuple_count | 1470

tuple_len | 438896

tuple_percent | 95.67

dead_tuple_count | 11

dead_tuple_len | 3157

dead_tuple_percent | 0.69

free_space | 8932

free_percent | 1.95

�ƹ��ܤ������Ǥ���

table_len-- �ơ��֥��ʪ��Ū���礭��(�Х���)

tuple_count-- ���ץ��

tuple_len-- ���ץ�Ĺ�ι��(�Х���)

tuple_percent-- ���ץ�γ�硥table_len���Ф���tuple_len����Ψ��

dead_tuple_len-- �ǥåɥ��ץ��

dead_tuple_percent-- �ǥåɥ��ץ�γ�硥table_len���Ф���tuple_len����Ψ��

free_space-- �����Ѳ�ǽ���ΰ�(�Х���)

free_percent-- �����Ѳ�ǽ���ΰ补table_len���Ф���free_space����Ψ��

2. pgstattuple�Υ��󥹥ȡ���

pgstattuple�θƤӽФ������ϰʲ��Ǥ���

pgstattuple(NAME) RETURNS FLOAT8

CREATE OR REPLACE FUNCTION pgstattuple(text) RETURNS SETOF pgstattuple_view

AS 'MODULE_PATHNAME', 'pgstattuple'

LANGUAGE 'c' WITH (isstrict);

������: �ơ��֥�̾

�ؿ�������UPDATE��DELETE�Ǻ��줿�ơ��֥�Υ����ΰ���礭���ǡ�

�ơ��֥��ʪ��Ū���礭�����Ф�����(�ѡ������)���ֵѤ��ޤ���

�ؿ�������pgstattuple_view���Ǥ����ޤ���pgstattuple��1�Ԥ����Ԥ�

�֤��ޤ���

�ʤ���pgstattuple�ϥơ��֥��AccessShareLock���������ʤ��Τǡ�

pgstattuple ��¹���˳����ơ��֥�˹���������ȯ������ȡ�������

5. ��������

2002/08/23

SRF(Set Returning Function)��Ȥä�7.3�Ѥ˽񤭴�����

2001/12/20 Tom Lane�ˤ�뽤��

Fix pgstattuple to acquire a read lock on the target table. This

#defineDUMMY_TUPLE "pgstattuple_view"

#defineNCOLUMNS 9

#defineNCHARS 32

pgstattuple(PG_FUNCTION_ARGS)

{

text*relname=PG_GETARG_TEXT_P(0);

text*relname;

RangeVar*relrv;

Relationrel;

HeapScanDescscan;

BlockNumberblock=0;/* next block to count free space in */

BlockNumbertupblock;

Bufferbuffer;

doubletable_len;

uint64table_len;

uint64tuple_len=0;

uint64dead_tuple_len=0;

uint64tuple_count=0;

uint64free_space=0;/* free/reusable space in bytes */

doublefree_percent;/* free/reusable space in % */

relrv=makeRangeVarFromNameList(textToQualifiedNameList(relname,

"pgstattuple"));

FuncCallContext*funcctx;

intcall_cntr;

intmax_calls;

TupleDesctupdesc;

TupleTableSlot*slot;

AttInMetadata*attinmeta;

char**values;

inti;

Datumresult;

if(SRF_IS_FIRSTCALL())

{

funcctx=SRF_FIRSTCALL_INIT();

funcctx->max_calls=1;

tupdesc=RelationNameGetTupleDesc(DUMMY_TUPLE);

slot=TupleDescGetSlot(tupdesc);

funcctx->slot=slot;

attinmeta=TupleDescGetAttInMetadata(tupdesc);

funcctx->attinmeta=attinmeta;

}

funcctx=SRF_PERCALL_SETUP();

call_cntr=funcctx->call_cntr;

max_calls=funcctx->max_calls;

slot=funcctx->slot;

attinmeta=funcctx->attinmeta;

if (call_cntr >=max_calls)

{

SRF_RETURN_DONE(funcctx);

}

relname=PG_GETARG_TEXT_P(0);

relrv=makeRangeVarFromNameList(textToQualifiedNameList(relname,"pgstattuple"));

rel=heap_openrv(relrv,AccessShareLock);

nblocks=RelationGetNumberOfBlocks(rel);

scan=heap_beginscan(rel,SnapshotAny,0,NULL);

while ((tuple=heap_getnext(scan,ForwardScanDirection))!=NULL)

{

if (HeapTupleSatisfiesNow(tuple->t_data))

heap_close(rel,AccessShareLock);

table_len= (double)nblocks*BLCKSZ;

table_len= (uint64)nblocks*BLCKSZ;

if (nblocks==0)

{

free_percent= (double)free_space*100.0 /table_len;

}

elog(DEBUG3,"physical length: %.2fMB live tuples: %.0f (%.2fMB, %.2f%%) dead tuples: %.0f (%.2fMB, %.2f%%) free/reusable space: %.2fMB (%.2f%%) overhead: %.2f%%",

table_len / (1024*1024),/* physical length in MB */

(double)tuple_count,/* number of live tuples */

(double)tuple_len / (1024*1024),/* live tuples in MB */

tuple_percent,/* live tuples in % */

(double)dead_tuple_count,/* number of dead tuples */

(double)dead_tuple_len / (1024*1024),/* dead tuples in MB */

dead_tuple_percent,/* dead tuples in % */

(double)free_space / (1024*1024),/* free/available space in

free_percent,/* free/available space in % */

(nblocks==0) ?0.0 :100.0

-free_percent);

PG_RETURN_FLOAT8(dead_tuple_percent);

values= (char**)palloc(NCOLUMNS*sizeof(char*));

for (i=0;i<NCOLUMNS;i++)

{

values[i]= (char*)palloc(NCHARS*sizeof(char));

}

i=0;

snprintf(values[i++],NCHARS,"%lld",table_len);

snprintf(values[i++],NCHARS,"%lld",tuple_count);

snprintf(values[i++],NCHARS,"%lld",tuple_len);

snprintf(values[i++],NCHARS,"%.2f",tuple_percent);

snprintf(values[i++],NCHARS,"%lld",dead_tuple_count);

snprintf(values[i++],NCHARS,"%lld",dead_tuple_len);

snprintf(values[i++],NCHARS,"%.2f",dead_tuple_percent);

snprintf(values[i++],NCHARS,"%lld",free_space);

snprintf(values[i++],NCHARS,"%.2f",free_percent);

tuple=BuildTupleFromCStrings(attinmeta,values);

result=TupleGetDatum(slot,tuple);

for (i=0;i<NCOLUMNS;i++)

{

pfree(values[i]);

}

pfree(values);

SRF_RETURN_NEXT(funcctx,result);

}

DROP FUNCTION pgstattuple(text);

CREATE FUNCTION pgstattuple(text) RETURNS float8

AS 'MODULE_PATHNAME', 'pgstattuple'

LANGUAGE 'c' WITH (isstrict);

DROP VIEW pgstattuple_view CASCADE;

CREATE VIEW pgstattuple_view AS

SELECT

0::BIGINT AS table_len,-- physical table length in bytes

0::BIGINT AS tuple_count,-- number of live tuples

0::BIGINT AS tuple_len,-- total tuples length in bytes

0.0::FLOAT AS tuple_percent,-- live tuples in %

0::BIGINT AS dead_tuple_count,-- number of dead tuples

0::BIGINT AS dead_tuple_len,-- total dead tuples length in bytes

0.0::FLOAT AS dead_tuple_percent,-- dead tuples in %

0::BIGINT AS free_space,-- free space in bytes

0.0::FLOAT AS free_percent;-- free space in %

CREATE OR REPLACE FUNCTION pgstattuple(text) RETURNS SETOF pgstattuple_view

AS 'MODULE_PATHNAME', 'pgstattuple'

LANGUAGE 'c' WITH (isstrict);