pgstattuple-stats_target.patch
text/x-patch
Filename: pgstattuple-stats_target.patch
Type: text/x-patch
Part: 2
Message:
Re: Measuring relation free space
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
| File | + | − |
|---|---|---|
| contrib/pgstattuple/pgstattuple--1.1.sql | 0 | 0 |
| contrib/pgstattuple/pgstattuple.c | 0 | 0 |
diff --git a/contrib/pgstattuple/pgstattuple--1.1.sql b/contrib/pgstattuple/pgstattuple--1.1.sql
index d94c20f..08507c1 100644
*** a/contrib/pgstattuple/pgstattuple--1.1.sql
--- b/contrib/pgstattuple/pgstattuple--1.1.sql
*************** LANGUAGE C STRICT;
*** 51,57 ****
--
-- relation_free_space()
--
! CREATE FUNCTION relation_free_space(IN relname text)
RETURNS real
AS 'MODULE_PATHNAME', 'relation_free_space'
LANGUAGE C STRICT;
--- 51,57 ----
--
-- relation_free_space()
--
! CREATE FUNCTION relation_free_space(IN relname text, IN stats_target int DEFAULT 100)
RETURNS real
AS 'MODULE_PATHNAME', 'relation_free_space'
LANGUAGE C STRICT;
diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c
index 1001035..8649b6a 100644
*** a/contrib/pgstattuple/pgstattuple.c
--- b/contrib/pgstattuple/pgstattuple.c
*************** static Datum pgstat_index(Relation rel,
*** 86,93 ****
static void pgstat_index_page(pgstattuple_type *stat, Page page,
OffsetNumber minoff, OffsetNumber maxoff, bool enable_counters);
! static float4 GetHeapRelationFreeSpace(Relation rel);
! static float4 GetIndexRelationFreeSpace(Relation rel);
/*
* Buffer access strategy for reading relations, it's simpler to keep it
--- 86,94 ----
static void pgstat_index_page(pgstattuple_type *stat, Page page,
OffsetNumber minoff, OffsetNumber maxoff, bool enable_counters);
! static float4 GetHeapRelationFreeSpace(Relation rel, int32 stats_target);
! static float4 GetIndexRelationFreeSpace(Relation rel, int32 stats_target);
! static BlockNumber get_next_block(BlockNumber blkno, BlockNumber nblocks, int blocks_read, int32 stats_target);
/*
* Buffer access strategy for reading relations, it's simpler to keep it
*************** Datum
*** 621,630 ****
--- 622,637 ----
relation_free_space(PG_FUNCTION_ARGS)
{
text *relname = PG_GETARG_TEXT_P(0);
+ int32 stats_target = PG_GETARG_INT32(1);
RangeVar *relrv;
Relation rel;
float4 free_space = 0;
+ if (stats_target < 1 || stats_target > 100)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("parameter stats_target should be between 1 and 100")));
+
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
*************** relation_free_space(PG_FUNCTION_ARGS)
*** 651,660 ****
{
case RELKIND_RELATION:
case RELKIND_TOASTVALUE:
! free_space = GetHeapRelationFreeSpace(rel);
break;
case RELKIND_INDEX:
! free_space = GetIndexRelationFreeSpace(rel);
break;
default:
/* we are not supposed to get here */
--- 658,667 ----
{
case RELKIND_RELATION:
case RELKIND_TOASTVALUE:
! free_space = GetHeapRelationFreeSpace(rel, stats_target);
break;
case RELKIND_INDEX:
! free_space = GetIndexRelationFreeSpace(rel, stats_target);
break;
default:
/* we are not supposed to get here */
*************** relation_free_space(PG_FUNCTION_ARGS)
*** 675,686 ****
* This is a helper function for relation_free_space()
*/
static float4
! GetHeapRelationFreeSpace(Relation rel)
{
Buffer buffer;
Page page;
BlockNumber blkno = 0;
BlockNumber nblocks;
Size free_space = 0;
double free_percent = 0;
--- 682,695 ----
* This is a helper function for relation_free_space()
*/
static float4
! GetHeapRelationFreeSpace(Relation rel, int32 stats_target)
{
Buffer buffer;
Page page;
BlockNumber blkno = 0;
BlockNumber nblocks;
+ BlockNumber totalBlocksInRelation;
+ int blocks_read = 0;
Size free_space = 0;
double free_percent = 0;
*************** GetHeapRelationFreeSpace(Relation rel)
*** 691,716 ****
{
/* Get the current relation length */
LockRelationForExtension(rel, ExclusiveLock);
! nblocks = RelationGetNumberOfBlocks(rel);
UnlockRelationForExtension(rel, ExclusiveLock);
/* Quit if we've scanned the whole relation */
if (blkno >= nblocks)
{
break;
}
! for (; blkno < nblocks; blkno++)
{
CHECK_FOR_INTERRUPTS();
buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
page = BufferGetPage(buffer);
free_space += PageGetHeapFreeSpace(page);
UnlockReleaseBuffer(buffer);
}
}
--- 700,733 ----
{
/* Get the current relation length */
LockRelationForExtension(rel, ExclusiveLock);
! totalBlocksInRelation = RelationGetNumberOfBlocks(rel);
UnlockRelationForExtension(rel, ExclusiveLock);
+ nblocks = totalBlocksInRelation * stats_target / 100;
+
/* Quit if we've scanned the whole relation */
if (blkno >= nblocks)
{
break;
}
! for (; blkno < nblocks; )
{
CHECK_FOR_INTERRUPTS();
buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
+ blocks_read++;
page = BufferGetPage(buffer);
free_space += PageGetHeapFreeSpace(page);
UnlockReleaseBuffer(buffer);
+
+ if (stats_target == 100)
+ blkno++;
+ else
+ blkno = get_next_block(blkno, totalBlocksInRelation, blocks_read, stats_target);
}
}
*************** GetHeapRelationFreeSpace(Relation rel)
*** 728,737 ****
*------------------------------------------------
*/
static float4
! GetIndexRelationFreeSpace(Relation rel)
{
- BlockNumber nblocks;
BlockNumber blkno;
pgstattuple_type stat = {0};
double free_percent = 0;
--- 745,756 ----
*------------------------------------------------
*/
static float4
! GetIndexRelationFreeSpace(Relation rel, int32 stats_target)
{
BlockNumber blkno;
+ BlockNumber nblocks;
+ BlockNumber totalBlocksInRelation;
+ int blocks_read = 0;
pgstattuple_type stat = {0};
double free_percent = 0;
*************** GetIndexRelationFreeSpace(Relation rel)
*** 764,779 ****
{
/* Get the current relation length */
LockRelationForExtension(rel, ExclusiveLock);
! nblocks = RelationGetNumberOfBlocks(rel);
UnlockRelationForExtension(rel, ExclusiveLock);
/* Quit if we've scanned the whole relation */
if (blkno >= nblocks)
{
break;
}
! for (; blkno < nblocks; blkno++)
{
CHECK_FOR_INTERRUPTS();
--- 783,800 ----
{
/* Get the current relation length */
LockRelationForExtension(rel, ExclusiveLock);
! totalBlocksInRelation = RelationGetNumberOfBlocks(rel);
UnlockRelationForExtension(rel, ExclusiveLock);
+ nblocks = totalBlocksInRelation * stats_target / 100;
+
/* Quit if we've scanned the whole relation */
if (blkno >= nblocks)
{
break;
}
! for (; blkno < nblocks; )
{
CHECK_FOR_INTERRUPTS();
*************** GetIndexRelationFreeSpace(Relation rel)
*** 798,803 ****
--- 819,830 ----
elog(ERROR, "Unknown index type %d", rel->rd_rel->relam);
break;
}
+ blocks_read++;
+
+ if (stats_target == 100)
+ blkno++;
+ else
+ get_next_block(blkno, totalBlocksInRelation, blocks_read, stats_target);
}
}
*************** GetIndexRelationFreeSpace(Relation rel)
*** 806,808 ****
--- 833,870 ----
return free_percent;
}
+
+ static BlockNumber
+ get_next_block(BlockNumber blkno, BlockNumber nblocks, int blocks_read, int32 stats_target)
+ {
+ BlockNumber K = nblocks - blkno; /* remaining blocks */
+ int k = (nblocks * stats_target / 100) - blocks_read; /* blocks still to sample */
+ double p; /* probability to skip block */
+ double V;
+
+ if (k > K)
+ {
+ /* need all the rest */
+ return blkno + 1;
+ }
+
+ V = ((double) random() + 1) / ((double) MAX_RANDOM_VALUE + 2);
+ p = 1.0 - (double) k / (double) K;
+ while (V < p)
+ {
+ if (k > K)
+ {
+ /* need all the rest */
+ return blkno + 1;
+ }
+
+ /* skip */
+ blkno++;
+ K--; /* keep K == N - t */
+
+ /* adjust p to be new cutoff point in reduced range */
+ p *= 1.0 - (double) k / (double) K;
+ }
+
+ return blkno + 1;
+ }