the number of pending entries in GIN index with FASTUPDATE=on
Hi,
Is there the way to know the number of pending entries in GIN index which
was created with FASTUPDATE = on? If not, is it worth implementing the
function returning that number?
I sometimes would like to know that number when I measure how much
pending entries affect the performance of GIN index scan and tune how
frequently autovacuum should run VACUUM to clean up them.
Regards,
--
Fujii Masao
On Fri, Nov 2, 2012 at 12:48 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
Is there the way to know the number of pending entries in GIN index which
was created with FASTUPDATE = on? If not, is it worth implementing the
function returning that number?I sometimes would like to know that number when I measure how much
pending entries affect the performance of GIN index scan and tune how
frequently autovacuum should run VACUUM to clean up them.
Seems useful to me.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Nov 2, 2012 at 12:48 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
Is there the way to know the number of pending entries in GIN index which
was created with FASTUPDATE = on? If not, is it worth implementing the
function returning that number?
Seems useful to me.
Seems like the appropriate place to expose this would be in a
GIN-specific variant of contrib/pgstattuple's pgstatindex(). I seem to
recall some previous discussion about how to fix the btree-centricity
of that function's API, but I don't recall if we thought of a good
solution.
regards, tom lane
On Tue, Nov 6, 2012 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Nov 2, 2012 at 12:48 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
Is there the way to know the number of pending entries in GIN index which
was created with FASTUPDATE = on? If not, is it worth implementing the
function returning that number?Seems useful to me.
Seems like the appropriate place to expose this would be in a
GIN-specific variant of contrib/pgstattuple's pgstatindex().
Yeah, that seems good to me, too. Or something in pgstatindex, anyway.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Wed, Nov 7, 2012 at 4:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Nov 6, 2012 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Nov 2, 2012 at 12:48 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
Is there the way to know the number of pending entries in GIN index which
was created with FASTUPDATE = on? If not, is it worth implementing the
function returning that number?Seems useful to me.
Seems like the appropriate place to expose this would be in a
GIN-specific variant of contrib/pgstattuple's pgstatindex().Yeah, that seems good to me, too. Or something in pgstatindex, anyway.
Agreed. Attached patch introduces the pgstatginindex() which now reports
GIN version number, number of pages in the pending list and number of
tuples in the pending list, as information about a GIN index.
Regards,
--
Fujii Masao
Attachments:
pgstatginindex_v1.patchapplication/octet-stream; name=pgstatginindex_v1.patchDownload
*** a/contrib/pgstattuple/Makefile
--- b/contrib/pgstattuple/Makefile
***************
*** 4,10 **** MODULE_big = pgstattuple
OBJS = pgstattuple.o pgstatindex.o
EXTENSION = pgstattuple
! DATA = pgstattuple--1.0.sql pgstattuple--unpackaged--1.0.sql
REGRESS = pgstattuple
--- 4,10 ----
OBJS = pgstattuple.o pgstatindex.o
EXTENSION = pgstattuple
! DATA = pgstattuple--1.1.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
REGRESS = pgstattuple
*** a/contrib/pgstattuple/expected/pgstattuple.out
--- b/contrib/pgstattuple/expected/pgstattuple.out
***************
*** 4,10 **** CREATE EXTENSION pgstattuple;
-- the pgstattuple functions, but the results for empty tables and
-- indexes should be that.
--
! create table test (a int primary key);
select * from pgstattuple('test'::text);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
--- 4,10 ----
-- the pgstattuple functions, but the results for empty tables and
-- indexes should be that.
--
! create table test (a int primary key, b int[]);
select * from pgstattuple('test'::text);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
***************
*** 35,37 **** select pg_relpages('test_pkey');
--- 35,44 ----
1
(1 row)
+ create index test_ginidx on test using gin (b);
+ select * from pgstatginindex('test_ginidx');
+ version | pending_pages | pending_tuples
+ ---------+---------------+----------------
+ 1 | 0 | 0
+ (1 row)
+
*** a/contrib/pgstattuple/pgstatindex.c
--- b/contrib/pgstattuple/pgstatindex.c
***************
*** 27,32 ****
--- 27,33 ----
#include "postgres.h"
+ #include "access/gin_private.h"
#include "access/heapam.h"
#include "access/nbtree.h"
#include "catalog/namespace.h"
***************
*** 39,50 ****
--- 40,54 ----
extern Datum pgstatindex(PG_FUNCTION_ARGS);
extern Datum pg_relpages(PG_FUNCTION_ARGS);
+ extern Datum pgstatginindex(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pgstatindex);
PG_FUNCTION_INFO_V1(pg_relpages);
+ PG_FUNCTION_INFO_V1(pgstatginindex);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
+ #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
#define CHECK_PAGE_OFFSET_RANGE(pg, offnum) { \
if ( !(FirstOffsetNumber <= (offnum) && \
***************
*** 79,84 **** typedef struct BTIndexStat
--- 83,101 ----
uint64 fragments;
} BTIndexStat;
+ /* ------------------------------------------------
+ * A structure for a whole GIN index statistics
+ * used by pgstatginindex().
+ * ------------------------------------------------
+ */
+ typedef struct GinIndexStat
+ {
+ int32 version;
+
+ BlockNumber pending_pages;
+ int64 pending_tuples;
+ } GinIndexStat;
+
/* ------------------------------------------------------
* pgstatindex()
*
***************
*** 292,294 **** pg_relpages(PG_FUNCTION_ARGS)
--- 309,390 ----
PG_RETURN_INT64(relpages);
}
+
+ /* ------------------------------------------------------
+ * pgstatginindex()
+ *
+ * Usage: SELECT * FROM pgstatginindex('ginindex');
+ * ------------------------------------------------------
+ */
+ Datum
+ pgstatginindex(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ Relation rel;
+ Buffer buffer;
+ Page page;
+ GinMetaPageData *metadata;
+ GinIndexStat stats;
+ HeapTuple tuple;
+ TupleDesc tupleDesc;
+ int j;
+ char *values[3];
+ Datum result;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser to use pgstattuple functions"))));
+
+ rel = relation_open(relid, AccessShareLock);
+
+ if (!IS_INDEX(rel) || !IS_GIN(rel))
+ elog(ERROR, "relation \"%s\" is not a GIN index",
+ RelationGetRelationName(rel));
+
+ /*
+ * Reject attempts to read non-local temporary relations; we would be
+ * likely to get wrong data since we have no visibility into the owning
+ * session's local buffers.
+ */
+ if (RELATION_IS_OTHER_TEMP(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary indexes of other sessions")));
+
+ /*
+ * Read metapage
+ */
+ buffer = ReadBuffer(rel, GIN_METAPAGE_BLKNO);
+ LockBuffer(buffer, GIN_SHARE);
+ page = BufferGetPage(buffer);
+ metadata = GinPageGetMeta(page);
+
+ stats.version = metadata->ginVersion;
+ stats.pending_pages = metadata->nPendingPages;
+ stats.pending_tuples = metadata->nPendingHeapTuples;
+
+ UnlockReleaseBuffer(buffer);
+ relation_close(rel, AccessShareLock);
+
+ /*
+ * Build a tuple descriptor for our result type
+ */
+ if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+
+ j = 0;
+ values[j] = palloc(32);
+ snprintf(values[j++], 32, "%d", stats.version);
+ values[j] = palloc(32);
+ snprintf(values[j++], 32, "%u", stats.pending_pages);
+ values[j] = palloc(64);
+ snprintf(values[j++], 64, INT64_FORMAT, stats.pending_tuples);
+
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ PG_RETURN_DATUM(result);
+ }
*** a/contrib/pgstattuple/pgstattuple--1.0.sql
--- /dev/null
***************
*** 1,49 ****
- /* contrib/pgstattuple/pgstattuple--1.0.sql */
-
- -- complain if script is sourced in psql, rather than via CREATE EXTENSION
- \echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
-
- CREATE FUNCTION pgstattuple(IN relname text,
- OUT table_len BIGINT, -- physical table length in bytes
- OUT tuple_count BIGINT, -- number of live tuples
- OUT tuple_len BIGINT, -- total tuples length in bytes
- OUT tuple_percent FLOAT8, -- live tuples in %
- OUT dead_tuple_count BIGINT, -- number of dead tuples
- OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
- OUT dead_tuple_percent FLOAT8, -- dead tuples in %
- OUT free_space BIGINT, -- free space in bytes
- OUT free_percent FLOAT8) -- free space in %
- AS 'MODULE_PATHNAME', 'pgstattuple'
- LANGUAGE C STRICT;
-
- CREATE FUNCTION pgstattuple(IN reloid oid,
- OUT table_len BIGINT, -- physical table length in bytes
- OUT tuple_count BIGINT, -- number of live tuples
- OUT tuple_len BIGINT, -- total tuples length in bytes
- OUT tuple_percent FLOAT8, -- live tuples in %
- OUT dead_tuple_count BIGINT, -- number of dead tuples
- OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
- OUT dead_tuple_percent FLOAT8, -- dead tuples in %
- OUT free_space BIGINT, -- free space in bytes
- OUT free_percent FLOAT8) -- free space in %
- AS 'MODULE_PATHNAME', 'pgstattuplebyid'
- LANGUAGE C STRICT;
-
- CREATE FUNCTION pgstatindex(IN relname text,
- OUT version INT,
- OUT tree_level INT,
- OUT index_size BIGINT,
- OUT root_block_no BIGINT,
- OUT internal_pages BIGINT,
- OUT leaf_pages BIGINT,
- OUT empty_pages BIGINT,
- OUT deleted_pages BIGINT,
- OUT avg_leaf_density FLOAT8,
- OUT leaf_fragmentation FLOAT8)
- AS 'MODULE_PATHNAME', 'pgstatindex'
- LANGUAGE C STRICT;
-
- CREATE FUNCTION pg_relpages(IN relname text)
- RETURNS BIGINT
- AS 'MODULE_PATHNAME', 'pg_relpages'
- LANGUAGE C STRICT;
--- 0 ----
*** /dev/null
--- b/contrib/pgstattuple/pgstattuple--1.1.sql
***************
*** 0 ****
--- 1,58 ----
+ /* contrib/pgstattuple/pgstattuple--1.1.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
+
+ CREATE FUNCTION pgstattuple(IN relname text,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8) -- free space in %
+ AS 'MODULE_PATHNAME', 'pgstattuple'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pgstattuple(IN reloid oid,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8) -- free space in %
+ AS 'MODULE_PATHNAME', 'pgstattuplebyid'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pgstatindex(IN relname text,
+ OUT version INT,
+ OUT tree_level INT,
+ OUT index_size BIGINT,
+ OUT root_block_no BIGINT,
+ OUT internal_pages BIGINT,
+ OUT leaf_pages BIGINT,
+ OUT empty_pages BIGINT,
+ OUT deleted_pages BIGINT,
+ OUT avg_leaf_density FLOAT8,
+ OUT leaf_fragmentation FLOAT8)
+ AS 'MODULE_PATHNAME', 'pgstatindex'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pg_relpages(IN relname text)
+ RETURNS BIGINT
+ AS 'MODULE_PATHNAME', 'pg_relpages'
+ LANGUAGE C STRICT;
+
+ /* New stuff in 1.1 begins here */
+
+ CREATE FUNCTION pgstatginindex(IN relname regclass,
+ OUT version INT4,
+ OUT pending_pages INT4,
+ OUT pending_tuples BIGINT)
+ AS 'MODULE_PATHNAME', 'pgstatginindex'
+ LANGUAGE C STRICT;
*** a/contrib/pgstattuple/pgstattuple.control
--- b/contrib/pgstattuple/pgstattuple.control
***************
*** 1,5 ****
# pgstattuple extension
comment = 'show tuple-level statistics'
! default_version = '1.0'
module_pathname = '$libdir/pgstattuple'
relocatable = true
--- 1,5 ----
# pgstattuple extension
comment = 'show tuple-level statistics'
! default_version = '1.1'
module_pathname = '$libdir/pgstattuple'
relocatable = true
*** a/contrib/pgstattuple/sql/pgstattuple.sql
--- b/contrib/pgstattuple/sql/pgstattuple.sql
***************
*** 6,12 **** CREATE EXTENSION pgstattuple;
-- indexes should be that.
--
! create table test (a int primary key);
select * from pgstattuple('test'::text);
select * from pgstattuple('test'::regclass);
--- 6,12 ----
-- indexes should be that.
--
! create table test (a int primary key, b int[]);
select * from pgstattuple('test'::text);
select * from pgstattuple('test'::regclass);
***************
*** 15,17 **** select * from pgstatindex('test_pkey');
--- 15,21 ----
select pg_relpages('test');
select pg_relpages('test_pkey');
+
+ create index test_ginidx on test using gin (b);
+
+ select * from pgstatginindex('test_ginidx');
*** a/doc/src/sgml/pgstattuple.sgml
--- b/doc/src/sgml/pgstattuple.sgml
***************
*** 257,262 **** leaf_fragmentation | 0
--- 257,319 ----
</listitem>
</varlistentry>
</variablelist>
+
+ <varlistentry>
+ <term>
+ <function>pgstatginindex(regclass) returns record</>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pgstatginindex</function> returns a record showing information
+ about a GIN index. For example:
+ <programlisting>
+ test=> SELECT * FROM pgstatginindex('test_gin_index');
+ -[ RECORD 1 ]--+--
+ version | 1
+ pending_pages | 0
+ pending_tuples | 0
+ </programlisting>
+ </para>
+
+ <para>
+ The output columns are:
+
+ <informaltable>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>version</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry>GIN version number</entry>
+ </row>
+
+ <row>
+ <entry><structfield>pending_pages</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry>Number of pages in the pending list</entry>
+ </row>
+
+ <row>
+ <entry><structfield>pending_tuples</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number of tuples in the pending list</entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </para>
+ </listitem>
+ </varlistentry>
</sect2>
<sect2>
Hello,
Agreed. Attached patch introduces the pgstatginindex() which now reports
GIN version number, number of pages in the pending list and number of
tuples in the pending list, as information about a GIN index.
It seems fine on the whole, and I have some suggestions.
1. This patch applies current git head cleanly, but installation
ends up with failure because of the lack of
pgstattuple--1.0--1.1.sql which added in Makefile.
2. I feel somewhat uneasy with size for palloc's (it's too long),
and BuildTupleFromCString used instead of heap_from_tuple.. But
it would lead additional modification for existent simillars.
You can leave that if you prefer to keep this patch smaller,
but it looks to me more preferable to construct the result
tuple not via c-strings in some aspects. (*1)
3. pgstatginidex shows only version, pending_pages, and
pendingtuples. Why don't you show the other properties such as
entry pages, data pages, entries, and total pages as
pgstatindex does?
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
(*1) Sample
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index 8a2ae85..71c2023 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -29,2 +29,3 @@+#include "access/htup_details.h"
#include "access/gin_private.h"
@@ -39,3 +40,2 @@-
extern Datum pgstatindex(PG_FUNCTION_ARGS);
@@ -330,4 +330,5 @@ pgstatginindex(PG_FUNCTION_ARGS)
int j;
- char *values[3];
+ Datum values[3];
Datum result;
+ bool nulls[3] = {false, false, false};@@ -376,11 +377,6 @@ pgstatginindex(PG_FUNCTION_ARGS)
j = 0;
- values[j] = palloc(32);
- snprintf(values[j++], 32, "%d", stats.version);
- values[j] = palloc(32);
- snprintf(values[j++], 32, "%u", stats.pending_pages);
- values[j] = palloc(64);
- snprintf(values[j++], 64, INT64_FORMAT, stats.pending_tuples);
-
- tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
- values);
+ values[j++] = Int32GetDatum(stats.version);
+ values[j++] = UInt32GetDatum(stats.pending_pages);
+ values[j++] = Int64GetDatumFast(stats.pending_tuples);
+ tuple = heap_form_tuple(tupleDesc, values, nulls);On Tue, Nov 20, 2012 at 4:44 PM, Kyotaro HORIGUCHI
<horiguchi.kyotaro@lab.ntt.co.jp> wrote:
Hello,
Agreed. Attached patch introduces the pgstatginindex() which now reports
GIN version number, number of pages in the pending list and number of
tuples in the pending list, as information about a GIN index.It seems fine on the whole, and I have some suggestions.
Thanks for the review!
1. This patch applies current git head cleanly, but installation
ends up with failure because of the lack of
pgstattuple--1.0--1.1.sql which added in Makefile.
Added pgstattuple--1.0--1.1.sql.
2. I feel somewhat uneasy with size for palloc's (it's too long),
and BuildTupleFromCString used instead of heap_from_tuple.. But
it would lead additional modification for existent simillars.You can leave that if you prefer to keep this patch smaller,
but it looks to me more preferable to construct the result
tuple not via c-strings in some aspects. (*1)
OK. I changed the code as you suggested.
Updated version of the patch attached.
3. pgstatginidex shows only version, pending_pages, and
pendingtuples. Why don't you show the other properties such as
entry pages, data pages, entries, and total pages as
pgstatindex does?
I didn't expose those because they are accurate as of last VACUUM.
But if you think they are useful, I don't object to expose them.
Regards,
--
Fujii Masao
Attachments:
pgstatginindex_v2.patchapplication/octet-stream; name=pgstatginindex_v2.patchDownload
*** a/contrib/pgstattuple/Makefile
--- b/contrib/pgstattuple/Makefile
***************
*** 4,10 **** MODULE_big = pgstattuple
OBJS = pgstattuple.o pgstatindex.o
EXTENSION = pgstattuple
! DATA = pgstattuple--1.0.sql pgstattuple--unpackaged--1.0.sql
REGRESS = pgstattuple
--- 4,10 ----
OBJS = pgstattuple.o pgstatindex.o
EXTENSION = pgstattuple
! DATA = pgstattuple--1.1.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
REGRESS = pgstattuple
*** a/contrib/pgstattuple/expected/pgstattuple.out
--- b/contrib/pgstattuple/expected/pgstattuple.out
***************
*** 4,10 **** CREATE EXTENSION pgstattuple;
-- the pgstattuple functions, but the results for empty tables and
-- indexes should be that.
--
! create table test (a int primary key);
select * from pgstattuple('test'::text);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
--- 4,10 ----
-- the pgstattuple functions, but the results for empty tables and
-- indexes should be that.
--
! create table test (a int primary key, b int[]);
select * from pgstattuple('test'::text);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
***************
*** 35,37 **** select pg_relpages('test_pkey');
--- 35,44 ----
1
(1 row)
+ create index test_ginidx on test using gin (b);
+ select * from pgstatginindex('test_ginidx');
+ version | pending_pages | pending_tuples
+ ---------+---------------+----------------
+ 1 | 0 | 0
+ (1 row)
+
*** a/contrib/pgstattuple/pgstatindex.c
--- b/contrib/pgstattuple/pgstatindex.c
***************
*** 27,32 ****
--- 27,34 ----
#include "postgres.h"
+ #include "access/gin_private.h"
+ #include "access/htup_details.h"
#include "access/heapam.h"
#include "access/nbtree.h"
#include "catalog/namespace.h"
***************
*** 39,50 ****
--- 41,55 ----
extern Datum pgstatindex(PG_FUNCTION_ARGS);
extern Datum pg_relpages(PG_FUNCTION_ARGS);
+ extern Datum pgstatginindex(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pgstatindex);
PG_FUNCTION_INFO_V1(pg_relpages);
+ PG_FUNCTION_INFO_V1(pgstatginindex);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
+ #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
#define CHECK_PAGE_OFFSET_RANGE(pg, offnum) { \
if ( !(FirstOffsetNumber <= (offnum) && \
***************
*** 79,84 **** typedef struct BTIndexStat
--- 84,102 ----
uint64 fragments;
} BTIndexStat;
+ /* ------------------------------------------------
+ * A structure for a whole GIN index statistics
+ * used by pgstatginindex().
+ * ------------------------------------------------
+ */
+ typedef struct GinIndexStat
+ {
+ int32 version;
+
+ BlockNumber pending_pages;
+ int64 pending_tuples;
+ } GinIndexStat;
+
/* ------------------------------------------------------
* pgstatindex()
*
***************
*** 292,294 **** pg_relpages(PG_FUNCTION_ARGS)
--- 310,388 ----
PG_RETURN_INT64(relpages);
}
+
+ /* ------------------------------------------------------
+ * pgstatginindex()
+ *
+ * Usage: SELECT * FROM pgstatginindex('ginindex');
+ * ------------------------------------------------------
+ */
+ Datum
+ pgstatginindex(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ Relation rel;
+ Buffer buffer;
+ Page page;
+ GinMetaPageData *metadata;
+ GinIndexStat stats;
+ HeapTuple tuple;
+ TupleDesc tupleDesc;
+ Datum values[3];
+ bool nulls[3] = {false, false, false};
+ Datum result;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser to use pgstattuple functions"))));
+
+ rel = relation_open(relid, AccessShareLock);
+
+ if (!IS_INDEX(rel) || !IS_GIN(rel))
+ elog(ERROR, "relation \"%s\" is not a GIN index",
+ RelationGetRelationName(rel));
+
+ /*
+ * Reject attempts to read non-local temporary relations; we would be
+ * likely to get wrong data since we have no visibility into the owning
+ * session's local buffers.
+ */
+ if (RELATION_IS_OTHER_TEMP(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary indexes of other sessions")));
+
+ /*
+ * Read metapage
+ */
+ buffer = ReadBuffer(rel, GIN_METAPAGE_BLKNO);
+ LockBuffer(buffer, GIN_SHARE);
+ page = BufferGetPage(buffer);
+ metadata = GinPageGetMeta(page);
+
+ stats.version = metadata->ginVersion;
+ stats.pending_pages = metadata->nPendingPages;
+ stats.pending_tuples = metadata->nPendingHeapTuples;
+
+ UnlockReleaseBuffer(buffer);
+ relation_close(rel, AccessShareLock);
+
+ /*
+ * Build a tuple descriptor for our result type
+ */
+ if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+
+ values[0] = Int32GetDatum(stats.version);
+ values[1] = UInt32GetDatum(stats.pending_pages);
+ values[2] = Int64GetDatum(stats.pending_tuples);
+
+ /*
+ * Build and return the tuple
+ */
+ tuple = heap_form_tuple(tupleDesc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ PG_RETURN_DATUM(result);
+ }
*** /dev/null
--- b/contrib/pgstattuple/pgstattuple--1.0--1.1.sql
***************
*** 0 ****
--- 1,11 ----
+ /* contrib/pgstattuple/pgstattuple--1.0--1.1.sql */
+
+ -- complain if script is sourced in psql, rather than via ALTER EXTENSION
+ \echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.1'" to load this file. \quit
+
+ CREATE FUNCTION pgstatginindex(IN relname regclass,
+ OUT version INT4,
+ OUT pending_pages INT4,
+ OUT pending_tuples BIGINT)
+ AS 'MODULE_PATHNAME', 'pgstatginindex'
+ LANGUAGE C STRICT;
*** a/contrib/pgstattuple/pgstattuple--1.0.sql
--- /dev/null
***************
*** 1,49 ****
- /* contrib/pgstattuple/pgstattuple--1.0.sql */
-
- -- complain if script is sourced in psql, rather than via CREATE EXTENSION
- \echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
-
- CREATE FUNCTION pgstattuple(IN relname text,
- OUT table_len BIGINT, -- physical table length in bytes
- OUT tuple_count BIGINT, -- number of live tuples
- OUT tuple_len BIGINT, -- total tuples length in bytes
- OUT tuple_percent FLOAT8, -- live tuples in %
- OUT dead_tuple_count BIGINT, -- number of dead tuples
- OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
- OUT dead_tuple_percent FLOAT8, -- dead tuples in %
- OUT free_space BIGINT, -- free space in bytes
- OUT free_percent FLOAT8) -- free space in %
- AS 'MODULE_PATHNAME', 'pgstattuple'
- LANGUAGE C STRICT;
-
- CREATE FUNCTION pgstattuple(IN reloid oid,
- OUT table_len BIGINT, -- physical table length in bytes
- OUT tuple_count BIGINT, -- number of live tuples
- OUT tuple_len BIGINT, -- total tuples length in bytes
- OUT tuple_percent FLOAT8, -- live tuples in %
- OUT dead_tuple_count BIGINT, -- number of dead tuples
- OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
- OUT dead_tuple_percent FLOAT8, -- dead tuples in %
- OUT free_space BIGINT, -- free space in bytes
- OUT free_percent FLOAT8) -- free space in %
- AS 'MODULE_PATHNAME', 'pgstattuplebyid'
- LANGUAGE C STRICT;
-
- CREATE FUNCTION pgstatindex(IN relname text,
- OUT version INT,
- OUT tree_level INT,
- OUT index_size BIGINT,
- OUT root_block_no BIGINT,
- OUT internal_pages BIGINT,
- OUT leaf_pages BIGINT,
- OUT empty_pages BIGINT,
- OUT deleted_pages BIGINT,
- OUT avg_leaf_density FLOAT8,
- OUT leaf_fragmentation FLOAT8)
- AS 'MODULE_PATHNAME', 'pgstatindex'
- LANGUAGE C STRICT;
-
- CREATE FUNCTION pg_relpages(IN relname text)
- RETURNS BIGINT
- AS 'MODULE_PATHNAME', 'pg_relpages'
- LANGUAGE C STRICT;
--- 0 ----
*** /dev/null
--- b/contrib/pgstattuple/pgstattuple--1.1.sql
***************
*** 0 ****
--- 1,58 ----
+ /* contrib/pgstattuple/pgstattuple--1.1.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
+
+ CREATE FUNCTION pgstattuple(IN relname text,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8) -- free space in %
+ AS 'MODULE_PATHNAME', 'pgstattuple'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pgstattuple(IN reloid oid,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8) -- free space in %
+ AS 'MODULE_PATHNAME', 'pgstattuplebyid'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pgstatindex(IN relname text,
+ OUT version INT,
+ OUT tree_level INT,
+ OUT index_size BIGINT,
+ OUT root_block_no BIGINT,
+ OUT internal_pages BIGINT,
+ OUT leaf_pages BIGINT,
+ OUT empty_pages BIGINT,
+ OUT deleted_pages BIGINT,
+ OUT avg_leaf_density FLOAT8,
+ OUT leaf_fragmentation FLOAT8)
+ AS 'MODULE_PATHNAME', 'pgstatindex'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION pg_relpages(IN relname text)
+ RETURNS BIGINT
+ AS 'MODULE_PATHNAME', 'pg_relpages'
+ LANGUAGE C STRICT;
+
+ /* New stuff in 1.1 begins here */
+
+ CREATE FUNCTION pgstatginindex(IN relname regclass,
+ OUT version INT4,
+ OUT pending_pages INT4,
+ OUT pending_tuples BIGINT)
+ AS 'MODULE_PATHNAME', 'pgstatginindex'
+ LANGUAGE C STRICT;
*** a/contrib/pgstattuple/pgstattuple.control
--- b/contrib/pgstattuple/pgstattuple.control
***************
*** 1,5 ****
# pgstattuple extension
comment = 'show tuple-level statistics'
! default_version = '1.0'
module_pathname = '$libdir/pgstattuple'
relocatable = true
--- 1,5 ----
# pgstattuple extension
comment = 'show tuple-level statistics'
! default_version = '1.1'
module_pathname = '$libdir/pgstattuple'
relocatable = true
*** a/contrib/pgstattuple/sql/pgstattuple.sql
--- b/contrib/pgstattuple/sql/pgstattuple.sql
***************
*** 6,12 **** CREATE EXTENSION pgstattuple;
-- indexes should be that.
--
! create table test (a int primary key);
select * from pgstattuple('test'::text);
select * from pgstattuple('test'::regclass);
--- 6,12 ----
-- indexes should be that.
--
! create table test (a int primary key, b int[]);
select * from pgstattuple('test'::text);
select * from pgstattuple('test'::regclass);
***************
*** 15,17 **** select * from pgstatindex('test_pkey');
--- 15,21 ----
select pg_relpages('test');
select pg_relpages('test_pkey');
+
+ create index test_ginidx on test using gin (b);
+
+ select * from pgstatginindex('test_ginidx');
*** a/doc/src/sgml/pgstattuple.sgml
--- b/doc/src/sgml/pgstattuple.sgml
***************
*** 257,262 **** leaf_fragmentation | 0
--- 257,319 ----
</listitem>
</varlistentry>
</variablelist>
+
+ <varlistentry>
+ <term>
+ <function>pgstatginindex(regclass) returns record</>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pgstatginindex</function> returns a record showing information
+ about a GIN index. For example:
+ <programlisting>
+ test=> SELECT * FROM pgstatginindex('test_gin_index');
+ -[ RECORD 1 ]--+--
+ version | 1
+ pending_pages | 0
+ pending_tuples | 0
+ </programlisting>
+ </para>
+
+ <para>
+ The output columns are:
+
+ <informaltable>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>version</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry>GIN version number</entry>
+ </row>
+
+ <row>
+ <entry><structfield>pending_pages</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry>Number of pages in the pending list</entry>
+ </row>
+
+ <row>
+ <entry><structfield>pending_tuples</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number of tuples in the pending list</entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </para>
+ </listitem>
+ </varlistentry>
</sect2>
<sect2>
1. This patch applies current git head cleanly, but installation
ends up with failure because of the lack of
pgstattuple--1.0--1.1.sql which added in Makefile.Added pgstattuple--1.0--1.1.sql.
Good. Installation completed and ALTER EXTENSION UPDATE works
with that.
2. I feel somewhat uneasy with size for palloc's (it's too long),
and BuildTupleFromCString used instead of heap_from_tuple.. But
it would lead additional modification for existent simillars.OK. I changed the code as you suggested.
Thank you. It looks simpler than the last one. (although the way
differs to pgstatindex..)
3. pgstatginidex shows only version, pending_pages, and
pendingtuples. Why don't you show the other properties such as
entry pages, data pages, entries, and total pages as
pgstatindex does?I didn't expose those because they are accurate as of last VACUUM.
But if you think they are useful, I don't object to expose them.
Ok, my point was the apparent consistency of the functions. I
don't have any distinct wish about this.
I'll mark this as Ready for Committer.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 28.11.2012 04:11, Kyotaro HORIGUCHI wrote:
3. pgstatginidex shows only version, pending_pages, and
pendingtuples. Why don't you show the other properties such as
entry pages, data pages, entries, and total pages as
pgstatindex does?I didn't expose those because they are accurate as of last VACUUM.
But if you think they are useful, I don't object to expose them.Ok, my point was the apparent consistency of the functions. I
don't have any distinct wish about this.
We can always add more fields later if there's a need.
I'll mark this as Ready for Committer.
Thanks, committed.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers