Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5850b20

Browse files
committed
Add pgstattuple_approx() to the pgstattuple extension.
The new function allows to estimate bloat and other table level staticsin a faster, but approximate, way. It does so by using information fromthe free space map for pages marked as all visible in the visibilitymap. The rest of the table is actually read and free space/bloat ismeasured accurately. In many cases that allows to get bloat informationmuch quicker, causing less IO.Author: Abhijit Menon-SenReviewed-By: Andres Freund, Amit Kapila and Tomas VondraDiscussion: 20140402214144.GA28681@kea.toroid.org
1 parentdcf5e31 commit5850b20

File tree

6 files changed

+446
-5
lines changed

6 files changed

+446
-5
lines changed

‎contrib/pgstattuple/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,10 @@
11
# contrib/pgstattuple/Makefile
22

33
MODULE_big= pgstattuple
4-
OBJS= pgstattuple.o pgstatindex.o$(WIN32RES)
4+
OBJS= pgstattuple.o pgstatindex.opgstatapprox.o$(WIN32RES)
55

66
EXTENSION = pgstattuple
7-
DATA = pgstattuple--1.2.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
7+
DATA = pgstattuple--1.3.sql pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
88
PGFILEDESC = "pgstattuple - tuple-level statistics"
99

1010
REGRESS = pgstattuple

‎contrib/pgstattuple/pgstatapprox.c

Lines changed: 273 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,273 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* pgstatapproc.c
4+
* Bloat estimation functions
5+
*
6+
* Copyright (c) 2014-2015, PostgreSQL Global Development Group
7+
*
8+
* IDENTIFICATION
9+
* contrib/pgstattuple/pgstatapprox.c
10+
*
11+
*-------------------------------------------------------------------------
12+
*/
13+
#include"postgres.h"
14+
15+
#include"access/visibilitymap.h"
16+
#include"access/transam.h"
17+
#include"access/xact.h"
18+
#include"access/multixact.h"
19+
#include"access/htup_details.h"
20+
#include"catalog/namespace.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+
#include"storage/lmgr.h"
27+
#include"utils/builtins.h"
28+
#include"utils/tqual.h"
29+
#include"commands/vacuum.h"
30+
31+
PG_FUNCTION_INFO_V1(pgstattuple_approx);
32+
33+
typedefstructoutput_type
34+
{
35+
uint64table_len;
36+
uint64scanned_percent;
37+
uint64tuple_count;
38+
uint64tuple_len;
39+
doubletuple_percent;
40+
uint64dead_tuple_count;
41+
uint64dead_tuple_len;
42+
doubledead_tuple_percent;
43+
uint64free_space;
44+
doublefree_percent;
45+
}output_type;
46+
47+
#defineNUM_OUTPUT_COLUMNS 10
48+
49+
/*
50+
* This function takes an already open relation and scans its pages,
51+
* skipping those that have the corresponding visibility map bit set.
52+
* For pages we skip, we find the free space from the free space map
53+
* and approximate tuple_len on that basis. For the others, we count
54+
* the exact number of dead tuples etc.
55+
*
56+
* This scan is loosely based on vacuumlazy.c:lazy_scan_heap(), but
57+
* we do not try to avoid skipping single pages.
58+
*/
59+
staticvoid
60+
statapprox_heap(Relationrel,output_type*stat)
61+
{
62+
BlockNumberscanned,
63+
nblocks,
64+
blkno;
65+
Buffervmbuffer=InvalidBuffer;
66+
BufferAccessStrategybstrategy;
67+
TransactionIdOldestXmin;
68+
uint64misc_count=0;
69+
70+
OldestXmin=GetOldestXmin(rel, true);
71+
bstrategy=GetAccessStrategy(BAS_BULKREAD);
72+
73+
nblocks=RelationGetNumberOfBlocks(rel);
74+
scanned=0;
75+
76+
for (blkno=0;blkno<nblocks;blkno++)
77+
{
78+
Bufferbuf;
79+
Pagepage;
80+
OffsetNumberoffnum,
81+
maxoff;
82+
Sizefreespace;
83+
84+
CHECK_FOR_INTERRUPTS();
85+
86+
/*
87+
* If the page has only visible tuples, then we can find out the
88+
* free space from the FSM and move on.
89+
*/
90+
if (visibilitymap_test(rel,blkno,&vmbuffer))
91+
{
92+
freespace=GetRecordedFreeSpace(rel,blkno);
93+
stat->tuple_len+=BLCKSZ-freespace;
94+
stat->free_space+=freespace;
95+
continue;
96+
}
97+
98+
buf=ReadBufferExtended(rel,MAIN_FORKNUM,blkno,
99+
RBM_NORMAL,bstrategy);
100+
101+
LockBuffer(buf,BUFFER_LOCK_SHARE);
102+
103+
page=BufferGetPage(buf);
104+
105+
/*
106+
* It's not safe to call PageGetHeapFreeSpace() on new pages, so
107+
* we treat them as being free space for our purposes.
108+
*/
109+
if (!PageIsNew(page))
110+
stat->free_space+=PageGetHeapFreeSpace(page);
111+
else
112+
stat->free_space+=BLCKSZ-SizeOfPageHeaderData;
113+
114+
if (PageIsNew(page)||PageIsEmpty(page))
115+
{
116+
UnlockReleaseBuffer(buf);
117+
continue;
118+
}
119+
120+
scanned++;
121+
122+
/*
123+
* Look at each tuple on the page and decide whether it's live
124+
* or dead, then count it and its size. Unlike lazy_scan_heap,
125+
* we can afford to ignore problems and special cases.
126+
*/
127+
maxoff=PageGetMaxOffsetNumber(page);
128+
129+
for (offnum=FirstOffsetNumber;
130+
offnum <=maxoff;
131+
offnum=OffsetNumberNext(offnum))
132+
{
133+
ItemIditemid;
134+
HeapTupleDatatuple;
135+
136+
itemid=PageGetItemId(page,offnum);
137+
138+
if (!ItemIdIsUsed(itemid)||ItemIdIsRedirected(itemid)||
139+
ItemIdIsDead(itemid))
140+
{
141+
continue;
142+
}
143+
144+
Assert(ItemIdIsNormal(itemid));
145+
146+
ItemPointerSet(&(tuple.t_self),blkno,offnum);
147+
148+
tuple.t_data= (HeapTupleHeader)PageGetItem(page,itemid);
149+
tuple.t_len=ItemIdGetLength(itemid);
150+
tuple.t_tableOid=RelationGetRelid(rel);
151+
152+
/*
153+
* We count live and dead tuples, but we also need to add up
154+
* others in order to feed vac_estimate_reltuples.
155+
*/
156+
switch (HeapTupleSatisfiesVacuum(&tuple,OldestXmin,buf))
157+
{
158+
caseHEAPTUPLE_RECENTLY_DEAD:
159+
misc_count++;
160+
/* Fall through */
161+
caseHEAPTUPLE_DEAD:
162+
stat->dead_tuple_len+=tuple.t_len;
163+
stat->dead_tuple_count++;
164+
break;
165+
caseHEAPTUPLE_LIVE:
166+
stat->tuple_len+=tuple.t_len;
167+
stat->tuple_count++;
168+
break;
169+
caseHEAPTUPLE_INSERT_IN_PROGRESS:
170+
caseHEAPTUPLE_DELETE_IN_PROGRESS:
171+
misc_count++;
172+
break;
173+
default:
174+
elog(ERROR,"unexpected HeapTupleSatisfiesVacuum result");
175+
break;
176+
}
177+
}
178+
179+
UnlockReleaseBuffer(buf);
180+
}
181+
182+
stat->table_len= (uint64)nblocks*BLCKSZ;
183+
stat->tuple_count=vac_estimate_reltuples(rel, false,nblocks,scanned,
184+
stat->tuple_count+misc_count);
185+
186+
/*
187+
* Calculate percentages if the relation has one or more pages.
188+
*/
189+
if (nblocks!=0)
190+
{
191+
stat->scanned_percent=100*scanned /nblocks;
192+
stat->tuple_percent=100.0*stat->tuple_len /stat->table_len;
193+
stat->dead_tuple_percent=100.0*stat->dead_tuple_len /stat->table_len;
194+
stat->free_percent=100.0*stat->free_space /stat->table_len;
195+
}
196+
197+
if (BufferIsValid(vmbuffer))
198+
{
199+
ReleaseBuffer(vmbuffer);
200+
vmbuffer=InvalidBuffer;
201+
}
202+
}
203+
204+
/*
205+
* Returns estimated live/dead tuple statistics for the given relid.
206+
*/
207+
Datum
208+
pgstattuple_approx(PG_FUNCTION_ARGS)
209+
{
210+
Oidrelid=PG_GETARG_OID(0);
211+
Relationrel;
212+
output_typestat= {0};
213+
TupleDesctupdesc;
214+
boolnulls[NUM_OUTPUT_COLUMNS];
215+
Datumvalues[NUM_OUTPUT_COLUMNS];
216+
HeapTupleret;
217+
inti=0;
218+
219+
if (!superuser())
220+
ereport(ERROR,
221+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
222+
(errmsg("must be superuser to use pgstattuple functions"))));
223+
224+
if (get_call_result_type(fcinfo,NULL,&tupdesc)!=TYPEFUNC_COMPOSITE)
225+
elog(ERROR,"return type must be a row type");
226+
227+
if (tupdesc->natts!=NUM_OUTPUT_COLUMNS)
228+
elog(ERROR,"incorrect number of output arguments");
229+
230+
rel=relation_open(relid,AccessShareLock);
231+
232+
/*
233+
* Reject attempts to read non-local temporary relations; we would be
234+
* likely to get wrong data since we have no visibility into the owning
235+
* session's local buffers.
236+
*/
237+
if (RELATION_IS_OTHER_TEMP(rel))
238+
ereport(ERROR,
239+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
240+
errmsg("cannot access temporary tables of other sessions")));
241+
242+
/*
243+
* We support only ordinary relations and materialised views,
244+
* because we depend on the visibility map and free space map
245+
* for our estimates about unscanned pages.
246+
*/
247+
if (!(rel->rd_rel->relkind==RELKIND_RELATION||
248+
rel->rd_rel->relkind==RELKIND_MATVIEW))
249+
ereport(ERROR,
250+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
251+
errmsg("\"%s\" is not a table or materialized view",
252+
RelationGetRelationName(rel))));
253+
254+
statapprox_heap(rel,&stat);
255+
256+
relation_close(rel,AccessShareLock);
257+
258+
memset(nulls,0,sizeof(nulls));
259+
260+
values[i++]=Int64GetDatum(stat.table_len);
261+
values[i++]=Float8GetDatum(stat.scanned_percent);
262+
values[i++]=Int64GetDatum(stat.tuple_count);
263+
values[i++]=Int64GetDatum(stat.tuple_len);
264+
values[i++]=Float8GetDatum(stat.tuple_percent);
265+
values[i++]=Int64GetDatum(stat.dead_tuple_count);
266+
values[i++]=Int64GetDatum(stat.dead_tuple_len);
267+
values[i++]=Float8GetDatum(stat.dead_tuple_percent);
268+
values[i++]=Int64GetDatum(stat.free_space);
269+
values[i++]=Float8GetDatum(stat.free_percent);
270+
271+
ret=heap_form_tuple(tupdesc,values,nulls);
272+
returnHeapTupleGetDatum(ret);
273+
}
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
/* contrib/pgstattuple/pgstattuple--1.2--1.3.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pgstattuple UPDATE TO '1.3'" to load this file. \quit
5+
6+
CREATEFUNCTIONpgstattuple_approx(IN reloid regclass,
7+
OUT table_lenBIGINT,-- physical table length in bytes
8+
OUT scanned_percent FLOAT8,-- what percentage of the table's pages was scanned
9+
OUT approx_tuple_countBIGINT,-- estimated number of live tuples
10+
OUT approx_tuple_lenBIGINT,-- estimated total length in bytes of live tuples
11+
OUT approx_tuple_percent FLOAT8,-- live tuples in % (based on estimate)
12+
OUT dead_tuple_countBIGINT,-- exact number of dead tuples
13+
OUT dead_tuple_lenBIGINT,-- exact total length in bytes of dead tuples
14+
OUT dead_tuple_percent FLOAT8,-- dead tuples in % (based on estimate)
15+
OUT approx_free_spaceBIGINT,-- estimated free space in bytes
16+
OUT approx_free_percent FLOAT8)-- free space in % (based on estimate)
17+
AS'MODULE_PATHNAME','pgstattuple_approx'
18+
LANGUAGE C STRICT;

‎contrib/pgstattuple/pgstattuple--1.2.sqlrenamed to‎contrib/pgstattuple/pgstattuple--1.3.sql

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* contrib/pgstattuple/pgstattuple--1.2.sql*/
1+
/* contrib/pgstattuple/pgstattuple--1.3.sql*/
22

33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use"CREATE EXTENSION pgstattuple" to load this file. \quit
@@ -77,3 +77,19 @@ CREATE FUNCTION pg_relpages(IN relname regclass)
7777
RETURNSBIGINT
7878
AS'MODULE_PATHNAME','pg_relpagesbyid'
7979
LANGUAGE C STRICT;
80+
81+
/* New stuff in 1.3 begins here*/
82+
83+
CREATEFUNCTIONpgstattuple_approx(IN reloid regclass,
84+
OUT table_lenBIGINT,-- physical table length in bytes
85+
OUT scanned_percent FLOAT8,-- what percentage of the table's pages was scanned
86+
OUT approx_tuple_countBIGINT,-- estimated number of live tuples
87+
OUT approx_tuple_lenBIGINT,-- estimated total length in bytes of live tuples
88+
OUT approx_tuple_percent FLOAT8,-- live tuples in % (based on estimate)
89+
OUT dead_tuple_countBIGINT,-- exact number of dead tuples
90+
OUT dead_tuple_lenBIGINT,-- exact total length in bytes of dead tuples
91+
OUT dead_tuple_percent FLOAT8,-- dead tuples in % (based on estimate)
92+
OUT approx_free_spaceBIGINT,-- estimated free space in bytes
93+
OUT approx_free_percent FLOAT8)-- free space in % (based on estimate)
94+
AS'MODULE_PATHNAME','pgstattuple_approx'
95+
LANGUAGE C STRICT;
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pgstattuple extension
22
comment = 'show tuple-level statistics'
3-
default_version = '1.2'
3+
default_version = '1.3'
44
module_pathname = '$libdir/pgstattuple'
55
relocatable = true

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp