PostgreSQL Source Code git master
All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
pgstatapprox.c
Go to the documentation of this file.
1/*-------------------------------------------------------------------------
2 *
3 * pgstatapprox.c
4 * Bloat estimation functions
5 *
6 * Copyright (c) 2014-2024, PostgreSQL Global Development Group
7 *
8 * IDENTIFICATION
9 * contrib/pgstattuple/pgstatapprox.c
10 *
11 *-------------------------------------------------------------------------
12 */
13#include "postgres.h"
14
15#include "access/heapam.h"
16#include "access/htup_details.h"
17#include "access/relation.h"
19#include "catalog/pg_am_d.h"
20#include "commands/vacuum.h"
21#include "funcapi.h"
22#include "miscadmin.h"
23#include "storage/bufmgr.h"
24#include "storage/freespace.h"
25#include "storage/procarray.h"
26
29
31
32typedef struct output_type
33{
45
46#define NUM_OUTPUT_COLUMNS 10
47
48/*
49 * This function takes an already open relation and scans its pages,
50 * skipping those that have the corresponding visibility map bit set.
51 * For pages we skip, we find the free space from the free space map
52 * and approximate tuple_len on that basis. For the others, we count
53 * the exact number of dead tuples etc.
54 *
55 * This scan is loosely based on vacuumlazy.c:lazy_scan_heap(), but
56 * we do not try to avoid skipping single pages.
57 */
58static void
60{
61 BlockNumber scanned,
62 nblocks,
63 blkno;
64 Buffer vmbuffer = InvalidBuffer;
65 BufferAccessStrategy bstrategy;
66 TransactionId OldestXmin;
67
68 OldestXmin = GetOldestNonRemovableTransactionId(rel);
70
71 nblocks = RelationGetNumberOfBlocks(rel);
72 scanned = 0;
73
74 for (blkno = 0; blkno < nblocks; blkno++)
75 {
76 Buffer buf;
77 Page page;
78 OffsetNumber offnum,
79 maxoff;
80 Size freespace;
81
83
84 /*
85 * If the page has only visible tuples, then we can find out the free
86 * space from the FSM and move on.
87 */
88 if (VM_ALL_VISIBLE(rel, blkno, &vmbuffer))
89 {
90 freespace = GetRecordedFreeSpace(rel, blkno);
91 stat->tuple_len += BLCKSZ - freespace;
92 stat->free_space += freespace;
93 continue;
94 }
95
97 RBM_NORMAL, bstrategy);
98
100
101 page = BufferGetPage(buf);
102
103 stat->free_space += PageGetExactFreeSpace(page);
104
105 /* We may count the page as scanned even if it's new/empty */
106 scanned++;
107
108 if (PageIsNew(page) || PageIsEmpty(page))
109 {
111 continue;
112 }
113
114 /*
115 * Look at each tuple on the page and decide whether it's live or
116 * dead, then count it and its size. Unlike lazy_scan_heap, we can
117 * afford to ignore problems and special cases.
118 */
119 maxoff = PageGetMaxOffsetNumber(page);
120
121 for (offnum = FirstOffsetNumber;
122 offnum <= maxoff;
123 offnum = OffsetNumberNext(offnum))
124 {
125 ItemId itemid;
126 HeapTupleData tuple;
127
128 itemid = PageGetItemId(page, offnum);
129
130 if (!ItemIdIsUsed(itemid) || ItemIdIsRedirected(itemid) ||
131 ItemIdIsDead(itemid))
132 {
133 continue;
134 }
135
136 Assert(ItemIdIsNormal(itemid));
137
138 ItemPointerSet(&(tuple.t_self), blkno, offnum);
139
140 tuple.t_data = (HeapTupleHeader) PageGetItem(page, itemid);
141 tuple.t_len = ItemIdGetLength(itemid);
142 tuple.t_tableOid = RelationGetRelid(rel);
143
144 /*
145 * We follow VACUUM's lead in counting INSERT_IN_PROGRESS tuples
146 * as "dead" while DELETE_IN_PROGRESS tuples are "live". We don't
147 * bother distinguishing tuples inserted/deleted by our own
148 * transaction.
149 */
150 switch (HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf))
151 {
152 case HEAPTUPLE_LIVE:
154 stat->tuple_len += tuple.t_len;
155 stat->tuple_count++;
156 break;
157 case HEAPTUPLE_DEAD:
160 stat->dead_tuple_len += tuple.t_len;
161 stat->dead_tuple_count++;
162 break;
163 default:
164 elog(ERROR, "unexpected HeapTupleSatisfiesVacuum result");
165 break;
166 }
167 }
168
170 }
171
172 stat->table_len = (uint64) nblocks * BLCKSZ;
173
174 /*
175 * We don't know how many tuples are in the pages we didn't scan, so
176 * extrapolate the live-tuple count to the whole table in the same way
177 * that VACUUM does. (Like VACUUM, we're not taking a random sample, so
178 * just extrapolating linearly seems unsafe.) There should be no dead
179 * tuples in all-visible pages, so no correction is needed for that, and
180 * we already accounted for the space in those pages, too.
181 */
182 stat->tuple_count = vac_estimate_reltuples(rel, nblocks, scanned,
183 stat->tuple_count);
184
185 /* It's not clear if we could get -1 here, but be safe. */
186 stat->tuple_count = Max(stat->tuple_count, 0);
187
188 /*
189 * Calculate percentages if the relation has one or more pages.
190 */
191 if (nblocks != 0)
192 {
193 stat->scanned_percent = 100.0 * scanned / nblocks;
194 stat->tuple_percent = 100.0 * stat->tuple_len / stat->table_len;
195 stat->dead_tuple_percent = 100.0 * stat->dead_tuple_len / stat->table_len;
196 stat->free_percent = 100.0 * stat->free_space / stat->table_len;
197 }
198
199 if (BufferIsValid(vmbuffer))
200 {
201 ReleaseBuffer(vmbuffer);
202 vmbuffer = InvalidBuffer;
203 }
204}
205
206/*
207 * Returns estimated live/dead tuple statistics for the given relid.
208 *
209 * The superuser() check here must be kept as the library might be upgraded
210 * without the extension being upgraded, meaning that in pre-1.5 installations
211 * these functions could be called by any user.
212 */
213Datum
215{
216 Oid relid = PG_GETARG_OID(0);
217
218 if (!superuser())
220 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
221 errmsg("must be superuser to use pgstattuple functions")));
222
224}
225
226/*
227 * As of pgstattuple version 1.5, we no longer need to check if the user
228 * is a superuser because we REVOKE EXECUTE on the SQL function from PUBLIC.
229 * Users can then grant access to it based on their policies.
230 *
231 * Otherwise identical to pgstattuple_approx (above).
232 */
233Datum
235{
236 Oid relid = PG_GETARG_OID(0);
237
239}
240
241Datum
243{
244 Relation rel;
245 output_type stat = {0};
246 TupleDesc tupdesc;
247 bool nulls[NUM_OUTPUT_COLUMNS];
249 HeapTuple ret;
250 int i = 0;
251
252 if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
253 elog(ERROR, "return type must be a row type");
254
255 if (tupdesc->natts != NUM_OUTPUT_COLUMNS)
256 elog(ERROR, "incorrect number of output arguments");
257
258 rel = relation_open(relid, AccessShareLock);
259
260 /*
261 * Reject attempts to read non-local temporary relations; we would be
262 * likely to get wrong data since we have no visibility into the owning
263 * session's local buffers.
264 */
265 if (RELATION_IS_OTHER_TEMP(rel))
267 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
268 errmsg("cannot access temporary tables of other sessions")));
269
270 /*
271 * We support only relation kinds with a visibility map and a free space
272 * map.
273 */
274 if (!(rel->rd_rel->relkind == RELKIND_RELATION ||
275 rel->rd_rel->relkind == RELKIND_MATVIEW ||
276 rel->rd_rel->relkind == RELKIND_TOASTVALUE))
278 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
279 errmsg("relation \"%s\" is of wrong relation kind",
282
283 if (rel->rd_rel->relam != HEAP_TABLE_AM_OID)
284 ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
285 errmsg("only heap AM is supported")));
286
287 statapprox_heap(rel, &stat);
288
290
291 memset(nulls, 0, sizeof(nulls));
292
293 values[i++] = Int64GetDatum(stat.table_len);
294 values[i++] = Float8GetDatum(stat.scanned_percent);
295 values[i++] = Int64GetDatum(stat.tuple_count);
296 values[i++] = Int64GetDatum(stat.tuple_len);
297 values[i++] = Float8GetDatum(stat.tuple_percent);
298 values[i++] = Int64GetDatum(stat.dead_tuple_count);
299 values[i++] = Int64GetDatum(stat.dead_tuple_len);
300 values[i++] = Float8GetDatum(stat.dead_tuple_percent);
301 values[i++] = Int64GetDatum(stat.free_space);
302 values[i++] = Float8GetDatum(stat.free_percent);
303
304 ret = heap_form_tuple(tupdesc, values, nulls);
305 return HeapTupleGetDatum(ret);
306}
uint32 BlockNumber
Definition: block.h:31
static Datum values[MAXATTR]
Definition: bootstrap.c:151
int Buffer
Definition: buf.h:23
#define InvalidBuffer
Definition: buf.h:25
void ReleaseBuffer(Buffer buffer)
Definition: bufmgr.c:4924
void UnlockReleaseBuffer(Buffer buffer)
Definition: bufmgr.c:4941
void LockBuffer(Buffer buffer, int mode)
Definition: bufmgr.c:5158
Buffer ReadBufferExtended(Relation reln, ForkNumber forkNum, BlockNumber blockNum, ReadBufferMode mode, BufferAccessStrategy strategy)
Definition: bufmgr.c:793
@ BAS_BULKREAD
Definition: bufmgr.h:36
#define BUFFER_LOCK_SHARE
Definition: bufmgr.h:190
#define RelationGetNumberOfBlocks(reln)
Definition: bufmgr.h:273
static Page BufferGetPage(Buffer buffer)
Definition: bufmgr.h:400
@ RBM_NORMAL
Definition: bufmgr.h:45
static bool BufferIsValid(Buffer bufnum)
Definition: bufmgr.h:351
Size PageGetExactFreeSpace(Page page)
Definition: bufpage.c:947
static bool PageIsEmpty(Page page)
Definition: bufpage.h:223
Pointer Page
Definition: bufpage.h:81
static Item PageGetItem(Page page, ItemId itemId)
Definition: bufpage.h:354
static ItemId PageGetItemId(Page page, OffsetNumber offsetNumber)
Definition: bufpage.h:243
static bool PageIsNew(Page page)
Definition: bufpage.h:233
static OffsetNumber PageGetMaxOffsetNumber(Page page)
Definition: bufpage.h:372
#define Max(x, y)
Definition: c.h:952
#define Assert(condition)
Definition: c.h:812
uint64_t uint64
Definition: c.h:486
uint32 TransactionId
Definition: c.h:606
size_t Size
Definition: c.h:559
int errcode(int sqlerrcode)
Definition: elog.c:853
int errmsg(const char *fmt,...)
Definition: elog.c:1070
#define ERROR
Definition: elog.h:39
#define elog(elevel,...)
Definition: elog.h:225
#define ereport(elevel,...)
Definition: elog.h:149
Datum Int64GetDatum(int64 X)
Definition: fmgr.c:1807
Datum Float8GetDatum(float8 X)
Definition: fmgr.c:1816
#define PG_GETARG_OID(n)
Definition: fmgr.h:275
#define PG_RETURN_DATUM(x)
Definition: fmgr.h:353
#define PG_FUNCTION_ARGS
Definition: fmgr.h:193
BufferAccessStrategy GetAccessStrategy(BufferAccessStrategyType btype)
Definition: freelist.c:541
Size GetRecordedFreeSpace(Relation rel, BlockNumber heapBlk)
Definition: freespace.c:244
TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo, Oid *resultTypeId, TupleDesc *resultTupleDesc)
Definition: funcapi.c:276
@ TYPEFUNC_COMPOSITE
Definition: funcapi.h:149
static Datum HeapTupleGetDatum(const HeapTupleData *tuple)
Definition: funcapi.h:230
@ HEAPTUPLE_RECENTLY_DEAD
Definition: heapam.h:128
@ HEAPTUPLE_INSERT_IN_PROGRESS
Definition: heapam.h:129
@ HEAPTUPLE_LIVE
Definition: heapam.h:127
@ HEAPTUPLE_DELETE_IN_PROGRESS
Definition: heapam.h:130
@ HEAPTUPLE_DEAD
Definition: heapam.h:126
HTSV_Result HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin, Buffer buffer)
HeapTuple heap_form_tuple(TupleDesc tupleDescriptor, const Datum *values, const bool *isnull)
Definition: heaptuple.c:1117
HeapTupleHeaderData * HeapTupleHeader
Definition: htup.h:23
int i
Definition: isn.c:72
#define ItemIdGetLength(itemId)
Definition: itemid.h:59
#define ItemIdIsNormal(itemId)
Definition: itemid.h:99
#define ItemIdIsDead(itemId)
Definition: itemid.h:113
#define ItemIdIsUsed(itemId)
Definition: itemid.h:92
#define ItemIdIsRedirected(itemId)
Definition: itemid.h:106
static void ItemPointerSet(ItemPointerData *pointer, BlockNumber blockNumber, OffsetNumber offNum)
Definition: itemptr.h:135
#define AccessShareLock
Definition: lockdefs.h:36
#define CHECK_FOR_INTERRUPTS()
Definition: miscadmin.h:122
#define OffsetNumberNext(offsetNumber)
Definition: off.h:52
uint16 OffsetNumber
Definition: off.h:24
#define FirstOffsetNumber
Definition: off.h:27
int errdetail_relkind_not_supported(char relkind)
Definition: pg_class.c:24
static char * buf
Definition: pg_test_fsync.c:72
static void statapprox_heap(Relation rel, output_type *stat)
Definition: pgstatapprox.c:59
Datum pgstattuple_approx(PG_FUNCTION_ARGS)
Definition: pgstatapprox.c:214
#define NUM_OUTPUT_COLUMNS
Definition: pgstatapprox.c:46
struct output_type output_type
Datum pgstattuple_approx_internal(Oid relid, FunctionCallInfo fcinfo)
Definition: pgstatapprox.c:242
PG_FUNCTION_INFO_V1(pgstattuple_approx)
Datum pgstattuple_approx_v1_5(PG_FUNCTION_ARGS)
Definition: pgstatapprox.c:234
uintptr_t Datum
Definition: postgres.h:64
unsigned int Oid
Definition: postgres_ext.h:31
TransactionId GetOldestNonRemovableTransactionId(Relation rel)
Definition: procarray.c:2005
#define RelationGetRelid(relation)
Definition: rel.h:505
#define RelationGetRelationName(relation)
Definition: rel.h:539
#define RELATION_IS_OTHER_TEMP(relation)
Definition: rel.h:658
@ MAIN_FORKNUM
Definition: relpath.h:58
void relation_close(Relation relation, LOCKMODE lockmode)
Definition: relation.c:205
Relation relation_open(Oid relationId, LOCKMODE lockmode)
Definition: relation.c:47
ItemPointerData t_self
Definition: htup.h:65
uint32 t_len
Definition: htup.h:64
HeapTupleHeader t_data
Definition: htup.h:68
Oid t_tableOid
Definition: htup.h:66
Form_pg_class rd_rel
Definition: rel.h:111
uint64 dead_tuple_count
Definition: pgstatapprox.c:39
double free_percent
Definition: pgstatapprox.c:43
double tuple_percent
Definition: pgstatapprox.c:38
double dead_tuple_percent
Definition: pgstatapprox.c:41
uint64 dead_tuple_len
Definition: pgstatapprox.c:40
uint64 tuple_count
Definition: pgstatapprox.c:36
uint64 free_space
Definition: pgstatapprox.c:42
uint64 table_len
Definition: pgstatapprox.c:34
uint64 tuple_len
Definition: pgstatapprox.c:37
double scanned_percent
Definition: pgstatapprox.c:35
bool superuser(void)
Definition: superuser.c:46
double vac_estimate_reltuples(Relation relation, BlockNumber total_pages, BlockNumber scanned_pages, double scanned_tuples)
Definition: vacuum.c:1314
#define VM_ALL_VISIBLE(r, b, v)
Definition: visibilitymap.h:24