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

Commite759854

Browse files
committed
pgstattuple: Add pgstathashindex.
Since pgstattuple v1.5 hasn't been released yet, no need for a newextension version. The new function exposes statistics about hashindexes similar to what other pgstatindex functions return for otherindex types.Ashutosh Sharma, reviewed by Kuntal Ghosh. Substantial furtherrevisions by me.
1 parent39b8cc9 commite759854

File tree

5 files changed

+318
-0
lines changed

5 files changed

+318
-0
lines changed

‎contrib/pgstattuple/expected/pgstattuple.out

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -130,3 +130,11 @@ select * from pgstatginindex('test_ginidx');
130130
2 | 0 | 0
131131
(1 row)
132132

133+
create index test_hashidx on test using hash (b);
134+
WARNING: hash indexes are not WAL-logged and their use is discouraged
135+
select * from pgstathashindex('test_hashidx');
136+
version | bucket_pages | overflow_pages | bitmap_pages | zero_pages | live_items | dead_items | free_percent
137+
---------+--------------+----------------+--------------+------------+------------+------------+--------------
138+
2 | 4 | 0 | 1 | 0 | 0 | 0 | 100
139+
(1 row)
140+

‎contrib/pgstattuple/pgstatindex.c

Lines changed: 195 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,13 +29,15 @@
2929

3030
#include"access/gin_private.h"
3131
#include"access/heapam.h"
32+
#include"access/hash.h"
3233
#include"access/htup_details.h"
3334
#include"access/nbtree.h"
3435
#include"catalog/namespace.h"
3536
#include"catalog/pg_am.h"
3637
#include"funcapi.h"
3738
#include"miscadmin.h"
3839
#include"storage/bufmgr.h"
40+
#include"storage/lmgr.h"
3941
#include"utils/builtins.h"
4042
#include"utils/rel.h"
4143
#include"utils/varlena.h"
@@ -54,6 +56,7 @@ PG_FUNCTION_INFO_V1(pgstatindexbyid);
5456
PG_FUNCTION_INFO_V1(pg_relpages);
5557
PG_FUNCTION_INFO_V1(pg_relpagesbyid);
5658
PG_FUNCTION_INFO_V1(pgstatginindex);
59+
PG_FUNCTION_INFO_V1(pgstathashindex);
5760

5861
PG_FUNCTION_INFO_V1(pgstatindex_v1_5);
5962
PG_FUNCTION_INFO_V1(pgstatindexbyid_v1_5);
@@ -66,6 +69,7 @@ Datum pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo);
6669
#defineIS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
6770
#defineIS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
6871
#defineIS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
72+
#defineIS_HASH(r) ((r)->rd_rel->relam == HASH_AM_OID)
6973

7074
/* ------------------------------------------------
7175
* A structure for a whole btree index statistics
@@ -102,7 +106,29 @@ typedef struct GinIndexStat
102106
int64pending_tuples;
103107
}GinIndexStat;
104108

109+
/* ------------------------------------------------
110+
* A structure for a whole HASH index statistics
111+
* used by pgstathashindex().
112+
* ------------------------------------------------
113+
*/
114+
typedefstructHashIndexStat
115+
{
116+
int32version;
117+
int32space_per_page;
118+
119+
BlockNumberbucket_pages;
120+
BlockNumberoverflow_pages;
121+
BlockNumberbitmap_pages;
122+
BlockNumberzero_pages;
123+
124+
int64live_items;
125+
int64dead_items;
126+
uint64free_space;
127+
}HashIndexStat;
128+
105129
staticDatumpgstatindex_impl(Relationrel,FunctionCallInfofcinfo);
130+
staticvoidGetHashPageStats(Pagepage,HashIndexStat*stats);
131+
106132

107133
/* ------------------------------------------------------
108134
* pgstatindex()
@@ -528,3 +554,172 @@ pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo)
528554

529555
return (result);
530556
}
557+
558+
/* ------------------------------------------------------
559+
* pgstathashindex()
560+
*
561+
* Usage: SELECT * FROM pgstathashindex('hashindex');
562+
* ------------------------------------------------------
563+
*/
564+
Datum
565+
pgstathashindex(PG_FUNCTION_ARGS)
566+
{
567+
Oidrelid=PG_GETARG_OID(0);
568+
BlockNumbernblocks;
569+
BlockNumberblkno;
570+
Relationrel;
571+
HashIndexStatstats;
572+
BufferAccessStrategybstrategy;
573+
HeapTupletuple;
574+
TupleDesctupleDesc;
575+
Datumvalues[8];
576+
boolnulls[8];
577+
Buffermetabuf;
578+
HashMetaPagemetap;
579+
float8free_percent;
580+
uint64total_space;
581+
582+
rel=index_open(relid,AccessShareLock);
583+
584+
if (!IS_HASH(rel))
585+
elog(ERROR,"relation \"%s\" is not a HASH index",
586+
RelationGetRelationName(rel));
587+
588+
/*
589+
* Reject attempts to read non-local temporary relations; we would be
590+
* likely to get wrong data since we have no visibility into the owning
591+
* session's local buffers.
592+
*/
593+
if (RELATION_IS_OTHER_TEMP(rel))
594+
ereport(ERROR,
595+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
596+
errmsg("cannot access temporary indexes of other sessions")));
597+
598+
/* Get the information we need from the metapage. */
599+
memset(&stats,0,sizeof(stats));
600+
metabuf=_hash_getbuf(rel,HASH_METAPAGE,HASH_READ,LH_META_PAGE);
601+
metap=HashPageGetMeta(BufferGetPage(metabuf));
602+
stats.version=metap->hashm_version;
603+
stats.space_per_page=metap->hashm_bsize;
604+
_hash_relbuf(rel,metabuf);
605+
606+
/* Get the current relation length */
607+
nblocks=RelationGetNumberOfBlocks(rel);
608+
609+
/* prepare access strategy for this index */
610+
bstrategy=GetAccessStrategy(BAS_BULKREAD);
611+
612+
/* Start from blkno 1 as 0th block is metapage */
613+
for (blkno=1;blkno<nblocks;blkno++)
614+
{
615+
Bufferbuf;
616+
Pagepage;
617+
HashPageOpaqueopaque;
618+
619+
CHECK_FOR_INTERRUPTS();
620+
621+
buf=ReadBufferExtended(rel,MAIN_FORKNUM,blkno,RBM_NORMAL,
622+
bstrategy);
623+
LockBuffer(buf,BUFFER_LOCK_SHARE);
624+
page= (Page)BufferGetPage(buf);
625+
626+
if (PageIsNew(page))
627+
stats.zero_pages++;
628+
elseif (PageGetSpecialSize(page)!=
629+
MAXALIGN(sizeof(HashPageOpaqueData)))
630+
ereport(ERROR,
631+
(errcode(ERRCODE_INDEX_CORRUPTED),
632+
errmsg("index \"%s\" contains corrupted page at block %u",
633+
RelationGetRelationName(rel),
634+
BufferGetBlockNumber(buf))));
635+
else
636+
{
637+
opaque= (HashPageOpaque)PageGetSpecialPointer(page);
638+
if (opaque->hasho_flag&LH_BUCKET_PAGE)
639+
{
640+
stats.bucket_pages++;
641+
GetHashPageStats(page,&stats);
642+
}
643+
elseif (opaque->hasho_flag&LH_OVERFLOW_PAGE)
644+
{
645+
stats.overflow_pages++;
646+
GetHashPageStats(page,&stats);
647+
}
648+
elseif (opaque->hasho_flag&LH_BITMAP_PAGE)
649+
stats.bitmap_pages++;
650+
else
651+
ereport(ERROR,
652+
(errcode(ERRCODE_INDEX_CORRUPTED),
653+
errmsg("unexpected page type 0x%04X in HASH index \"%s\" block %u",
654+
opaque->hasho_flag,RelationGetRelationName(rel),
655+
BufferGetBlockNumber(buf))));
656+
}
657+
UnlockReleaseBuffer(buf);
658+
}
659+
660+
/* Done accessing the index */
661+
index_close(rel,AccessShareLock);
662+
663+
/* Count zero pages as free space. */
664+
stats.free_space+=stats.zero_pages*stats.space_per_page;
665+
666+
/*
667+
* Total space available for tuples excludes the metapage and the bitmap
668+
* pages.
669+
*/
670+
total_space= (nblocks- (stats.bitmap_pages+1))*stats.space_per_page;
671+
672+
if (total_space==0)
673+
free_percent=0.0;
674+
else
675+
free_percent=100.0*stats.free_space /total_space;
676+
677+
/*
678+
* Build a tuple descriptor for our result type
679+
*/
680+
if (get_call_result_type(fcinfo,NULL,&tupleDesc)!=TYPEFUNC_COMPOSITE)
681+
elog(ERROR,"return type must be a row type");
682+
683+
tupleDesc=BlessTupleDesc(tupleDesc);
684+
685+
/*
686+
* Build and return the tuple
687+
*/
688+
MemSet(nulls,0,sizeof(nulls));
689+
values[0]=Int32GetDatum(stats.version);
690+
values[1]=Int64GetDatum((int64)stats.bucket_pages);
691+
values[2]=Int64GetDatum((int64)stats.overflow_pages);
692+
values[3]=Int64GetDatum((int64)stats.bitmap_pages);
693+
values[4]=Int64GetDatum((int64)stats.zero_pages);
694+
values[5]=Int64GetDatum(stats.live_items);
695+
values[6]=Int64GetDatum(stats.dead_items);
696+
values[7]=Float8GetDatum(free_percent);
697+
tuple=heap_form_tuple(tupleDesc,values,nulls);
698+
699+
PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
700+
}
701+
702+
/* -------------------------------------------------
703+
* GetHashPageStatis()
704+
*
705+
* Collect statistics of single hash page
706+
* -------------------------------------------------
707+
*/
708+
staticvoid
709+
GetHashPageStats(Pagepage,HashIndexStat*stats)
710+
{
711+
OffsetNumbermaxoff=PageGetMaxOffsetNumber(page);
712+
intoff;
713+
714+
/* count live and dead tuples, and free space */
715+
for (off=FirstOffsetNumber;off <=maxoff;off++)
716+
{
717+
ItemIdid=PageGetItemId(page,off);
718+
719+
if (!ItemIdIsDead(id))
720+
stats->live_items++;
721+
else
722+
stats->dead_items++;
723+
}
724+
stats->free_space+=PageGetExactFreeSpace(page);
725+
}

‎contrib/pgstattuple/pgstattuple--1.4--1.5.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,3 +109,19 @@ AS 'MODULE_PATHNAME', 'pgstattuple_approx_v1_5'
109109
LANGUAGE C STRICT PARALLEL SAFE;
110110

111111
REVOKE EXECUTEON FUNCTION pgstattuple_approx(regclass)FROM PUBLIC;
112+
113+
/* New stuff in 1.5 begins here*/
114+
115+
CREATE OR REPLACEFUNCTIONpgstathashindex(IN relname regclass,
116+
OUT versionINTEGER,
117+
OUT bucket_pagesBIGINT,
118+
OUT overflow_pagesBIGINT,
119+
OUT bitmap_pagesBIGINT,
120+
OUT zero_pagesBIGINT,
121+
OUT live_itemsBIGINT,
122+
OUT dead_itemsBIGINT,
123+
OUT free_percent FLOAT8)
124+
AS'MODULE_PATHNAME','pgstathashindex'
125+
LANGUAGE C STRICT PARALLEL SAFE;
126+
127+
REVOKE EXECUTEON FUNCTION pgstathashindex(regclass)FROM PUBLIC;

‎contrib/pgstattuple/sql/pgstattuple.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,3 +47,7 @@ select pg_relpages(relname) from pg_class where relname = 'test_pkey';
4747
createindextest_ginidxon test using gin (b);
4848

4949
select*from pgstatginindex('test_ginidx');
50+
51+
createindextest_hashidxon test using hash (b);
52+
53+
select*from pgstathashindex('test_hashidx');

‎doc/src/sgml/pgstattuple.sgml

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -352,6 +352,101 @@ pending_tuples | 0
352352
</listitem>
353353
</varlistentry>
354354

355+
<varlistentry>
356+
<term>
357+
<indexterm>
358+
<primary>pgstathashindex</primary>
359+
</indexterm>
360+
<function>pgstathashindex(regclass) returns record</>
361+
</term>
362+
363+
<listitem>
364+
<para>
365+
<function>pgstathashindex</function> returns a record showing information
366+
about a HASH index. For example:
367+
<programlisting>
368+
test=&gt; select * from pgstathashindex('con_hash_index');
369+
-[ RECORD 1 ]--+-----------------
370+
version | 2
371+
bucket_pages | 33081
372+
overflow_pages | 0
373+
bitmap_pages | 1
374+
zero_pages | 32455
375+
live_items | 10204006
376+
dead_items | 0
377+
free_percent | 61.8005949100872
378+
</programlisting>
379+
</para>
380+
381+
<para>
382+
The output columns are:
383+
384+
<informaltable>
385+
<tgroup cols="3">
386+
<thead>
387+
<row>
388+
<entry>Column</entry>
389+
<entry>Type</entry>
390+
<entry>Description</entry>
391+
</row>
392+
</thead>
393+
394+
<tbody>
395+
<row>
396+
<entry><structfield>version</structfield></entry>
397+
<entry><type>integer</type></entry>
398+
<entry>HASH version number</entry>
399+
</row>
400+
401+
<row>
402+
<entry><structfield>bucket_pages</structfield></entry>
403+
<entry><type>bigint</type></entry>
404+
<entry>Number of bucket pages</entry>
405+
</row>
406+
407+
<row>
408+
<entry><structfield>overflow_pages</structfield></entry>
409+
<entry><type>bigint</type></entry>
410+
<entry>Number of overflow pages</entry>
411+
</row>
412+
413+
<row>
414+
<entry><structfield>bitmap_pages</structfield></entry>
415+
<entry><type>bigint</type></entry>
416+
<entry>Number of bitmap pages</entry>
417+
</row>
418+
419+
<row>
420+
<entry><structfield>zero_pages</structfield></entry>
421+
<entry><type>bigint</type></entry>
422+
<entry>Number of new or zero pages</entry>
423+
</row>
424+
425+
<row>
426+
<entry><structfield>live_items</structfield></entry>
427+
<entry><type>bigint</type></entry>
428+
<entry>Number of live tuples</entry>
429+
</row>
430+
431+
<row>
432+
<entry><structfield>dead_tuples</structfield></entry>
433+
<entry><type>bigint</type></entry>
434+
<entry>Number of dead tuples</entry>
435+
</row>
436+
437+
<row>
438+
<entry><structfield>free_percent</structfield></entry>
439+
<entry><type>float</type></entry>
440+
<entry>Percentage of free space</entry>
441+
</row>
442+
443+
</tbody>
444+
</tgroup>
445+
</informaltable>
446+
</para>
447+
</listitem>
448+
</varlistentry>
449+
355450
<varlistentry>
356451
<term>
357452
<indexterm>

0 commit comments

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