On Wed, Dec 3, 2014 at 2:37 AM, Robert Haas <robertmh...@gmail.com> wrote:
> On Fri, Nov 28, 2014 at 4:27 AM, Alexander Korotkov
> <aekorot...@gmail.com> wrote:
>> On Fri, Nov 21, 2014 at 8:12 AM, Michael Paquier <michael.paqu...@gmail.com>
>> wrote:
>>> Please find attached a simple patch adding fillfactor as storage parameter
>>> for GIN indexes. The default value is the same as the one currently aka 100
>>> to have the pages completely packed when a GIN index is created.
>>
>>
>> That's not true. Let us discuss it a little bit.
>> [blah discussion]
That's quite a nice explanation. Thanks!
>> My summary is following:
>> 1) In order to have fully correct support of fillfactor in GIN we need to
>> rewrite GIN build algorithm.
>> 2) Without rewriting GIN build algorithm, not much can be done with entry
>> tree. However, you can implement some heuristics.
TBH, I am not really planning to rewrite the whole code.
>> 3) You definitely need to touch code that selects ratio of split in
>> dataPlaceToPageLeaf (starting with if (!btree->isBuild)).
OK I see, so for a split we need to have a calculation based on the
fillfactor, with 75% by default.
>> 4) GIN data pages are always compressed excepts pg_upgraded indexes from
>> pre-9.4. Take care about it in following code.
>> if (GinPageIsCompressed(page))
>> freespace = GinDataLeafPageGetFreeSpace(page);
>> + else if (btree->isBuild)
>> + freespace = BLCKSZ * (100 - fillfactor) / 100;
Hm. Simply reversing both conditions is fine, no?
> This is a very interesting explanation; thanks for writing it up!
> It does leave me wondering why anyone would want fillfactor for GIN at
> all, even if they were willing to rewrite the build algorithm.
Based on the explanation of Alexander, the current GIN code fills in a
page at 75% for a split, and was doing even 50/50 pre-9.4 if I recall
correctly. IMO a higher fillfactor makes sense for a GIN index that
gets less random updates, no?
I am attaching an updated patch, with the default fillfactor value at
75%, and with the page split code using the fillfactor rate.
Thoughts?
--
Michael
From 39c6cab320f648b61cc65654ae67e62d8926bfa1 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Fri, 21 Nov 2014 14:08:54 +0900
Subject: [PATCH] Support fillfactor for GIN indexes
Users can call this new storage parameter to fill in the entry and
leaf pages of a newly-built index as wanted. Fillfactor range varies
between 20 and 100.
---
doc/src/sgml/ref/create_index.sgml | 4 ++--
src/backend/access/common/reloptions.c | 9 +++++++++
src/backend/access/gin/gindatapage.c | 34 ++++++++++++++++++++++++----------
src/backend/access/gin/ginentrypage.c | 20 +++++++++++++++++++-
src/backend/access/gin/ginutil.c | 3 ++-
src/include/access/gin_private.h | 3 +++
6 files changed, 59 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 6b2ee28..c0ba24a 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -294,8 +294,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
<para>
The optional <literal>WITH</> clause specifies <firstterm>storage
parameters</> for the index. Each index method has its own set of allowed
- storage parameters. The B-tree, hash, GiST and SP-GiST index methods all
- accept this parameter:
+ storage parameters. The B-tree, hash, GIN, GiST and SP-GiST index methods
+ all accept this parameter:
</para>
<variablelist>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index f008fab..418ecec 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -15,6 +15,7 @@
#include "postgres.h"
+#include "access/gin_private.h"
#include "access/gist_private.h"
#include "access/hash.h"
#include "access/htup_details.h"
@@ -133,6 +134,14 @@ static relopt_int intRelOpts[] =
},
{
{
+ "fillfactor",
+ "Packs gin index pages only to this percentage",
+ RELOPT_KIND_GIN
+ },
+ GIN_DEFAULT_FILLFACTOR, GIN_MIN_FILLFACTOR, 100
+ },
+ {
+ {
"autovacuum_vacuum_threshold",
"Minimum number of tuple updates or deletes prior to vacuum",
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
diff --git a/src/backend/access/gin/gindatapage.c b/src/backend/access/gin/gindatapage.c
index 8e81f6c..eebfa62 100644
--- a/src/backend/access/gin/gindatapage.c
+++ b/src/backend/access/gin/gindatapage.c
@@ -446,11 +446,21 @@ dataPlaceToPageLeaf(GinBtree btree, Buffer buf, GinBtreeStack *stack,
leafSegmentInfo *lastleftinfo;
ItemPointerData maxOldItem;
ItemPointerData remaining;
+ int fillfactor;
Assert(GinPageIsData(page));
rbound = *GinDataPageGetRightBound(page);
+ /* Grab option values */
+ if (btree->index->rd_options)
+ {
+ GinOptions *options = (GinOptions *) btree->index->rd_options;
+ fillfactor = options->fillfactor;
+ }
+ else
+ fillfactor = GIN_DEFAULT_FILLFACTOR;
+
/*
* Count how many of the new items belong to this page.
*/
@@ -511,15 +521,19 @@ dataPlaceToPageLeaf(GinBtree btree, Buffer buf, GinBtreeStack *stack,
/*
* If we're appending to the end of the page, we will append as many items
- * as we can fit (after splitting), and stop when the pages becomes full.
- * Otherwise we have to limit the number of new items to insert, because
- * once we start packing we can't just stop when we run out of space,
- * because we must make sure that all the old items still fit.
+ * as we can fit up to the given fillfactor at build (after splitting),
+ * and stop when the pages becomes full at this rate. Otherwise we have to
+ * limit the number of new items to insert, because once we start packing
+ * we can't just stop when we run out of space, because we must make sure
+ * that all the old items still fit.
*/
- if (GinPageIsCompressed(page))
+ if (btree->isBuild)
+ freespace = BLCKSZ * (100 - fillfactor) / 100;
+ else if (GinPageIsCompressed(page))
freespace = GinDataLeafPageGetFreeSpace(page);
else
freespace = 0;
+
if (append)
{
/*
@@ -628,10 +642,10 @@ dataPlaceToPageLeaf(GinBtree btree, Buffer buf, GinBtreeStack *stack,
* until they're balanced.
*
* As a further heuristic, when appending items to the end of the
- * page, try make the left page 75% full, one the assumption that
- * subsequent insertions will probably also go to the end. This packs
- * the index somewhat tighter when appending to a table, which is very
- * common.
+ * page, try make the left page full at the rate of fillfactor, one
+ * the assumption that subsequent insertions will probably also go
+ * to the end. This packs the index somewhat tighter when appending
+ * to a table, which is very common.
*/
if (!btree->isBuild)
{
@@ -654,7 +668,7 @@ dataPlaceToPageLeaf(GinBtree btree, Buffer buf, GinBtreeStack *stack,
break;
if (append)
{
- if ((leaf->lsize - segsize) < (BLCKSZ * 3) / 4)
+ if ((leaf->lsize - segsize) < (BLCKSZ * fillfactor) / 100)
break;
}
diff --git a/src/backend/access/gin/ginentrypage.c b/src/backend/access/gin/ginentrypage.c
index 9997eae..7bd5b30 100644
--- a/src/backend/access/gin/ginentrypage.c
+++ b/src/backend/access/gin/ginentrypage.c
@@ -462,6 +462,16 @@ entryIsEnoughSpace(GinBtree btree, Buffer buf, OffsetNumber off,
Size releasedsz = 0;
Size addedsz;
Page page = BufferGetPage(buf);
+ int fillfactor;
+ int freespace;
+
+ if (btree->index->rd_options)
+ {
+ GinOptions *options = (GinOptions *) btree->index->rd_options;
+ fillfactor = options->fillfactor;
+ }
+ else
+ fillfactor = GIN_DEFAULT_FILLFACTOR;
Assert(insertData->entry);
Assert(!GinPageIsData(page));
@@ -475,7 +485,15 @@ entryIsEnoughSpace(GinBtree btree, Buffer buf, OffsetNumber off,
addedsz = MAXALIGN(IndexTupleSize(insertData->entry)) + sizeof(ItemIdData);
- if (PageGetFreeSpace(page) + releasedsz >= addedsz)
+ /*
+ * Calculate freespace available. When building the index take into
+ * account the fillfactor.
+ */
+ if (btree->isBuild)
+ freespace = PageGetFreeSpace(page) - BLCKSZ * (100 - fillfactor) / 100;
+ else
+ freespace = PageGetFreeSpace(page);
+ if (freespace + releasedsz >= addedsz)
return true;
return false;
diff --git a/src/backend/access/gin/ginutil.c b/src/backend/access/gin/ginutil.c
index 445466b..1f7835e 100644
--- a/src/backend/access/gin/ginutil.c
+++ b/src/backend/access/gin/ginutil.c
@@ -527,7 +527,8 @@ ginoptions(PG_FUNCTION_ARGS)
static const relopt_parse_elt tab[] = {
{"fastupdate", RELOPT_TYPE_BOOL, offsetof(GinOptions, useFastUpdate)},
{"gin_pending_list_limit", RELOPT_TYPE_INT, offsetof(GinOptions,
- pendingListCleanupSize)}
+ pendingListCleanupSize)},
+ {"fillfactor", RELOPT_TYPE_INT, offsetof(GinOptions, fillfactor)}
};
options = parseRelOptions(reloptions, validate, RELOPT_KIND_GIN,
diff --git a/src/include/access/gin_private.h b/src/include/access/gin_private.h
index cf2ef80..43911dd 100644
--- a/src/include/access/gin_private.h
+++ b/src/include/access/gin_private.h
@@ -323,6 +323,7 @@ typedef struct GinOptions
int32 vl_len_; /* varlena header (do not touch directly!) */
bool useFastUpdate; /* use fast updates? */
int pendingListCleanupSize; /* maximum size of pending list */
+ int fillfactor; /* page fillfactor in percent (20..100) */
} GinOptions;
#define GIN_DEFAULT_USE_FASTUPDATE true
@@ -335,6 +336,8 @@ typedef struct GinOptions
((GinOptions *) (relation)->rd_options)->pendingListCleanupSize : \
gin_pending_list_limit)
+#define GIN_MIN_FILLFACTOR 20
+#define GIN_DEFAULT_FILLFACTOR 75
/* Macros for buffer lock/unlock operations */
#define GIN_UNLOCK BUFFER_LOCK_UNLOCK
--
2.2.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
