Horton
Menu

Thread

  1. the number of pending entries in GIN index with FASTUPDATE=on

    Fujii Masao <masao.fujii@gmail.com> — 2012-11-02T16:48:56Z

    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

  2. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Robert Haas <robertmhaas@gmail.com> — 2012-11-06T16:37:29Z

    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

  3. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-11-06T18:01:30Z

    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

  4. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Robert Haas <robertmhaas@gmail.com> — 2012-11-06T19:34:53Z

    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

  5. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Fujii Masao <masao.fujii@gmail.com> — 2012-11-08T18:42:44Z

    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

  6. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> — 2012-11-20T07:44:39Z

    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);

  7. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Fujii Masao <masao.fujii@gmail.com> — 2012-11-22T19:14:38Z

    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

  8. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> — 2012-11-28T02:11:39Z

    > > 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

  9. Re: the number of pending entries in GIN index with FASTUPDATE=on

    Heikki Linnakangas <hlinnakangas@vmware.com> — 2012-12-05T08:08:38Z

    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