Обсуждение: Tupple statistics function

Hi,

I have written a small function that show how many tuples are dead
etc. in a specified table. Example output is:

test=# select pgstattuple('tellers');
NOTICE:  physical length: 0.02MB live tuples: 200 (0.01MB, 58.59%) dead tuples: 100 (0.00MB, 29.30%) overhead:
12.11%pgstattuple
 
-------------  29.296875
(1 row)

Shall I add this function into contrib directory?
--
Tatsuo Ishii

> Hi,
> 
> I have written a small function that show how many tuples are dead
> etc. in a specified table. Example output is:
> 
> test=# select pgstattuple('tellers');
> NOTICE:  physical length: 0.02MB live tuples: 200 (0.01MB, 58.59%) dead tuples: 100 (0.00MB, 29.30%) overhead:
12.11%
>  pgstattuple 
> -------------
>    29.296875
> (1 row)
> 
> Shall I add this function into contrib directory?

I have been wanting this for a long time.  In fact, I wanted it linked
to VACUUM so you could vacuum a table only if it had >X% dead tuples. 
Seems we can find a place for this in the existing commands.  Not sure
where, though.  Ideas?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> I have written a small function that show how many tuples are dead
> etc. in a specified table.

Dead according to whose viewpoint?  Under MVCC this seems to be
in the eye of the beholder...

> Shall I add this function into contrib directory?

No real objection, but you should carefully document exactly what
the results mean.

BTW, I'd suggest accounting for free, reusable space separately from
"overhead".
        regards, tom lane

> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > I have written a small function that show how many tuples are dead
> > etc. in a specified table.
> 
> Dead according to whose viewpoint?  Under MVCC this seems to be
> in the eye of the beholder...

You can know if the tuple is visible to other backends, or at least take
a good guess like VACUUM does.  Maybe he has coded that in there.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> ... Maybe he has coded that in there.

Maybe so, but he didn't say.  That's why I was asking for exact
documentation.
        regards, tom lane

> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > I have written a small function that show how many tuples are dead
> > etc. in a specified table.
> 
> Dead according to whose viewpoint?  Under MVCC this seems to be
> in the eye of the beholder...
> 
> > Shall I add this function into contrib directory?
> 
> No real objection, but you should carefully document exactly what
> the results mean.
> 
> BTW, I'd suggest accounting for free, reusable space separately from
> "overhead".
> 
>             regards, tom lane

Ok, here are the source code...

/** $Header: /home/t-ishii/repository/pgstattuple/pgstattuple.c,v 1.2 2001/08/30 06:21:48 t-ishii Exp $** Copyright (c)
2001 Tatsuo Ishii** Permission to use, copy, modify, and distribute this software and* its documentation for any
purpose,without fee, and without a* written agreement is hereby granted, provided that the above* copyright notice and
thisparagraph and the following two* paragraphs appear in all copies.*/
 

#include "postgres.h"
#include "fmgr.h"
#include "access/heapam.h"
#include "access/transam.h"

PG_FUNCTION_INFO_V1(pgstattuple);

extern Datum pgstattuple(PG_FUNCTION_ARGS);

/* ----------* pgstattuple:* returns the percentage of dead tuples** C FUNCTION definition* pgstattuple(NAME) returns
FLOAT8*----------*/
 
Datum
pgstattuple(PG_FUNCTION_ARGS)
{   Name    p = PG_GETARG_NAME(0);
   Relation    rel;   HeapScanDesc    scan;   HeapTuple    tuple;   BlockNumber nblocks;   double    table_len;
uint64   tuple_len = 0;   uint64    dead_tuple_len = 0;   uint32    tuple_count = 0;   uint32    dead_tuple_count = 0;
double    tuple_percent;   double    dead_tuple_percent;
 
   rel = heap_openr(NameStr(*p), NoLock);   nblocks = RelationGetNumberOfBlocks(rel);   scan = heap_beginscan(rel,
false,SnapshotAny, 0, NULL);
 
   while ((tuple = heap_getnext(scan,0)))   {if (HeapTupleSatisfiesNow(tuple->t_data)){    tuple_len += tuple->t_len;
tuple_count++;}else{    dead_tuple_len += tuple->t_len;    dead_tuple_count++;}   }   heap_endscan(scan);
heap_close(rel,NoLock);
 
   table_len = (double)nblocks*BLCKSZ;
   if (nblocks == 0)   {tuple_percent = 0.0;dead_tuple_percent = 0.0;   }   else   {tuple_percent =
(double)tuple_len*100.0/table_len;dead_tuple_percent= (double)dead_tuple_len*100.0/table_len;   }
 
   elog(NOTICE,"physical length: %.2fMB live tuples: %u (%.2fMB, %.2f%%) dead tuples: %u (%.2fMB, %.2f%%) overhead:
%.2f%%",
 table_len/1024/1024,
 tuple_count, (double)tuple_len/1024/1024, tuple_percent,
 dead_tuple_count, (double)dead_tuple_len/1024/1024, dead_tuple_percent,
 (nblocks == 0)?0.0: 100.0 - tuple_percent - dead_tuple_percent);
   PG_RETURN_FLOAT8(dead_tuple_percent);
}

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > ... Maybe he has coded that in there.
> 
> Maybe so, but he didn't say.  That's why I was asking for exact
> documentation.

As you can see from the source code, it just use
HeapTupleSatisfiesNow(). I wrote this function for the admin
use. He/she should know if active transactions are touching the table,
I think. But more precise guess might be interesting for some cases.
--
Tatsuo Ishii