TODO-Item: B-tree fillfactor control
Started by ITAGAKI Takahiroalmost 20 years ago15 messages
Hi Hackers,
I'm trying the following TODO item:
[Indexes]
- Add fillfactor to control reserved free space during index creation
I have already made an patch and it seemed to work well.
----
$ ./pgbench -i -s 10
# select relpages from pg_class where relname = 'accounts_pkey';
relpages | 2745 ( default PCTFree is 10% )
# set btree_leaf_free_percent = 0;
# reindex index accounts_pkey;
# select relpages from pg_class where relname = 'accounts_pkey';
relpages | 2475 ( <- about 2745 * 0.9 = 2470.5 )
# set btree_leaf_free_percent = 30;
# reindex index accounts_pkey;
# select relpages from pg_class where relname = 'accounts_pkey';
relpages | 3537 ( <- about 2745 * 0.9 / 0.7 = 3529.3 )
----
And now, I need advice on some issues.
- Is it appropriate to use GUC variables to control fillfactors?
Is it better to extend CREATE INDEX / REINDEX grammar?
- Should indexes remember their fillfactors when they are created?
The last fillfactors will be used on next reindex.
- Is fillfactor useful for hash and gist indexes?
I think hash does not need it, but gist might need it.
Look forward to your comments.
Thanks,
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories
Attachments:
btree_free_percent.patchapplication/octet-stream; name=btree_free_percent.patchDownload
diff -cpr pgsql-orig/src/backend/access/nbtree/nbtsort.c pgsql/src/backend/access/nbtree/nbtsort.c
*** pgsql-orig/src/backend/access/nbtree/nbtsort.c 2006-01-19 22:02:42.000000000 +0900
--- pgsql/src/backend/access/nbtree/nbtsort.c 2006-01-19 22:13:13.000000000 +0900
*************** typedef struct BTWriteState
*** 125,130 ****
--- 125,132 ----
(sizeof(BTItemData) - sizeof(IndexTupleData))) : \
0)
+ double btree_node_free_percent = 30.0;
+ double btree_leaf_free_percent = 10.0;
static Page _bt_blnewpage(uint32 level);
static BTPageState *_bt_pagestate(BTWriteState *wstate, uint32 level);
*************** _bt_pagestate(BTWriteState *wstate, uint
*** 360,369 ****
state->btps_lastoff = P_HIKEY;
state->btps_level = level;
/* set "full" threshold based on level. See notes at head of file. */
! if (level > 0)
! state->btps_full = (PageGetPageSize(state->btps_page) * 3) / 10;
! else
! state->btps_full = PageGetPageSize(state->btps_page) / 10;
/* no parent level, yet */
state->btps_next = NULL;
--- 362,370 ----
state->btps_lastoff = P_HIKEY;
state->btps_level = level;
/* set "full" threshold based on level. See notes at head of file. */
! state->btps_full = (Size) ((PageGetPageSize(state->btps_page)
! * (level > 0 ? btree_node_free_percent : btree_leaf_free_percent)
! + 99) / 100);
/* no parent level, yet */
state->btps_next = NULL;
diff -cpr pgsql-orig/src/backend/utils/misc/guc.c pgsql/src/backend/utils/misc/guc.c
*** pgsql-orig/src/backend/utils/misc/guc.c 2006-01-19 22:02:42.000000000 +0900
--- pgsql/src/backend/utils/misc/guc.c 2006-01-19 22:13:02.000000000 +0900
*************** extern int CommitDelay;
*** 95,100 ****
--- 95,102 ----
extern int CommitSiblings;
extern char *default_tablespace;
extern bool fullPageWrites;
+ extern double btree_node_free_percent;
+ extern double btree_leaf_free_percent;
#ifdef TRACE_SORT
extern bool trace_sort;
*************** static struct config_real ConfigureNames
*** 1685,1690 ****
--- 1687,1710 ----
0.2, 0.0, 100.0, NULL, NULL
},
+ {
+ {"btree_node_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for btree node pages during index creation."),
+ NULL
+ },
+ &btree_node_free_percent,
+ 30.0, 0.0, 66.6, NULL, NULL
+ },
+
+ {
+ {"btree_leaf_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for btree leaf pages during index creation."),
+ NULL
+ },
+ &btree_leaf_free_percent,
+ 10.0, 0.0, 66.6, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0.0, 0.0, 0.0, NULL, NULL
Re: TODO-Item: B-tree fillfactor control
ITAGAKI Takahiro wrote:
Hi Hackers,
I'm trying the following TODO item:
[Indexes]
- Add fillfactor to control reserved free space during index creationI have already made an patch and it seemed to work well.
Great.
And now, I need advice on some issues.
- Is it appropriate to use GUC variables to control fillfactors?
Is it better to extend CREATE INDEX / REINDEX grammar?
I think it has to be part of CREATE INDEX and ALTER INDEX.
Is there a use for separate node and leaf settings?
This patch needs documentation, and if we have separate items, we are
going to have to explain when to use node or leaf.
- Should indexes remember their fillfactors when they are created?
The last fillfactors will be used on next reindex.
They should remember, for sure, and REINDEX should use it. It think
this is similar to the ALTER TABLE ALTER [ COLUMN ] ... SET STATISTICS
functionality. It will need to be dumped as well by pg_dump. If you
need help with any of this, let me know.
- Is fillfactor useful for hash and gist indexes?
I think hash does not need it, but gist might need it.
Not sure. We don't know what type of index a GIST will be so we have no
way of knowing. I am thinking we can implement just btree now and the
GIST folks can add it later if they want. My guess is that each GIST is
going to behave differently for different fill-factors, so if allow it
to be set for GIST, GIST developers can pull the value if they want.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Re: TODO-Item: B-tree fillfactor control
Bruce Momjian <pgman@candle.pha.pa.us> writes:
- Should indexes remember their fillfactors when they are created?
The last fillfactors will be used on next reindex.
They should remember, for sure, and REINDEX should use it. It think
this is similar to the ALTER TABLE ALTER [ COLUMN ] ... SET STATISTICS
functionality. It will need to be dumped as well by pg_dump.
If you want it to be dumped by pg_dump (which is debatable IMHO) then
it MUST NOT be a syntax extension, it has to be driven by a GUC
variable, else we have compatibility problems with the dumps. We just
went through this with WITH/WITHOUT OIDS.
regards, tom lane
Re: TODO-Item: B-tree fillfactor control
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
- Should indexes remember their fillfactors when they are created?
The last fillfactors will be used on next reindex.They should remember, for sure, and REINDEX should use it. It think
this is similar to the ALTER TABLE ALTER [ COLUMN ] ... SET STATISTICS
functionality. It will need to be dumped as well by pg_dump.If you want it to be dumped by pg_dump (which is debatable IMHO) then
it MUST NOT be a syntax extension, it has to be driven by a GUC
variable, else we have compatibility problems with the dumps. We just
went through this with WITH/WITHOUT OIDS.
OK, so we are going to issue a GUC to set the fill factor in pg_dump,
but still have a fillfactor syntax for use by users? That is how we do
WITH/WITHOUT OIDS.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Re: TODO-Item: B-tree fillfactor control
If you want it to be dumped by pg_dump (which is debatable IMHO) then
it MUST NOT be a syntax extension, it has to be driven by a GUC
variable, else we have compatibility problems with the dumps. We just
went through this with WITH/WITHOUT OIDS.
Compatibility problems? CREATE INDEX isn't an SQL standard command is it?
Chris
Re: TODO-Item: B-tree fillfactor control
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
Compatibility problems? CREATE INDEX isn't an SQL standard command is it?
No, but it'll cause unnecessary cross-version compatibility issues for
us.
regards, tom lane
Re: TODO-Item: B-tree fillfactor control
Tom Lane wrote:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
Compatibility problems? CREATE INDEX isn't an SQL standard command is it?
No, but it'll cause unnecessary cross-version compatibility issues for
us.
It is true it isn't SQL standard, but I think our CREATE INDEX syntax
matches many vendor's syntax in most cases.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Re: TODO-Item: B-tree fillfactor control
Bruce Momjian <pgman@candle.pha.pa.us> wrote:
- Is fillfactor useful for hash and gist indexes?
I think hash does not need it, but gist might need it.Not sure. We don't know what type of index a GIST will be so we have no
way of knowing. I am thinking we can implement just btree now and the
GIST folks can add it later if they want. My guess is that each GIST is
going to behave differently for different fill-factors, so if allow it
to be set for GIST, GIST developers can pull the value if they want.
My understanding about hash was wrong. It uses fill factor of 75%, which is
hard-coded. On the other hand, GIST has no ability to control fill factor
currently. I'm trying to add fill factors to hash and gist, so I'll ask
index developers to review a patch in the future.
- Is it appropriate to use GUC variables to control fillfactors?
Is it better to extend CREATE INDEX / REINDEX grammar?I think it has to be part of CREATE INDEX and ALTER INDEX.
SQL standard has no regulation for indexes, so I refered to other databases.
- Oracle and DB2 : CREATE INDEX index ON table (...) PCTFREE 30;
- MS SQL Server : CREATE INDEX index ON table (...) WITH FILLFACTOR = 70;
PCTFREE seems to be common, so I'll extend DDL to use PCTFREE syntax.
The following two syntaxes will be able to be used.
1. SET btree_free_percent = 30;
CREATE INDEX index ON table (...);
SET btree_free_percent = 10; -- revert
2. CREATE INDEX index ON table (...) PCTFREE 30;
1 would be useful for a compatibe pg_dump format, per suggestion from Tom.
Is there a use for separate node and leaf settings?
We should use different settings for leaf and node, but it may confuse users.
So I'll simplify the setting as follows:
node_free_percent = Min(30%, 3 * leaf_free_percent)
When leaf_free_percent is 10%, node_free_percent is 30%. They are the same
values of the current implementation.
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories
Re: TODO-Item: B-tree fillfactor control
ITAGAKI Takahiro wrote:
Bruce Momjian <pgman@candle.pha.pa.us> wrote:
- Is fillfactor useful for hash and gist indexes?
I think hash does not need it, but gist might need it.Not sure. We don't know what type of index a GIST will be so we have no
way of knowing. I am thinking we can implement just btree now and the
GIST folks can add it later if they want. My guess is that each GIST is
going to behave differently for different fill-factors, so if allow it
to be set for GIST, GIST developers can pull the value if they want.My understanding about hash was wrong. It uses fill factor of 75%, which is
hard-coded. On the other hand, GIST has no ability to control fill factor
currently. I'm trying to add fill factors to hash and gist, so I'll ask
index developers to review a patch in the future.
OK.
- Is it appropriate to use GUC variables to control fillfactors?
Is it better to extend CREATE INDEX / REINDEX grammar?I think it has to be part of CREATE INDEX and ALTER INDEX.
SQL standard has no regulation for indexes, so I refered to other databases.
- Oracle and DB2 : CREATE INDEX index ON table (...) PCTFREE 30;
- MS SQL Server : CREATE INDEX index ON table (...) WITH FILLFACTOR = 70;PCTFREE seems to be common, so I'll extend DDL to use PCTFREE syntax.
The following two syntaxes will be able to be used.
1. SET btree_free_percent = 30;
CREATE INDEX index ON table (...);
SET btree_free_percent = 10; -- revert
2. CREATE INDEX index ON table (...) PCTFREE 30;1 would be useful for a compatibe pg_dump format, per suggestion from Tom.
I personally like FILLFACTOR, but I understand the desire to match
Oracle. PCTFREE seems too abreviated for me, but it would match the GUC
better, so maybe it is the best.
Is there a use for separate node and leaf settings?
We should use different settings for leaf and node, but it may confuse users.
So I'll simplify the setting as follows:
node_free_percent = Min(30%, 3 * leaf_free_percent)
When leaf_free_percent is 10%, node_free_percent is 30%. They are the same
values of the current implementation.
Yes, I think that is ideal.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073This is a draft patch for index fillfactor control discussed in
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00013.php
I added the following features:
- Add support for btree, hash and gist.
- Syntax extension using PCTFREE.
- Save settings to catalog. Next REINDEX will use the last value.
I'd like to ask index developers to review the patch, especially
the method to control fill factor for hash and gist.
I'll write documentations if there is no problem in the features.
Comments are welcome.
[Syntax extension]
- CREATE INDEX index ON table (columns) [ PCTFREE percent ];
- REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ PCTFREE percent ];
- ALTER INDEX index SET PCTFREE percent;
- PRIMARY KEY, UNIQUE constraint
CREATE TABLE / ALTER TABLE table ADD PRIMARY KEY [ PCTFREE percent ]
- with GUC
SET btree_free_percent = 30;
CREATE INDEX index ON table (...);
SET btree_free_percent = 10; -- revert
[Test and Result]
# CREATE table test1 (i int);
# INSERT INTO test1 SELECT generate_series(1, 100000);
# CREATE table test2 (c circle);
# INSERT INTO test2
# SELECT circle(point(100 * random(), 100 * random()), random())
# from generate_series(1, 100000);
# CREATE INDEX idx1_btree_0 ON test1 USING btree (i) PCTFREE 0;
# CREATE INDEX idx1_btree_10 ON test1 USING btree (i) PCTFREE 10;
# CREATE INDEX idx1_btree_30 ON test1 USING btree (i) PCTFREE 30;
# CREATE INDEX idx1_hash_0 ON test1 USING hash (i) PCTFREE 0;
# CREATE INDEX idx1_hash_25 ON test1 USING hash (i) PCTFREE 25;
# CREATE INDEX idx1_hash_40 ON test1 USING hash (i) PCTFREE 40;
# CREATE INDEX idx2_gist_0 ON test2 USING gist (c) PCTFREE 0;
# CREATE INDEX idx2_gist_10 ON test2 USING gist (c) PCTFREE 10;
# CREATE INDEX idx2_gist_30 ON test2 USING gist (c) PCTFREE 30;
# SELECT relname, relpages from pg_class where relname LIKE 'idx%' ORDER BY relname;
relname | relpages
---------------+----------
idx1_btree_0 | 249
idx1_btree_10 | 276 -- 249 / 0.9 = 277
idx1_btree_30 | 357 -- 249 / 0.7 = 356
idx1_hash_0 | 375
idx1_hash_25 | 413 -- Hash is not linear against fill factors.
idx1_hash_40 | 453 --
idx2_gist_0 | 882
idx2_gist_10 | 977 -- 882 / 0.9 = 980
idx2_gist_30 | 1273 -- 882 / 0.7 = 1260
(9 rows)
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories
Attachments:
index_free_percent-0206.patchapplication/octet-stream; name=index_free_percent-0206.patchDownload
diff -cpr pgsql-orig/doc/src/sgml/config.sgml pgsql/doc/src/sgml/config.sgml
*** pgsql-orig/doc/src/sgml/config.sgml 2006-02-06 12:47:39.000000000 +0900
--- pgsql/doc/src/sgml/config.sgml 2006-02-06 12:48:14.000000000 +0900
*************** SELECT * FROM parent WHERE key = 2400;
*** 3188,3193 ****
--- 3188,3209 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-default-free-percent" xreflabel="default_free_percent">
+ <term><varname>btree_free_percent</varname> (<type>integer</type>)</term>
+ <term><varname>hash_free_percent</varname> (<type>integer</type>)</term>
+ <term><varname>gist_free_percent</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>btree_free_percent</> configuration parameter</primary>
+ <primary><varname>hash_free_percent</> configuration parameter</primary>
+ <primary><varname>gist_free_percent</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ This variable specifies the default fill factors of indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
<term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
<indexterm>
diff -cpr pgsql-orig/doc/src/sgml/ref/alter_index.sgml pgsql/doc/src/sgml/ref/alter_index.sgml
*** pgsql-orig/doc/src/sgml/ref/alter_index.sgml 2006-02-06 12:47:39.000000000 +0900
--- pgsql/doc/src/sgml/ref/alter_index.sgml 2006-02-06 12:48:14.000000000 +0900
*************** PostgreSQL documentation
*** 22,27 ****
--- 22,28 ----
<synopsis>
ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
+ ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET PCTFREE <replaceable class="PARAMETER">percent</replaceable>
</synopsis>
</refsynopsisdiv>
*************** ALTER INDEX <replaceable class="PARAMETE
*** 56,61 ****
--- 57,74 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>SET PCTFREE</literal></term>
+ <listitem>
+ <para>
+ This form changes the index's fill factor to the specified percentage.
+ Actual index data will not be changed. Do REINDEX to reorganize.
+ See also
+ <xref linkend="SQL-REINDEX" endterm="sql-reindex-title">.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
diff -cpr pgsql-orig/doc/src/sgml/ref/create_table.sgml pgsql/doc/src/sgml/ref/create_table.sgml
*** pgsql-orig/doc/src/sgml/ref/create_table.sgml 2006-02-06 12:47:39.000000000 +0900
--- pgsql/doc/src/sgml/ref/create_table.sgml 2006-02-06 12:48:14.000000000 +0900
*************** where <replaceable class="PARAMETER">col
*** 36,43 ****
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL |
NULL |
! UNIQUE [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK (<replaceable class="PARAMETER">expression</replaceable>) |
REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
--- 36,43 ----
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL |
NULL |
! UNIQUE [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK (<replaceable class="PARAMETER">expression</replaceable>) |
REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
*************** where <replaceable class="PARAMETER">col
*** 46,53 ****
and <replaceable class="PARAMETER">table_constraint</replaceable> is:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
! { UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
--- 46,53 ----
and <replaceable class="PARAMETER">table_constraint</replaceable> is:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
! { UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
*************** and <replaceable class="PARAMETER">table
*** 616,621 ****
--- 616,634 ----
</varlistentry>
<varlistentry>
+ <term><literal>PCTFREE <replaceable class="PARAMETER">percent</replaceable></literal></term>
+ <listitem>
+ <para>
+ This clause allows selection of the fill factor on the index
+ associated with a <literal>UNIQUE</literal> or <literal>PRIMARY
+ KEY</literal> constraint will be created.
+ If not specified,
+ <xref linkend="guc-default-free-percent"> is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
<listitem>
<para>
diff -cpr pgsql-orig/doc/src/sgml/ref/reindex.sgml pgsql/doc/src/sgml/ref/reindex.sgml
*** pgsql-orig/doc/src/sgml/ref/reindex.sgml 2006-02-06 12:47:39.000000000 +0900
--- pgsql/doc/src/sgml/ref/reindex.sgml 2006-02-06 12:48:14.000000000 +0900
*************** PostgreSQL documentation
*** 20,26 ****
<refsynopsisdiv>
<synopsis>
! REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
</synopsis>
</refsynopsisdiv>
--- 20,26 ----
<refsynopsisdiv>
<synopsis>
! REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ FORCE ]
</synopsis>
</refsynopsisdiv>
*************** REINDEX { INDEX | TABLE | DATABASE | SYS
*** 117,122 ****
--- 117,131 ----
</varlistentry>
<varlistentry>
+ <term><literal>PCTFREE <replaceable class="PARAMETER">percent</replaceable></literal></term>
+ <listitem>
+ <para>
+ This form changes the index's fill factor to the specified percentage.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>FORCE</literal></term>
<listitem>
<para>
diff -cpr pgsql-orig/src/backend/access/gist/gist.c pgsql/src/backend/access/gist/gist.c
*** pgsql-orig/src/backend/access/gist/gist.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/gist/gist.c 2006-02-06 12:48:14.000000000 +0900
***************
*** 25,30 ****
--- 25,32 ----
const XLogRecPtr XLogRecPtrForTemp = {1, 1};
+ int gist_free_percent = 10;
+
/* Working state for gistbuild and its callback */
typedef struct
{
*************** static void gistbuildCallback(Relation i
*** 44,49 ****
--- 46,52 ----
void *state);
static void gistdoinsert(Relation r,
IndexTuple itup,
+ Size freespace,
GISTSTATE *GISTstate);
static void gistfindleaf(GISTInsertState *state,
GISTSTATE *giststate);
*************** gistbuild(PG_FUNCTION_ARGS)
*** 90,95 ****
--- 93,105 ----
double reltuples;
GISTBuildState buildstate;
Buffer buffer;
+ int save_free_percent = gist_free_percent;
+
+ /* set guc fill factor to specified value by command. */
+ if (indexInfo->ii_PctFree != -1)
+ gist_free_percent = indexInfo->ii_PctFree;
+
+ elog(DEBUG1, "gistbuild (gist_free_percent=%d)", gist_free_percent);
/*
* We expect to be called exactly once for any index relation. If that's
*************** gistbuild(PG_FUNCTION_ARGS)
*** 148,153 ****
--- 158,167 ----
/* okay, all heap tuples are indexed */
MemoryContextDelete(buildstate.tmpCtx);
+ /* revert guc fill factor. */
+ if (indexInfo->ii_PctFree != -1)
+ gist_free_percent = save_free_percent;
+
/* since we just counted the # of tuples, may as well update stats */
IndexCloseAndUpdateStats(heap, reltuples, index, buildstate.indtuples);
*************** gistbuildCallback(Relation index,
*** 204,210 ****
* you're inserting single tups, but not when you're initializing the
* whole index at once.
*/
! gistdoinsert(index, itup, &buildstate->giststate);
buildstate->indtuples += 1;
MemoryContextSwitchTo(oldCtx);
--- 218,225 ----
* you're inserting single tups, but not when you're initializing the
* whole index at once.
*/
! gistdoinsert(index, itup, BLCKSZ * gist_free_percent / 100,
! &buildstate->giststate);
buildstate->indtuples += 1;
MemoryContextSwitchTo(oldCtx);
*************** gistinsert(PG_FUNCTION_ARGS)
*** 261,267 ****
itup = index_form_tuple(giststate.tupdesc, values, isnull);
itup->t_tid = *ht_ctid;
! gistdoinsert(r, itup, &giststate);
/* cleanup */
freeGISTstate(&giststate);
--- 276,282 ----
itup = index_form_tuple(giststate.tupdesc, values, isnull);
itup->t_tid = *ht_ctid;
! gistdoinsert(r, itup, 0, &giststate);
/* cleanup */
freeGISTstate(&giststate);
*************** gistinsert(PG_FUNCTION_ARGS)
*** 278,284 ****
* so it does not bother releasing palloc'd allocations.
*/
static void
! gistdoinsert(Relation r, IndexTuple itup, GISTSTATE *giststate)
{
GISTInsertState state;
--- 293,299 ----
* so it does not bother releasing palloc'd allocations.
*/
static void
! gistdoinsert(Relation r, IndexTuple itup, Size freespace, GISTSTATE *giststate)
{
GISTInsertState state;
*************** gistdoinsert(Relation r, IndexTuple itup
*** 288,293 ****
--- 303,309 ----
state.itup[0] = (IndexTuple) palloc(IndexTupleSize(itup));
memcpy(state.itup[0], itup, IndexTupleSize(itup));
state.ituplen = 1;
+ state.freespace = freespace;
state.r = r;
state.key = itup->t_tid;
state.needInsertComplete = true;
*************** gistplacetopage(GISTInsertState *state,
*** 316,322 ****
PageIndexTupleDelete(state->stack->page, state->stack->childoffnum);
! if (gistnospace(state->stack->page, state->itup, state->ituplen))
{
/* no space for insertion */
IndexTuple *itvec,
--- 332,342 ----
PageIndexTupleDelete(state->stack->page, state->stack->childoffnum);
! /*
! * XXX: If fill factors for leaf and node should be different, use below or so.
! * freespace = (is_leaf ? state->leaf_freespace : state->node_freespace)
! */
! if (gistnospace(state->stack->page, state->itup, state->ituplen, state->freespace))
{
/* no space for insertion */
IndexTuple *itvec,
*************** gistSplit(Relation r,
*** 1035,1041 ****
for (i = 1; i < entryvec->n; i++)
cleaneditup[i - 1] = itup[realoffset[i] - 1];
! if (gistnospace(left, cleaneditup, lencleaneditup))
{
/* no space on left to put all good tuples, so picksplit */
gistUserPicksplit(r, entryvec, &v, cleaneditup, lencleaneditup, giststate);
--- 1055,1061 ----
for (i = 1; i < entryvec->n; i++)
cleaneditup[i - 1] = itup[realoffset[i] - 1];
! if (gistnospace(left, cleaneditup, lencleaneditup, 0))
{
/* no space on left to put all good tuples, so picksplit */
gistUserPicksplit(r, entryvec, &v, cleaneditup, lencleaneditup, giststate);
*************** gistSplit(Relation r,
*** 1108,1114 ****
}
/* write on disk (may need another split) */
! if (gistnospace(right, rvectup, v.spl_nright))
{
nlen = v.spl_nright;
newtup = gistSplit(r, rightbuf, rvectup, &nlen, dist, giststate);
--- 1128,1134 ----
}
/* write on disk (may need another split) */
! if (gistnospace(right, rvectup, v.spl_nright, 0))
{
nlen = v.spl_nright;
newtup = gistSplit(r, rightbuf, rvectup, &nlen, dist, giststate);
*************** gistSplit(Relation r,
*** 1140,1146 ****
ItemPointerSetBlockNumber(&(newtup[0]->t_tid), rbknum);
}
! if (gistnospace(left, lvectup, v.spl_nleft))
{
int llen = v.spl_nleft;
IndexTuple *lntup;
--- 1160,1166 ----
ItemPointerSetBlockNumber(&(newtup[0]->t_tid), rbknum);
}
! if (gistnospace(left, lvectup, v.spl_nleft, 0))
{
int llen = v.spl_nleft;
IndexTuple *lntup;
diff -cpr pgsql-orig/src/backend/access/gist/gistutil.c pgsql/src/backend/access/gist/gistutil.c
*** pgsql-orig/src/backend/access/gist/gistutil.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/gist/gistutil.c 2006-02-06 12:48:14.000000000 +0900
*************** gistfillbuffer(Relation r, Page page, In
*** 81,90 ****
* Check space for itup vector on page
*/
bool
! gistnospace(Page page, IndexTuple *itvec, int len)
{
! unsigned int size = 0;
! int i;
for (i = 0; i < len; i++)
size += IndexTupleSize(itvec[i]) + sizeof(ItemIdData);
--- 81,90 ----
* Check space for itup vector on page
*/
bool
! gistnospace(Page page, IndexTuple *itvec, int len, Size freespace)
{
! Size size = freespace;
! int i;
for (i = 0; i < len; i++)
size += IndexTupleSize(itvec[i]) + sizeof(ItemIdData);
diff -cpr pgsql-orig/src/backend/access/gist/gistvacuum.c pgsql/src/backend/access/gist/gistvacuum.c
*** pgsql-orig/src/backend/access/gist/gistvacuum.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/gist/gistvacuum.c 2006-02-06 12:48:14.000000000 +0900
*************** gistVacuumUpdate(GistVacuum *gv, BlockNu
*** 149,155 ****
if (curlenaddon)
{
/* insert updated tuples */
! if (gistnospace(page, addon, curlenaddon))
{
/* there is no space on page to insert tuples */
IndexTuple *vec;
--- 149,155 ----
if (curlenaddon)
{
/* insert updated tuples */
! if (gistnospace(page, addon, curlenaddon, 0 /* XXX: Should pctfree be used here? */))
{
/* there is no space on page to insert tuples */
IndexTuple *vec;
diff -cpr pgsql-orig/src/backend/access/gist/gistxlog.c pgsql/src/backend/access/gist/gistxlog.c
*** pgsql-orig/src/backend/access/gist/gistxlog.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/gist/gistxlog.c 2006-02-06 12:48:14.000000000 +0900
*************** gistContinueInsert(gistIncompleteInsert
*** 690,696 ****
}
}
! if (gistnospace(pages[numbuffer - 1], itup, lenitup))
{
/* no space left on page, so we should split */
buffers[numbuffer] = XLogReadBuffer(true, index, P_NEW);
--- 690,696 ----
}
}
! if (gistnospace(pages[numbuffer - 1], itup, lenitup, 0))
{
/* no space left on page, so we should split */
buffers[numbuffer] = XLogReadBuffer(true, index, P_NEW);
diff -cpr pgsql-orig/src/backend/access/hash/hash.c pgsql/src/backend/access/hash/hash.c
*** pgsql-orig/src/backend/access/hash/hash.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/hash/hash.c 2006-02-06 12:48:14.000000000 +0900
*************** hashbuild(PG_FUNCTION_ARGS)
*** 53,58 ****
--- 53,65 ----
IndexInfo *indexInfo = (IndexInfo *) PG_GETARG_POINTER(2);
double reltuples;
HashBuildState buildstate;
+ int save_free_percent = hash_free_percent;
+
+ /* set guc fill factor to specified value by command. */
+ if (indexInfo->ii_PctFree != -1)
+ hash_free_percent = indexInfo->ii_PctFree;
+
+ elog(DEBUG1, "hashbuild (hash_free_percent=%d)", hash_free_percent);
/*
* We expect to be called exactly once for any index relation. If that's
*************** hashbuild(PG_FUNCTION_ARGS)
*** 72,77 ****
--- 79,88 ----
reltuples = IndexBuildHeapScan(heap, index, indexInfo,
hashbuildCallback, (void *) &buildstate);
+ /* revert guc fill factor. */
+ if (indexInfo->ii_PctFree != -1)
+ hash_free_percent = save_free_percent;
+
/* since we just counted the # of tuples, may as well update stats */
IndexCloseAndUpdateStats(heap, reltuples, index, buildstate.indtuples);
diff -cpr pgsql-orig/src/backend/access/hash/hashpage.c pgsql/src/backend/access/hash/hashpage.c
*** pgsql-orig/src/backend/access/hash/hashpage.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/hash/hashpage.c 2006-02-06 12:48:14.000000000 +0900
*************** static void _hash_splitbucket(Relation r
*** 42,47 ****
--- 42,48 ----
uint32 maxbucket,
uint32 highmask, uint32 lowmask);
+ int hash_free_percent = 25;
/*
* We use high-concurrency locking on hash indexes (see README for an overview
*************** _hash_metapinit(Relation rel)
*** 251,257 ****
RelationGetDescr(rel)->attrs[0]->atttypmod);
item_width = MAXALIGN(sizeof(IndexTupleData)) + MAXALIGN(data_width) +
sizeof(ItemIdData); /* include the line pointer */
! ffactor = (BLCKSZ * 3 / 4) / item_width;
/* keep to a sane range */
if (ffactor < 10)
ffactor = 10;
--- 252,258 ----
RelationGetDescr(rel)->attrs[0]->atttypmod);
item_width = MAXALIGN(sizeof(IndexTupleData)) + MAXALIGN(data_width) +
sizeof(ItemIdData); /* include the line pointer */
! ffactor = BLCKSZ * (100 - hash_free_percent) / 100 / item_width;
/* keep to a sane range */
if (ffactor < 10)
ffactor = 10;
diff -cpr pgsql-orig/src/backend/access/nbtree/nbtree.c pgsql/src/backend/access/nbtree/nbtree.c
*** pgsql-orig/src/backend/access/nbtree/nbtree.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/nbtree/nbtree.c 2006-02-06 13:25:06.000000000 +0900
*************** btbuild(PG_FUNCTION_ARGS)
*** 70,75 ****
--- 70,82 ----
IndexInfo *indexInfo = (IndexInfo *) PG_GETARG_POINTER(2);
double reltuples;
BTBuildState buildstate;
+ int save_free_percent = btree_free_percent;
+
+ /* set guc fill factor to specified value by command. */
+ if (indexInfo->ii_PctFree != -1)
+ btree_free_percent = indexInfo->ii_PctFree;
+
+ elog(DEBUG1, "btbuild (btree_free_percent=%d)", btree_free_percent);
/*
* bootstrap processing does something strange, so don't use sort/build
*************** btbuild(PG_FUNCTION_ARGS)
*** 148,153 ****
--- 155,164 ----
}
#endif /* BTREE_BUILD_STATS */
+ /* revert guc fill factor. */
+ if (indexInfo->ii_PctFree != -1)
+ btree_free_percent = save_free_percent;
+
/* since we just counted the # of tuples, may as well update stats */
IndexCloseAndUpdateStats(heap, reltuples, index, buildstate.indtuples);
diff -cpr pgsql-orig/src/backend/access/nbtree/nbtsort.c pgsql/src/backend/access/nbtree/nbtsort.c
*** pgsql-orig/src/backend/access/nbtree/nbtsort.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/access/nbtree/nbtsort.c 2006-02-06 12:48:14.000000000 +0900
*************** typedef struct BTWriteState
*** 120,125 ****
--- 120,126 ----
static Page _bt_blnewpage(uint32 level);
+ static Size _bt_full_threshold(Size pagesize, bool leaf);
static BTPageState *_bt_pagestate(BTWriteState *wstate, uint32 level);
static void _bt_slideleft(Page page);
static void _bt_sortaddtup(Page page, Size itemsize,
*************** static void _bt_uppershutdown(BTWriteSta
*** 130,135 ****
--- 131,137 ----
static void _bt_load(BTWriteState *wstate,
BTSpool *btspool, BTSpool *btspool2);
+ int btree_free_percent = 10;
/*
* Interface routines
*************** _bt_blwritepage(BTWriteState *wstate, Pa
*** 327,332 ****
--- 329,349 ----
}
/*
+ * The steady-state load factor for btrees is usually estimated at 70%.
+ * We choose to pack leaf pages to 90% and upper pages to 70% as defaults.
+ */
+ static Size
+ _bt_full_threshold(Size pagesize, bool leaf)
+ {
+ int pctfree;
+ if (leaf)
+ pctfree = btree_free_percent;
+ else /* node */
+ pctfree = Min(30, 3 * btree_free_percent);
+ return pagesize * pctfree / 100;
+ }
+
+ /*
* allocate and initialize a new BTPageState. the returned structure
* is suitable for immediate use by _bt_buildadd.
*/
*************** _bt_pagestate(BTWriteState *wstate, uint
*** 346,355 ****
state->btps_lastoff = P_HIKEY;
state->btps_level = level;
/* set "full" threshold based on level. See notes at head of file. */
! if (level > 0)
! state->btps_full = (PageGetPageSize(state->btps_page) * 3) / 10;
! else
! state->btps_full = PageGetPageSize(state->btps_page) / 10;
/* no parent level, yet */
state->btps_next = NULL;
--- 363,370 ----
state->btps_lastoff = P_HIKEY;
state->btps_level = level;
/* set "full" threshold based on level. See notes at head of file. */
! state->btps_full = _bt_full_threshold(
! PageGetPageSize(state->btps_page), level == 0);
/* no parent level, yet */
state->btps_next = NULL;
diff -cpr pgsql-orig/src/backend/bootstrap/bootparse.y pgsql/src/backend/bootstrap/bootparse.y
*** pgsql-orig/src/backend/bootstrap/bootparse.y 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/bootstrap/bootparse.y 2006-02-06 12:48:14.000000000 +0900
*************** Boot_DeclareIndexStmt:
*** 251,257 ****
NULL,
$10,
NULL, NIL,
! false, false, false,
false, false, true, false);
do_end();
}
--- 251,257 ----
NULL,
$10,
NULL, NIL,
! false, false, false, -1,
false, false, true, false);
do_end();
}
*************** Boot_DeclareUniqueIndexStmt:
*** 269,275 ****
NULL,
$11,
NULL, NIL,
! true, false, false,
false, false, true, false);
do_end();
}
--- 269,275 ----
NULL,
$11,
NULL, NIL,
! true, false, false, -1,
false, false, true, false);
do_end();
}
diff -cpr pgsql-orig/src/backend/catalog/index.c pgsql/src/backend/catalog/index.c
*** pgsql-orig/src/backend/catalog/index.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/catalog/index.c 2006-02-06 12:48:14.000000000 +0900
*************** UpdateIndexRelation(Oid indexoid,
*** 410,415 ****
--- 410,416 ----
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
values[Anum_pg_index_indisclustered - 1] = BoolGetDatum(false);
+ values[Anum_pg_index_indpctfree - 1] = Int16GetDatum(indexInfo->ii_PctFree);
values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey);
values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass);
values[Anum_pg_index_indexprs - 1] = exprsDatum;
*************** BuildIndexInfo(Relation index)
*** 902,907 ****
--- 903,909 ----
/* other info */
ii->ii_Unique = indexStruct->indisunique;
+ ii->ii_PctFree = indexStruct->indpctfree;
return ii;
}
*************** index_build(Relation heapRelation,
*** 1343,1348 ****
--- 1345,1352 ----
*/
Assert(RelationIsValid(indexRelation));
Assert(PointerIsValid(indexRelation->rd_am));
+ Assert(indexInfo->ii_PctFree >= -1);
+ Assert(indexInfo->ii_PctFree <= MAX_PCTFREE);
procedure = indexRelation->rd_am->ambuild;
Assert(RegProcedureIsValid(procedure));
*************** IndexGetRelation(Oid indexId)
*** 1616,1622 ****
* reindex_index - This routine is used to recreate a single index
*/
void
! reindex_index(Oid indexId)
{
Relation iRel,
heapRelation;
--- 1620,1626 ----
* reindex_index - This routine is used to recreate a single index
*/
void
! reindex_index(Oid indexId, int pctfree)
{
Relation iRel,
heapRelation;
*************** reindex_index(Oid indexId)
*** 1663,1668 ****
--- 1667,1678 ----
/* Fetch info needed for index_build */
indexInfo = BuildIndexInfo(iRel);
+ if (pctfree != -1)
+ {
+ /* PCTFREE is specified, so update catalog. */
+ CatalogSetIndexPctfree(iRel, pctfree);
+ indexInfo->ii_PctFree = pctfree;
+ }
if (inplace)
{
*************** reindex_index(Oid indexId)
*** 1703,1709 ****
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, bool toast_too)
{
Relation rel;
Oid toast_relid;
--- 1713,1719 ----
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, bool toast_too, int pctfree)
{
Relation rel;
Oid toast_relid;
*************** reindex_relation(Oid relid, bool toast_t
*** 1761,1767 ****
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
! reindex_index(indexOid);
CommandCounterIncrement();
--- 1771,1777 ----
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
! reindex_index(indexOid, pctfree);
CommandCounterIncrement();
*************** reindex_relation(Oid relid, bool toast_t
*** 1784,1790 ****
* still hold the lock on the master table.
*/
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid, false);
return result;
}
--- 1794,1800 ----
* still hold the lock on the master table.
*/
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid, false, pctfree);
return result;
}
diff -cpr pgsql-orig/src/backend/catalog/indexing.c pgsql/src/backend/catalog/indexing.c
*** pgsql-orig/src/backend/catalog/indexing.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/catalog/indexing.c 2006-02-06 12:48:14.000000000 +0900
***************
*** 16,24 ****
--- 16,26 ----
#include "postgres.h"
#include "access/genam.h"
+ #include "access/heapam.h"
#include "catalog/index.h"
#include "catalog/indexing.h"
#include "executor/executor.h"
+ #include "utils/syscache.h"
/*
*************** CatalogUpdateIndexes(Relation heapRel, H
*** 149,151 ****
--- 151,196 ----
CatalogIndexInsert(indstate, heapTuple);
CatalogCloseIndexes(indstate);
}
+
+ void
+ CatalogSetIndexPctfree(Relation index, int pctfree)
+ {
+ Relation indexes;
+ HeapTuple tuple;
+ Form_pg_index indexDesc;
+ Oid indexOid = RelationGetRelid(index);
+
+ /*
+ * Limit target to a sane range
+ */
+ if (pctfree < -1 || pctfree > MAX_PCTFREE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PCTFREE %d must be between 0 and %d",
+ pctfree, MAX_PCTFREE)));
+
+ indexes = heap_open(IndexRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy(INDEXRELID,
+ ObjectIdGetDatum(indexOid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for index %u", indexOid);
+
+ indexDesc = (Form_pg_index) GETSTRUCT(tuple);
+
+ /*
+ * XXX: Is it legal to overwrite index->rd_index->indpctfree?
+ * There was a bug that REINDEX just after ALTER INDEX SET PCTFREE
+ * didn't use the specified value. This is intended to fix it, but
+ * invalidating cache of index->rd_index might be better.
+ */
+ index->rd_index->indpctfree = pctfree;
+
+ indexDesc->indpctfree = pctfree;
+ simple_heap_update(indexes, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(indexes, tuple);
+
+ heap_freetuple(tuple);
+ heap_close(indexes, RowExclusiveLock);
+ }
diff -cpr pgsql-orig/src/backend/commands/cluster.c pgsql/src/backend/commands/cluster.c
*** pgsql-orig/src/backend/commands/cluster.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/commands/cluster.c 2006-02-06 12:48:14.000000000 +0900
*************** rebuild_relation(Relation OldHeap, Oid i
*** 547,553 ****
* all-new at this point). We do not need CommandCounterIncrement()
* because reindex_relation does it.
*/
! reindex_relation(tableOid, false);
}
/*
--- 547,553 ----
* all-new at this point). We do not need CommandCounterIncrement()
* because reindex_relation does it.
*/
! reindex_relation(tableOid, false, /* use last pctfree */ -1);
}
/*
diff -cpr pgsql-orig/src/backend/commands/indexcmds.c pgsql/src/backend/commands/indexcmds.c
*** pgsql-orig/src/backend/commands/indexcmds.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/commands/indexcmds.c 2006-02-06 12:48:14.000000000 +0900
*************** static bool relationHasPrimaryKey(Relati
*** 79,84 ****
--- 79,85 ----
* 'isconstraint': index is for a PRIMARY KEY or UNIQUE constraint,
* so build a pg_constraint entry for it.
* 'is_alter_table': this is due to an ALTER rather than a CREATE operation.
+ * 'pctfree': fill factor.
* 'check_rights': check for CREATE rights in the namespace. (This should
* be true except when ALTER is deleting/recreating an index.)
* 'skip_build': make the catalog entries but leave the index file empty;
*************** DefineIndex(RangeVar *heapRelation,
*** 97,102 ****
--- 98,104 ----
bool unique,
bool primary,
bool isconstraint,
+ int pctfree,
bool is_alter_table,
bool check_rights,
bool skip_build,
*************** DefineIndex(RangeVar *heapRelation,
*** 378,383 ****
--- 380,386 ----
indexInfo->ii_Predicate = make_ands_implicit(predicate);
indexInfo->ii_PredicateState = NIL;
indexInfo->ii_Unique = unique;
+ indexInfo->ii_PctFree = pctfree;
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
ComputeIndexAttrs(indexInfo, classObjectId, attributeList,
*************** RemoveIndex(RangeVar *relation, DropBeha
*** 914,920 ****
* Recreate a specific index.
*/
void
! ReindexIndex(RangeVar *indexRelation)
{
Oid indOid;
HeapTuple tuple;
--- 917,923 ----
* Recreate a specific index.
*/
void
! ReindexIndex(RangeVar *indexRelation, int pctfree)
{
Oid indOid;
HeapTuple tuple;
*************** ReindexIndex(RangeVar *indexRelation)
*** 939,945 ****
ReleaseSysCache(tuple);
! reindex_index(indOid);
}
/*
--- 942,948 ----
ReleaseSysCache(tuple);
! reindex_index(indOid, pctfree);
}
/*
*************** ReindexIndex(RangeVar *indexRelation)
*** 947,953 ****
* Recreate all indexes of a table (and of its toast table, if any)
*/
void
! ReindexTable(RangeVar *relation)
{
Oid heapOid;
HeapTuple tuple;
--- 950,956 ----
* Recreate all indexes of a table (and of its toast table, if any)
*/
void
! ReindexTable(RangeVar *relation, int pctfree)
{
Oid heapOid;
HeapTuple tuple;
*************** ReindexTable(RangeVar *relation)
*** 980,986 ****
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, true))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
--- 983,989 ----
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, true, pctfree))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
*************** ReindexTable(RangeVar *relation)
*** 994,1000 ****
* separate transaction, so we can release the lock on it right away.
*/
void
! ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
{
Relation relationRelation;
HeapScanDesc scan;
--- 997,1003 ----
* separate transaction, so we can release the lock on it right away.
*/
void
! ReindexDatabase(const char *databaseName, int pctfree, bool do_system, bool do_user)
{
Relation relationRelation;
HeapScanDesc scan;
*************** ReindexDatabase(const char *databaseName
*** 1099,1105 ****
StartTransactionCommand();
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
! if (reindex_relation(relid, true))
ereport(NOTICE,
(errmsg("table \"%s\" was reindexed",
get_rel_name(relid))));
--- 1102,1108 ----
StartTransactionCommand();
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
! if (reindex_relation(relid, true, pctfree))
ereport(NOTICE,
(errmsg("table \"%s\" was reindexed",
get_rel_name(relid))));
diff -cpr pgsql-orig/src/backend/commands/tablecmds.c pgsql/src/backend/commands/tablecmds.c
*** pgsql-orig/src/backend/commands/tablecmds.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/commands/tablecmds.c 2006-02-06 12:48:14.000000000 +0900
*************** static void ATPrepSetTableSpace(AlteredT
*** 245,250 ****
--- 245,252 ----
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
bool enable, bool skip_system);
+ static void ATPrepSetPctfree(Relation rel);
+ static void ATExecSetPctfree(Relation rel, Node *newValue);
static void copy_relation_data(Relation rel, SMgrRelation dst);
static void update_ri_trigger_args(Oid relid,
const char *oldname,
*************** ExecuteTruncate(List *relations)
*** 629,635 ****
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, true);
}
}
--- 631,637 ----
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, true, /* use last pctfree */ -1);
}
}
*************** ATPrepCmd(List **wqueue, Relation rel, A
*** 2008,2013 ****
--- 2010,2019 ----
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
+ case AT_SetPctfree:
+ ATPrepSetPctfree(rel);
+ pass = AT_PASS_ADD_CONSTR;
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
*************** ATExecCmd(AlteredTableInfo *tab, Relatio
*** 2183,2188 ****
--- 2189,2197 ----
case AT_DisableTrigUser: /* DISABLE TRIGGER USER */
ATExecEnableDisableTrigger(rel, NULL, false, true);
break;
+ case AT_SetPctfree: /* SET PCTFREE */
+ ATExecSetPctfree(rel, cmd->def);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
*************** ATRewriteTables(List **wqueue)
*** 2297,2303 ****
* which is all-new anyway). We do not need
* CommandCounterIncrement() because reindex_relation does it.
*/
! reindex_relation(tab->relid, false);
}
else
{
--- 2306,2312 ----
* which is all-new anyway). We do not need
* CommandCounterIncrement() because reindex_relation does it.
*/
! reindex_relation(tab->relid, false, /* use last pctfree */ -1);
}
else
{
*************** ATExecAddIndex(AlteredTableInfo *tab, Re
*** 3697,3702 ****
--- 3706,3712 ----
stmt->unique,
stmt->primary,
stmt->isconstraint,
+ stmt->pctfree,
true, /* is_alter_table */
check_rights,
skip_build,
*************** ATExecAddConstraint(AlteredTableInfo *ta
*** 3797,3802 ****
--- 3807,3847 ----
}
/*
+ * ALTER IONDEX SET PCTFREE
+ */
+ static void
+ ATPrepSetPctfree(Relation rel)
+ {
+ if (rel->rd_rel->relkind != RELKIND_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a index",
+ RelationGetRelationName(rel))));
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+ }
+
+ static void
+ ATExecSetPctfree(Relation rel, Node *newValue)
+ {
+ int pctfree;
+
+ Assert(rel->rd_rel->relkind == RELKIND_INDEX);
+ Assert(IsA(newValue, Integer));
+
+ pctfree = intVal(newValue);
+ CatalogSetIndexPctfree(rel, pctfree);
+ CommandCounterIncrement();
+
+ /* Only catalog was changed. Actual change delays until next reindex. */
+ elog(NOTICE, "PCTFREE for %s has changed to %d. Do REINDEX to reorganize.",
+ RelationGetRelationName(rel), pctfree);
+ }
+
+ /*
* Add a foreign-key constraint to a single table
*
* Subroutine for ATExecAddConstraint. Must already hold exclusive
*************** AlterTableCreateToastTable(Oid relOid, b
*** 5991,5996 ****
--- 6036,6042 ----
indexInfo->ii_Predicate = NIL;
indexInfo->ii_PredicateState = NIL;
indexInfo->ii_Unique = true;
+ indexInfo->ii_PctFree = -1;
classObjectId[0] = OID_BTREE_OPS_OID;
classObjectId[1] = INT4_BTREE_OPS_OID;
diff -cpr pgsql-orig/src/backend/nodes/copyfuncs.c pgsql/src/backend/nodes/copyfuncs.c
*** pgsql-orig/src/backend/nodes/copyfuncs.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/nodes/copyfuncs.c 2006-02-06 12:48:14.000000000 +0900
*************** _copyConstraint(Constraint *from)
*** 1622,1627 ****
--- 1622,1628 ----
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_NODE_FIELD(keys);
+ COPY_SCALAR_FIELD(pctfree);
COPY_STRING_FIELD(indexspace);
return newnode;
*************** _copyIndexStmt(IndexStmt *from)
*** 2002,2007 ****
--- 2003,2009 ----
COPY_SCALAR_FIELD(unique);
COPY_SCALAR_FIELD(primary);
COPY_SCALAR_FIELD(isconstraint);
+ COPY_SCALAR_FIELD(pctfree);
return newnode;
}
*************** _copyReindexStmt(ReindexStmt *from)
*** 2554,2559 ****
--- 2556,2562 ----
COPY_SCALAR_FIELD(kind);
COPY_NODE_FIELD(relation);
COPY_STRING_FIELD(name);
+ COPY_SCALAR_FIELD(pctfree);
COPY_SCALAR_FIELD(do_system);
COPY_SCALAR_FIELD(do_user);
diff -cpr pgsql-orig/src/backend/nodes/equalfuncs.c pgsql/src/backend/nodes/equalfuncs.c
*** pgsql-orig/src/backend/nodes/equalfuncs.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/nodes/equalfuncs.c 2006-02-06 12:48:14.000000000 +0900
*************** _equalIndexStmt(IndexStmt *a, IndexStmt
*** 965,970 ****
--- 965,971 ----
COMPARE_SCALAR_FIELD(unique);
COMPARE_SCALAR_FIELD(primary);
COMPARE_SCALAR_FIELD(isconstraint);
+ COMPARE_SCALAR_FIELD(pctfree);
return true;
}
*************** _equalReindexStmt(ReindexStmt *a, Reinde
*** 1428,1433 ****
--- 1429,1435 ----
COMPARE_SCALAR_FIELD(kind);
COMPARE_NODE_FIELD(relation);
COMPARE_STRING_FIELD(name);
+ COMPARE_SCALAR_FIELD(pctfree);
COMPARE_SCALAR_FIELD(do_system);
COMPARE_SCALAR_FIELD(do_user);
*************** _equalConstraint(Constraint *a, Constrai
*** 1686,1691 ****
--- 1688,1694 ----
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_NODE_FIELD(keys);
+ COMPARE_SCALAR_FIELD(pctfree);
COMPARE_STRING_FIELD(indexspace);
return true;
diff -cpr pgsql-orig/src/backend/nodes/outfuncs.c pgsql/src/backend/nodes/outfuncs.c
*** pgsql-orig/src/backend/nodes/outfuncs.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/nodes/outfuncs.c 2006-02-06 12:48:14.000000000 +0900
*************** _outIndexStmt(StringInfo str, IndexStmt
*** 1345,1350 ****
--- 1345,1351 ----
WRITE_BOOL_FIELD(unique);
WRITE_BOOL_FIELD(primary);
WRITE_BOOL_FIELD(isconstraint);
+ WRITE_INT_FIELD(pctfree);
}
static void
*************** _outConstraint(StringInfo str, Constrain
*** 1743,1754 ****
--- 1744,1757 ----
case CONSTR_PRIMARY:
appendStringInfo(str, "PRIMARY_KEY");
WRITE_NODE_FIELD(keys);
+ WRITE_INT_FIELD(pctfree);
WRITE_STRING_FIELD(indexspace);
break;
case CONSTR_UNIQUE:
appendStringInfo(str, "UNIQUE");
WRITE_NODE_FIELD(keys);
+ WRITE_INT_FIELD(pctfree);
WRITE_STRING_FIELD(indexspace);
break;
diff -cpr pgsql-orig/src/backend/parser/analyze.c pgsql/src/backend/parser/analyze.c
*** pgsql-orig/src/backend/parser/analyze.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/parser/analyze.c 2006-02-06 12:48:14.000000000 +0900
*************** transformIndexConstraints(ParseState *ps
*** 1219,1224 ****
--- 1219,1225 ----
index->relation = cxt->relation;
index->accessMethod = DEFAULT_INDEX_TYPE;
+ index->pctfree = constraint->pctfree;
index->tableSpace = constraint->indexspace;
index->indexParams = NIL;
index->whereClause = NULL;
diff -cpr pgsql-orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y
*** pgsql-orig/src/backend/parser/gram.y 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/parser/gram.y 2006-02-06 12:48:14.000000000 +0900
*************** static void doNegateFloat(Value *v);
*** 161,168 ****
%type <node> alter_column_default opclass_item alter_using
%type <ival> add_drop
! %type <node> alter_table_cmd alter_rel_cmd
! %type <list> alter_table_cmds alter_rel_cmds
%type <dbehavior> opt_drop_behavior
--- 161,168 ----
%type <node> alter_column_default opclass_item alter_using
%type <ival> add_drop
! %type <node> alter_table_cmd alter_index_cmd alter_rel_cmd
! %type <list> alter_table_cmds alter_index_cmds
%type <dbehavior> opt_drop_behavior
*************** static void doNegateFloat(Value *v);
*** 250,256 ****
%type <list> extract_list overlay_list position_list
%type <list> substr_list trim_list
! %type <ival> opt_interval
%type <node> overlay_placing substr_from substr_for
%type <boolean> opt_instead opt_analyze
--- 250,256 ----
%type <list> extract_list overlay_list position_list
%type <list> substr_list trim_list
! %type <ival> opt_interval opt_pctfree
%type <node> overlay_placing substr_from substr_for
%type <boolean> opt_instead opt_analyze
*************** static void doNegateFloat(Value *v);
*** 386,392 ****
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
! PARTIAL PASSWORD PLACING POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
--- 386,392 ----
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
! PARTIAL PASSWORD PLACING PCTFREE POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
*************** AlterTableStmt:
*** 1313,1319 ****
n->relkind = OBJECT_TABLE;
$$ = (Node *)n;
}
! | ALTER INDEX relation_expr alter_rel_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n->relation = $3;
--- 1313,1319 ----
n->relkind = OBJECT_TABLE;
$$ = (Node *)n;
}
! | ALTER INDEX relation_expr alter_index_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n->relation = $3;
*************** alter_table_cmds:
*** 1328,1333 ****
--- 1328,1338 ----
| alter_table_cmds ',' alter_table_cmd { $$ = lappend($1, $3); }
;
+ alter_index_cmds:
+ alter_index_cmd { $$ = list_make1($1); }
+ | alter_index_cmds ',' alter_index_cmd { $$ = lappend($1, $3); }
+ ;
+
/* Subcommands that are for ALTER TABLE only */
alter_table_cmd:
/* ALTER TABLE <relation> ADD [COLUMN] <coldef> */
*************** alter_table_cmd:
*** 1500,1508 ****
}
;
! alter_rel_cmds:
! alter_rel_cmd { $$ = list_make1($1); }
! | alter_rel_cmds ',' alter_rel_cmd { $$ = lappend($1, $3); }
;
/* Subcommands that are for ALTER TABLE or ALTER INDEX */
--- 1505,1525 ----
}
;
! /* Subcommands that are for ALTER INDEX only */
! alter_index_cmd:
! /* ALTER INDEX <index> SET PCTFREE <IntegerOnly> */
! /* XXX: Need revert command? (ex. SET PCTFREE DEFAULT) */
! SET PCTFREE IntegerOnly
! {
! AlterTableCmd *n = makeNode(AlterTableCmd);
! n->subtype = AT_SetPctfree;
! n->def = (Node *) $3;
! $$ = (Node *)n;
! }
! | alter_rel_cmd
! {
! $$ = $1;
! }
;
/* Subcommands that are for ALTER TABLE or ALTER INDEX */
*************** ColConstraintElem:
*** 1844,1849 ****
--- 1861,1867 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
*************** ColConstraintElem:
*** 1855,1864 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
--- 1873,1883 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
*************** ColConstraintElem:
*** 1866,1875 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->indexspace = $2;
$$ = (Node *)n;
}
! | PRIMARY KEY OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
--- 1885,1895 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->pctfree = $2;
! n->indexspace = $3;
$$ = (Node *)n;
}
! | PRIMARY KEY opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
*************** ColConstraintElem:
*** 1877,1883 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->indexspace = $3;
$$ = (Node *)n;
}
| CHECK '(' a_expr ')'
--- 1897,1904 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->pctfree = $3;
! n->indexspace = $4;
$$ = (Node *)n;
}
| CHECK '(' a_expr ')'
*************** ColConstraintElem:
*** 1888,1893 ****
--- 1909,1915 ----
n->raw_expr = $3;
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
*************** ColConstraintElem:
*** 1907,1912 ****
--- 1929,1935 ----
}
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
*************** ConstraintElem:
*** 2028,2037 ****
n->name = NULL;
n->raw_expr = $3;
n->cooked_expr = NULL;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE '(' columnList ')' OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
--- 2051,2061 ----
n->name = NULL;
n->raw_expr = $3;
n->cooked_expr = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE '(' columnList ')' opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
*************** ConstraintElem:
*** 2039,2048 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $3;
! n->indexspace = $5;
$$ = (Node *)n;
}
! | PRIMARY KEY '(' columnList ')' OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
--- 2063,2073 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $3;
! n->pctfree = $5;
! n->indexspace = $6;
$$ = (Node *)n;
}
! | PRIMARY KEY '(' columnList ')' opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
*************** ConstraintElem:
*** 2050,2056 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $4;
! n->indexspace = $6;
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
--- 2075,2082 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $4;
! n->pctfree = $6;
! n->indexspace = $7;
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
*************** opt_granted_by: GRANTED BY RoleId {
*** 3505,3511 ****
*****************************************************************************/
IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name
! access_method_clause '(' index_params ')' OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
--- 3531,3537 ----
*****************************************************************************/
IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name
! access_method_clause '(' index_params ')' opt_pctfree OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
*************** IndexStmt: CREATE index_opt_unique INDEX
*** 3513,3520 ****
n->relation = $6;
n->accessMethod = $7;
n->indexParams = $9;
! n->tableSpace = $11;
! n->whereClause = $12;
$$ = (Node *)n;
}
;
--- 3539,3547 ----
n->relation = $6;
n->accessMethod = $7;
n->indexParams = $9;
! n->pctfree = $11;
! n->tableSpace = $12;
! n->whereClause = $13;
$$ = (Node *)n;
}
;
*************** DropCastStmt: DROP CAST '(' Typename AS
*** 3956,3985 ****
*****************************************************************************/
ReindexStmt:
! REINDEX reindex_type qualified_name opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->relation = $3;
n->name = NULL;
$$ = (Node *)n;
}
! | REINDEX SYSTEM_P name opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
n->relation = NULL;
n->do_system = true;
n->do_user = false;
$$ = (Node *)n;
}
! | REINDEX DATABASE name opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
n->relation = NULL;
n->do_system = true;
n->do_user = true;
$$ = (Node *)n;
--- 3983,4015 ----
*****************************************************************************/
ReindexStmt:
! REINDEX reindex_type qualified_name opt_pctfree opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->relation = $3;
+ n->pctfree = $4;
n->name = NULL;
$$ = (Node *)n;
}
! | REINDEX SYSTEM_P name opt_pctfree opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
+ n->pctfree = $4;
n->relation = NULL;
n->do_system = true;
n->do_user = false;
$$ = (Node *)n;
}
! | REINDEX DATABASE name opt_pctfree opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
n->relation = NULL;
+ n->pctfree = $4;
n->do_system = true;
n->do_user = true;
$$ = (Node *)n;
*************** reindex_type:
*** 3991,3996 ****
--- 4021,4043 ----
| TABLE { $$ = OBJECT_TABLE; }
;
+ opt_pctfree:
+ PCTFREE Iconst
+ {
+ if ($2 < 0 || $2 > MAX_PCTFREE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PCTFREE %d must be between 0 and %d",
+ $2, MAX_PCTFREE)));
+ $$ = $2;
+ }
+ | /*EMPTY*/
+ {
+ /* Insert "-1" meaning "use default" */
+ $$ = -1;
+ }
+ ;
+
opt_force: FORCE { $$ = TRUE; }
| /* EMPTY */ { $$ = FALSE; }
;
diff -cpr pgsql-orig/src/backend/parser/keywords.c pgsql/src/backend/parser/keywords.c
*** pgsql-orig/src/backend/parser/keywords.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/parser/keywords.c 2006-02-06 12:48:14.000000000 +0900
*************** static const ScanKeyword ScanKeywords[]
*** 256,261 ****
--- 256,262 ----
{"owner", OWNER},
{"partial", PARTIAL},
{"password", PASSWORD},
+ {"pctfree", PCTFREE},
{"placing", PLACING},
{"position", POSITION},
{"precision", PRECISION},
diff -cpr pgsql-orig/src/backend/tcop/utility.c pgsql/src/backend/tcop/utility.c
*** pgsql-orig/src/backend/tcop/utility.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/tcop/utility.c 2006-02-06 12:48:14.000000000 +0900
*************** ProcessUtility(Node *parsetree,
*** 794,799 ****
--- 794,800 ----
stmt->unique,
stmt->primary,
stmt->isconstraint,
+ stmt->pctfree,
false, /* is_alter_table */
true, /* check_rights */
false, /* skip_build */
*************** ProcessUtility(Node *parsetree,
*** 1052,1064 ****
switch (stmt->kind)
{
case OBJECT_INDEX:
! ReindexIndex(stmt->relation);
break;
case OBJECT_TABLE:
! ReindexTable(stmt->relation);
break;
case OBJECT_DATABASE:
! ReindexDatabase(stmt->name,
stmt->do_system, stmt->do_user);
break;
default:
--- 1053,1065 ----
switch (stmt->kind)
{
case OBJECT_INDEX:
! ReindexIndex(stmt->relation, stmt->pctfree);
break;
case OBJECT_TABLE:
! ReindexTable(stmt->relation, stmt->pctfree);
break;
case OBJECT_DATABASE:
! ReindexDatabase(stmt->name, stmt->pctfree,
stmt->do_system, stmt->do_user);
break;
default:
diff -cpr pgsql-orig/src/backend/utils/misc/guc.c pgsql/src/backend/utils/misc/guc.c
*** pgsql-orig/src/backend/utils/misc/guc.c 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/utils/misc/guc.c 2006-02-06 12:48:14.000000000 +0900
***************
*** 29,34 ****
--- 29,35 ----
#include "utils/guc_tables.h"
#include "access/twophase.h"
+ #include "catalog/index.h"
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "commands/async.h"
*************** static struct config_int ConfigureNamesI
*** 1570,1575 ****
--- 1571,1603 ----
0, 0, INT_MAX, assign_tcp_keepalives_count, show_tcp_keepalives_count
},
+ {
+ {"btree_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for btree pages during index creation."),
+ NULL
+ },
+ &btree_free_percent,
+ 10, 0, MAX_PCTFREE, NULL, NULL
+ },
+
+ {
+ {"hash_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for hash pages during index creation."),
+ NULL
+ },
+ &hash_free_percent,
+ 25, 0, MAX_PCTFREE, NULL, NULL
+ },
+
+ {
+ {"gist_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for gist pages during index creation."),
+ NULL
+ },
+ &gist_free_percent,
+ 10, 0, MAX_PCTFREE, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL
diff -cpr pgsql-orig/src/backend/utils/misc/postgresql.conf.sample pgsql/src/backend/utils/misc/postgresql.conf.sample
*** pgsql-orig/src/backend/utils/misc/postgresql.conf.sample 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/backend/utils/misc/postgresql.conf.sample 2006-02-06 12:48:14.000000000 +0900
***************
*** 371,376 ****
--- 371,379 ----
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#statement_timeout = 0 # 0 is disabled, in milliseconds
+ #btree_free_percent = 10 # 0-50% of free space
+ #hash_free_percent = 25 # 0-50% of free space
+ #gist_free_percent = 10 # 0-50% of free space
# - Locale and Formatting -
diff -cpr pgsql-orig/src/bin/psql/tab-complete.c pgsql/src/bin/psql/tab-complete.c
*** pgsql-orig/src/bin/psql/tab-complete.c 2006-02-06 12:47:39.000000000 +0900
--- pgsql/src/bin/psql/tab-complete.c 2006-02-06 12:48:14.000000000 +0900
*************** psql_completion(char *text, int start, i
*** 632,638 ****
pg_strcasecmp(prev2_wd, "INDEX") == 0)
{
static const char *const list_ALTERINDEX[] =
! {"SET TABLESPACE", "OWNER TO", "RENAME TO", NULL};
COMPLETE_WITH_LIST(list_ALTERINDEX);
}
--- 632,638 ----
pg_strcasecmp(prev2_wd, "INDEX") == 0)
{
static const char *const list_ALTERINDEX[] =
! {"SET TABLESPACE", "SET PCTFREE", "OWNER TO", "RENAME TO", NULL};
COMPLETE_WITH_LIST(list_ALTERINDEX);
}
diff -cpr pgsql-orig/src/include/access/gist_private.h pgsql/src/include/access/gist_private.h
*** pgsql-orig/src/include/access/gist_private.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/access/gist_private.h 2006-02-06 12:48:14.000000000 +0900
*************** typedef struct
*** 181,186 ****
--- 181,187 ----
Relation r;
IndexTuple *itup; /* in/out, points to compressed entry */
int ituplen; /* length of itup */
+ Size freespace; /* free space to be left */
GISTInsertStack *stack;
bool needInsertComplete;
*************** extern void gistcheckpage(Relation rel,
*** 267,273 ****
extern Buffer gistNewBuffer(Relation r);
extern OffsetNumber gistfillbuffer(Relation r, Page page, IndexTuple *itup,
int len, OffsetNumber off);
! extern bool gistnospace(Page page, IndexTuple *itvec, int len);
extern IndexTuple *gistextractbuffer(Buffer buffer, int *len /* out */ );
extern IndexTuple *gistjoinvector(
IndexTuple *itvec, int *len,
--- 268,274 ----
extern Buffer gistNewBuffer(Relation r);
extern OffsetNumber gistfillbuffer(Relation r, Page page, IndexTuple *itup,
int len, OffsetNumber off);
! extern bool gistnospace(Page page, IndexTuple *itvec, int len, Size freespace);
extern IndexTuple *gistextractbuffer(Buffer buffer, int *len /* out */ );
extern IndexTuple *gistjoinvector(
IndexTuple *itvec, int *len,
diff -cpr pgsql-orig/src/include/catalog/index.h pgsql/src/include/catalog/index.h
*** pgsql-orig/src/include/catalog/index.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/catalog/index.h 2006-02-06 12:48:14.000000000 +0900
***************
*** 21,26 ****
--- 21,34 ----
#define DEFAULT_INDEX_TYPE "btree"
+ /*
+ * Knobs for fill factor control during index building.
+ */
+ #define MAX_PCTFREE 50
+ extern int btree_free_percent;
+ extern int hash_free_percent;
+ extern int gist_free_percent;
+
/* Typedef for callback function for IndexBuildHeapScan */
typedef void (*IndexBuildCallback) (Relation index,
HeapTuple htup,
*************** extern double IndexBuildHeapScan(Relatio
*** 69,75 ****
IndexBuildCallback callback,
void *callback_state);
! extern void reindex_index(Oid indexId);
! extern bool reindex_relation(Oid relid, bool toast_too);
#endif /* INDEX_H */
--- 77,83 ----
IndexBuildCallback callback,
void *callback_state);
! extern void reindex_index(Oid indexId, int pctfree);
! extern bool reindex_relation(Oid relid, bool toast_too, int pctfree);
#endif /* INDEX_H */
diff -cpr pgsql-orig/src/include/catalog/indexing.h pgsql/src/include/catalog/indexing.h
*** pgsql-orig/src/include/catalog/indexing.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/catalog/indexing.h 2006-02-06 12:48:14.000000000 +0900
*************** extern void CatalogCloseIndexes(CatalogI
*** 33,38 ****
--- 33,39 ----
extern void CatalogIndexInsert(CatalogIndexState indstate,
HeapTuple heapTuple);
extern void CatalogUpdateIndexes(Relation heapRel, HeapTuple heapTuple);
+ extern void CatalogSetIndexPctfree(Relation index, int pctfree);
/*
diff -cpr pgsql-orig/src/include/catalog/pg_attribute.h pgsql/src/include/catalog/pg_attribute.h
*** pgsql-orig/src/include/catalog/pg_attribute.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/catalog/pg_attribute.h 2006-02-06 12:48:14.000000000 +0900
*************** DATA(insert ( 1259 tableoid 26 0 4 -
*** 454,462 ****
{ 0, {"indisunique"}, 16, -1, 1, 4, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisprimary"}, 16, -1, 1, 5, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisclustered"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
! { 0, {"indkey"}, 22, -1, -1, 7, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indclass"}, 30, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indexprs"}, 25, -1, -1, 9, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \
! { 0, {"indpred"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }
#endif /* PG_ATTRIBUTE_H */
--- 454,463 ----
{ 0, {"indisunique"}, 16, -1, 1, 4, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisprimary"}, 16, -1, 1, 5, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisclustered"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
! { 0, {"indpctfree"}, 21, -1, 2, 7, 0, -1, -1, true, 'p', 's', true, false, false, true, 0 }, \
! { 0, {"indkey"}, 22, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indclass"}, 30, -1, -1, 9, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indexprs"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \
! { 0, {"indpred"}, 25, -1, -1, 11, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }
#endif /* PG_ATTRIBUTE_H */
diff -cpr pgsql-orig/src/include/catalog/pg_index.h pgsql/src/include/catalog/pg_index.h
*** pgsql-orig/src/include/catalog/pg_index.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/catalog/pg_index.h 2006-02-06 12:48:14.000000000 +0900
*************** CATALOG(pg_index,2610) BKI_WITHOUT_OIDS
*** 41,46 ****
--- 41,47 ----
bool indisunique; /* is this a unique index? */
bool indisprimary; /* is this index for primary key? */
bool indisclustered; /* is this the index last clustered by? */
+ int2 indpctfree; /* fill factor */
/* VARIABLE LENGTH FIELDS: */
int2vector indkey; /* column numbers of indexed cols, or 0 */
*************** typedef FormData_pg_index *Form_pg_index
*** 63,78 ****
* compiler constants for pg_index
* ----------------
*/
! #define Natts_pg_index 10
#define Anum_pg_index_indexrelid 1
#define Anum_pg_index_indrelid 2
#define Anum_pg_index_indnatts 3
#define Anum_pg_index_indisunique 4
#define Anum_pg_index_indisprimary 5
#define Anum_pg_index_indisclustered 6
! #define Anum_pg_index_indkey 7
! #define Anum_pg_index_indclass 8
! #define Anum_pg_index_indexprs 9
! #define Anum_pg_index_indpred 10
#endif /* PG_INDEX_H */
--- 64,80 ----
* compiler constants for pg_index
* ----------------
*/
! #define Natts_pg_index 11
#define Anum_pg_index_indexrelid 1
#define Anum_pg_index_indrelid 2
#define Anum_pg_index_indnatts 3
#define Anum_pg_index_indisunique 4
#define Anum_pg_index_indisprimary 5
#define Anum_pg_index_indisclustered 6
! #define Anum_pg_index_indpctfree 7
! #define Anum_pg_index_indkey 8
! #define Anum_pg_index_indclass 9
! #define Anum_pg_index_indexprs 10
! #define Anum_pg_index_indpred 11
#endif /* PG_INDEX_H */
diff -cpr pgsql-orig/src/include/commands/defrem.h pgsql/src/include/commands/defrem.h
*** pgsql-orig/src/include/commands/defrem.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/commands/defrem.h 2006-02-06 12:48:14.000000000 +0900
*************** extern void DefineIndex(RangeVar *heapRe
*** 29,43 ****
bool unique,
bool primary,
bool isconstraint,
bool is_alter_table,
bool check_rights,
bool skip_build,
bool quiet);
extern void RemoveIndex(RangeVar *relation, DropBehavior behavior);
! extern void ReindexIndex(RangeVar *indexRelation);
! extern void ReindexTable(RangeVar *relation);
extern void ReindexDatabase(const char *databaseName,
! bool do_system, bool do_user);
extern char *makeObjectName(const char *name1, const char *name2,
const char *label);
extern char *ChooseRelationName(const char *name1, const char *name2,
--- 29,44 ----
bool unique,
bool primary,
bool isconstraint,
+ int pctfree,
bool is_alter_table,
bool check_rights,
bool skip_build,
bool quiet);
extern void RemoveIndex(RangeVar *relation, DropBehavior behavior);
! extern void ReindexIndex(RangeVar *indexRelation, int pctfree);
! extern void ReindexTable(RangeVar *relation, int pctfree);
extern void ReindexDatabase(const char *databaseName,
! int pctfree, bool do_system, bool do_user);
extern char *makeObjectName(const char *name1, const char *name2,
const char *label);
extern char *ChooseRelationName(const char *name1, const char *name2,
diff -cpr pgsql-orig/src/include/nodes/execnodes.h pgsql/src/include/nodes/execnodes.h
*** pgsql-orig/src/include/nodes/execnodes.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/nodes/execnodes.h 2006-02-06 12:48:14.000000000 +0900
***************
*** 40,45 ****
--- 40,46 ----
* Predicate partial-index predicate, or NIL if none
* PredicateState exec state for predicate, or NIL if none
* Unique is it a unique index?
+ * PctFree fill factor
* ----------------
*/
typedef struct IndexInfo
*************** typedef struct IndexInfo
*** 52,57 ****
--- 53,59 ----
List *ii_Predicate; /* list of Expr */
List *ii_PredicateState; /* list of ExprState */
bool ii_Unique;
+ int ii_PctFree;
} IndexInfo;
/* ----------------
diff -cpr pgsql-orig/src/include/nodes/parsenodes.h pgsql/src/include/nodes/parsenodes.h
*** pgsql-orig/src/include/nodes/parsenodes.h 2006-02-06 12:47:40.000000000 +0900
--- pgsql/src/include/nodes/parsenodes.h 2006-02-06 12:48:14.000000000 +0900
*************** typedef enum AlterTableType
*** 837,843 ****
AT_EnableTrigAll, /* ENABLE TRIGGER ALL */
AT_DisableTrigAll, /* DISABLE TRIGGER ALL */
AT_EnableTrigUser, /* ENABLE TRIGGER USER */
! AT_DisableTrigUser /* DISABLE TRIGGER USER */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
--- 837,844 ----
AT_EnableTrigAll, /* ENABLE TRIGGER ALL */
AT_DisableTrigAll, /* DISABLE TRIGGER ALL */
AT_EnableTrigUser, /* ENABLE TRIGGER USER */
! AT_DisableTrigUser, /* DISABLE TRIGGER USER */
! AT_SetPctfree, /* SET PCTFREE */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
*************** typedef struct Constraint
*** 1042,1047 ****
--- 1043,1050 ----
Node *raw_expr; /* expr, as untransformed parse tree */
char *cooked_expr; /* expr, as nodeToString representation */
List *keys; /* String nodes naming referenced column(s) */
+ int pctfree; /* index tablespace for PKEY/UNIQUE
+ * constraints; -1 for default */
char *indexspace; /* index tablespace for PKEY/UNIQUE
* constraints; NULL for default */
} Constraint;
*************** typedef struct IndexStmt
*** 1395,1400 ****
--- 1398,1404 ----
bool unique; /* is index unique? */
bool primary; /* is index on primary key? */
bool isconstraint; /* is it from a CONSTRAINT clause? */
+ int pctfree; /* fill factor */
} IndexStmt;
/* ----------------------
*************** typedef struct ReindexStmt
*** 1797,1802 ****
--- 1801,1807 ----
ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, OBJECT_DATABASE */
RangeVar *relation; /* Table or index to reindex */
const char *name; /* name of database to reindex */
+ int pctfree; /* index fill factor */
bool do_system; /* include system tables in database case */
bool do_user; /* include user tables in database case */
} ReindexStmt;
Re: TODO-Item: B-tree fillfactor control
On Mon, 2006-02-06 at 13:27 +0900, ITAGAKI Takahiro wrote:
This is a draft patch for index fillfactor control discussed in
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00013.phpI added the following features:
- Add support for btree, hash and gist.
- Syntax extension using PCTFREE.
- Save settings to catalog. Next REINDEX will use the last value.I'd like to ask index developers to review the patch, especially
the method to control fill factor for hash and gist.
I'll write documentations if there is no problem in the features.
Comments are welcome.
Looks pretty complete to me. A useful patch for large databases.
Do you have any performance numbers for the extreme settings? It may be
worth having different max limits for each of the index types, since
they differ so widely in algorithms. Do we have any tests to show
whether 3*setting is the right value for b-tree node pages? It sounds
about right but I have no evidence either way.
I'm surprised that you do not use the parameter to control the RIGHTMOST
index block split factor for B-trees, which remains at a constant 67%.
The PCTFREE only seems to apply at CREATE INDEX time.
"The steady-state load factor for btrees is usually estimated at 70%."
but we recognise that estimate as being from the 1980s and not
necessarily reflecting all application types for which we now use
databases.
Can we use the PCTFREE setting to control the RIGHTMOST behaviour? If I
manually control the PCTFREE I want it to work like that all of the
time, not just some of the time.
[i.e. with this patch if I fill an index with 1000 blocks of data using
PCTFREE 0 the index will use 1000 blocks. If I COPY another 1000 blocks
of data the index would then be 1500 blocks larger, 2500 total. The
current cvstip acts thus: if I fill an index with 1000 blocks of data
the index will use 1111 blocks. If I COPY another 1000 blocks of data
the index would then be 1500 blocks larger, 2611 total. I'd like to be
able to have the index use only 2000 blocks when PCTFREE=0 - if I ask
for fully packed I want fully packed, please]
If we support PCTFREE for compatibility reasons should we not also
support the alternative FILLFACTOR syntax also? I see no reason to
favour Oracle/DB2 compatability at the expense of SQLServer
compatibility.
Best Regards, Simon Riggs
Re: TODO-Item: B-tree fillfactor control
Simon Riggs <simon@2ndquadrant.com> writes:
If we support PCTFREE for compatibility reasons should we not also
support the alternative FILLFACTOR syntax also? I see no reason to
favour Oracle/DB2 compatability at the expense of SQLServer
compatibility.
One nonstandard syntax is more than enough.
regards, tom lane
Re: TODO-Item: B-tree fillfactor control
This is a revised patch for index fillfactor control:
- Split MAX_PCTFREE into three for each index method.
- B-tree indexes use their own settings when rightmost page is split.
- Fix a bug that GUC is modified when index building is canceled.
- Add some documentations.
Simon Riggs <simon@2ndquadrant.com> wrote:
Do you have any performance numbers for the extreme settings? It may be
worth having different max limits for each of the index types, since
they differ so widely in algorithms.
Different max limits are done.
I worry about whether index works properly on high PCTFREE settings. I found
hash has its own sanity checking, but I don't know other indexes have.
I'm surprised that you do not use the parameter to control the RIGHTMOST
index block split factor for B-trees, which remains at a constant 67%.
The PCTFREE only seems to apply at CREATE INDEX time.
Thanks for pointing out. I did not inadvertently use fillfactor on
the rightmost page. With the revised patch, PCTFREE will be considered
in such cases.
# CREATE TABLE test (i int);
# INSERT INTO test SELECT generate_series(1, 100000);
# CREATE INDEX btree ON test USING btree (i) PCTFREE 0;
# SELECT relpages from pg_class where relname ='btree';
relpages | 249
# INSERT INTO test SELECT generate_series(100001, 200000);
# SELECT relpages from pg_class where relname ='btree';
relpages | 497 <-- +99.6%
But default settings will change. Is this ok?
| | patched |
| now | free=10 | free=0 |
-----------------+-----+---------+--------+-
leaf (REINDEX) | 10 | 10 | 0 |
leaf (RIGHTMOST) | 30 | 10 | 0 | = leaf
node (REINDEX) | 30 | 30 | 0 | = 3*leaf
If we support PCTFREE for compatibility reasons should we not also
support the alternative FILLFACTOR syntax also? I see no reason to
favour Oracle/DB2 compatability at the expense of SQLServer
compatibility.
There are few synonyms in PostgreSQL, so I think it is better for us to
adopt only either one. I like FILLFACTOR personally, but compatibility
with Oracle is more important to users around me.
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories
Attachments:
index_free_percent-0210.patchapplication/octet-stream; name=index_free_percent-0210.patchDownload
diff -cpr pgsql-orig/doc/src/sgml/config.sgml pgsql/doc/src/sgml/config.sgml
*** pgsql-orig/doc/src/sgml/config.sgml 2006-02-10 18:49:12.000000000 +0900
--- pgsql/doc/src/sgml/config.sgml 2006-02-10 19:07:27.000000000 +0900
*************** SELECT * FROM parent WHERE key = 2400;
*** 3188,3193 ****
--- 3188,3229 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-btree-free-percent" xreflabel="btree_free_percent">
+ <term><varname>btree_free_percent</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>btree_free_percent</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ This variable specifies the default fill factors of btree indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-hash-free-percent" xreflabel="hash_free_percent">
+ <term><varname>hash_free_percent</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>hash_free_percent</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ This variable specifies the default fill factors of hash indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-gist-free-percent" xreflabel="gist_free_percent">
+ <term><varname>gist_free_percent</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>gist_free_percent</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ This variable specifies the default fill factors of gist indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
<term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
<indexterm>
diff -cpr pgsql-orig/doc/src/sgml/ref/alter_index.sgml pgsql/doc/src/sgml/ref/alter_index.sgml
*** pgsql-orig/doc/src/sgml/ref/alter_index.sgml 2006-02-10 18:49:12.000000000 +0900
--- pgsql/doc/src/sgml/ref/alter_index.sgml 2006-02-10 18:56:43.000000000 +0900
*************** PostgreSQL documentation
*** 22,27 ****
--- 22,28 ----
<synopsis>
ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
+ ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET PCTFREE <replaceable class="PARAMETER">percent</replaceable>
</synopsis>
</refsynopsisdiv>
*************** ALTER INDEX <replaceable class="PARAMETE
*** 56,61 ****
--- 57,74 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>SET PCTFREE</literal></term>
+ <listitem>
+ <para>
+ This form changes the index's fill factor to the specified percentage.
+ Actual index data will not be changed. Do REINDEX to reorganize.
+ See also
+ <xref linkend="SQL-REINDEX" endterm="sql-reindex-title">.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
diff -cpr pgsql-orig/doc/src/sgml/ref/create_table.sgml pgsql/doc/src/sgml/ref/create_table.sgml
*** pgsql-orig/doc/src/sgml/ref/create_table.sgml 2006-02-10 18:49:12.000000000 +0900
--- pgsql/doc/src/sgml/ref/create_table.sgml 2006-02-10 19:08:26.000000000 +0900
*************** where <replaceable class="PARAMETER">col
*** 36,43 ****
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL |
NULL |
! UNIQUE [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK (<replaceable class="PARAMETER">expression</replaceable>) |
REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
--- 36,43 ----
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL |
NULL |
! UNIQUE [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK (<replaceable class="PARAMETER">expression</replaceable>) |
REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
*************** where <replaceable class="PARAMETER">col
*** 46,53 ****
and <replaceable class="PARAMETER">table_constraint</replaceable> is:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
! { UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
--- 46,53 ----
and <replaceable class="PARAMETER">table_constraint</replaceable> is:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
! { UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
! PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ] |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
*************** and <replaceable class="PARAMETER">table
*** 616,621 ****
--- 616,635 ----
</varlistentry>
<varlistentry>
+ <term><literal>PCTFREE <replaceable class="PARAMETER">percent</replaceable></literal></term>
+ <listitem>
+ <para>
+ This clause allows selection of the fill factor on the index
+ associated with a <literal>UNIQUE</literal> or <literal>PRIMARY
+ KEY</literal> constraint will be created.
+ If not specified,
+ <xref linkend="guc-btree-free-percent">, <xref linkend="guc-hash-free-percent">
+ or <xref linkend="guc-gist-free-percent"> is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
<listitem>
<para>
diff -cpr pgsql-orig/doc/src/sgml/ref/reindex.sgml pgsql/doc/src/sgml/ref/reindex.sgml
*** pgsql-orig/doc/src/sgml/ref/reindex.sgml 2006-02-10 18:49:12.000000000 +0900
--- pgsql/doc/src/sgml/ref/reindex.sgml 2006-02-10 18:56:43.000000000 +0900
*************** PostgreSQL documentation
*** 20,26 ****
<refsynopsisdiv>
<synopsis>
! REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
</synopsis>
</refsynopsisdiv>
--- 20,26 ----
<refsynopsisdiv>
<synopsis>
! REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [PCTFREE <replaceable class="PARAMETER">percent</replaceable>] [ FORCE ]
</synopsis>
</refsynopsisdiv>
*************** REINDEX { INDEX | TABLE | DATABASE | SYS
*** 117,122 ****
--- 117,131 ----
</varlistentry>
<varlistentry>
+ <term><literal>PCTFREE <replaceable class="PARAMETER">percent</replaceable></literal></term>
+ <listitem>
+ <para>
+ This form changes the index's fill factor to the specified percentage.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>FORCE</literal></term>
<listitem>
<para>
diff -cpr pgsql-orig/src/backend/access/gist/gist.c pgsql/src/backend/access/gist/gist.c
*** pgsql-orig/src/backend/access/gist/gist.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/gist/gist.c 2006-02-10 18:56:43.000000000 +0900
***************
*** 25,30 ****
--- 25,32 ----
const XLogRecPtr XLogRecPtrForTemp = {1, 1};
+ int gist_free_percent = 10;
+
/* Working state for gistbuild and its callback */
typedef struct
{
*************** static void gistbuildCallback(Relation i
*** 44,49 ****
--- 46,52 ----
void *state);
static void gistdoinsert(Relation r,
IndexTuple itup,
+ Size freespace,
GISTSTATE *GISTstate);
static void gistfindleaf(GISTInsertState *state,
GISTSTATE *giststate);
*************** gistbuild(PG_FUNCTION_ARGS)
*** 91,96 ****
--- 94,109 ----
GISTBuildState buildstate;
Buffer buffer;
+ /* sanity checks */
+ if (indexInfo->ii_PctFree < -1 ||
+ indexInfo->ii_PctFree > GIST_MAX_PCTFREE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PCTFREE %d must be between 0 and %d",
+ indexInfo->ii_PctFree, GIST_MAX_PCTFREE)));
+
+ elog(DEBUG1, "gistbuild (PCTFREE=%d)", INDEX_GET_PCTFREE(index, gist_free_percent));
+
/*
* We expect to be called exactly once for any index relation. If that's
* not the case, big trouble's what we have.
*************** gistbuildCallback(Relation index,
*** 204,210 ****
* you're inserting single tups, but not when you're initializing the
* whole index at once.
*/
! gistdoinsert(index, itup, &buildstate->giststate);
buildstate->indtuples += 1;
MemoryContextSwitchTo(oldCtx);
--- 217,225 ----
* you're inserting single tups, but not when you're initializing the
* whole index at once.
*/
! gistdoinsert(index, itup,
! BLCKSZ * INDEX_GET_PCTFREE(index, gist_free_percent) / 100,
! &buildstate->giststate);
buildstate->indtuples += 1;
MemoryContextSwitchTo(oldCtx);
*************** gistinsert(PG_FUNCTION_ARGS)
*** 261,267 ****
itup = index_form_tuple(giststate.tupdesc, values, isnull);
itup->t_tid = *ht_ctid;
! gistdoinsert(r, itup, &giststate);
/* cleanup */
freeGISTstate(&giststate);
--- 276,282 ----
itup = index_form_tuple(giststate.tupdesc, values, isnull);
itup->t_tid = *ht_ctid;
! gistdoinsert(r, itup, 0, &giststate);
/* cleanup */
freeGISTstate(&giststate);
*************** gistinsert(PG_FUNCTION_ARGS)
*** 278,284 ****
* so it does not bother releasing palloc'd allocations.
*/
static void
! gistdoinsert(Relation r, IndexTuple itup, GISTSTATE *giststate)
{
GISTInsertState state;
--- 293,299 ----
* so it does not bother releasing palloc'd allocations.
*/
static void
! gistdoinsert(Relation r, IndexTuple itup, Size freespace, GISTSTATE *giststate)
{
GISTInsertState state;
*************** gistdoinsert(Relation r, IndexTuple itup
*** 288,293 ****
--- 303,309 ----
state.itup[0] = (IndexTuple) palloc(IndexTupleSize(itup));
memcpy(state.itup[0], itup, IndexTupleSize(itup));
state.ituplen = 1;
+ state.freespace = freespace;
state.r = r;
state.key = itup->t_tid;
state.needInsertComplete = true;
*************** gistplacetopage(GISTInsertState *state,
*** 316,322 ****
PageIndexTupleDelete(state->stack->page, state->stack->childoffnum);
! if (gistnospace(state->stack->page, state->itup, state->ituplen))
{
/* no space for insertion */
IndexTuple *itvec,
--- 332,342 ----
PageIndexTupleDelete(state->stack->page, state->stack->childoffnum);
! /*
! * XXX: If fill factors for leaf and node should be different, use below or so.
! * freespace = (is_leaf ? state->leaf_freespace : state->node_freespace)
! */
! if (gistnospace(state->stack->page, state->itup, state->ituplen, state->freespace))
{
/* no space for insertion */
IndexTuple *itvec,
*************** gistSplit(Relation r,
*** 1035,1041 ****
for (i = 1; i < entryvec->n; i++)
cleaneditup[i - 1] = itup[realoffset[i] - 1];
! if (gistnospace(left, cleaneditup, lencleaneditup))
{
/* no space on left to put all good tuples, so picksplit */
gistUserPicksplit(r, entryvec, &v, cleaneditup, lencleaneditup, giststate);
--- 1055,1061 ----
for (i = 1; i < entryvec->n; i++)
cleaneditup[i - 1] = itup[realoffset[i] - 1];
! if (gistnospace(left, cleaneditup, lencleaneditup, 0))
{
/* no space on left to put all good tuples, so picksplit */
gistUserPicksplit(r, entryvec, &v, cleaneditup, lencleaneditup, giststate);
*************** gistSplit(Relation r,
*** 1108,1114 ****
}
/* write on disk (may need another split) */
! if (gistnospace(right, rvectup, v.spl_nright))
{
nlen = v.spl_nright;
newtup = gistSplit(r, rightbuf, rvectup, &nlen, dist, giststate);
--- 1128,1134 ----
}
/* write on disk (may need another split) */
! if (gistnospace(right, rvectup, v.spl_nright, 0))
{
nlen = v.spl_nright;
newtup = gistSplit(r, rightbuf, rvectup, &nlen, dist, giststate);
*************** gistSplit(Relation r,
*** 1140,1146 ****
ItemPointerSetBlockNumber(&(newtup[0]->t_tid), rbknum);
}
! if (gistnospace(left, lvectup, v.spl_nleft))
{
int llen = v.spl_nleft;
IndexTuple *lntup;
--- 1160,1166 ----
ItemPointerSetBlockNumber(&(newtup[0]->t_tid), rbknum);
}
! if (gistnospace(left, lvectup, v.spl_nleft, 0))
{
int llen = v.spl_nleft;
IndexTuple *lntup;
diff -cpr pgsql-orig/src/backend/access/gist/gistutil.c pgsql/src/backend/access/gist/gistutil.c
*** pgsql-orig/src/backend/access/gist/gistutil.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/gist/gistutil.c 2006-02-10 18:56:43.000000000 +0900
*************** gistfillbuffer(Relation r, Page page, In
*** 81,90 ****
* Check space for itup vector on page
*/
bool
! gistnospace(Page page, IndexTuple *itvec, int len)
{
! unsigned int size = 0;
! int i;
for (i = 0; i < len; i++)
size += IndexTupleSize(itvec[i]) + sizeof(ItemIdData);
--- 81,90 ----
* Check space for itup vector on page
*/
bool
! gistnospace(Page page, IndexTuple *itvec, int len, Size freespace)
{
! Size size = freespace;
! int i;
for (i = 0; i < len; i++)
size += IndexTupleSize(itvec[i]) + sizeof(ItemIdData);
diff -cpr pgsql-orig/src/backend/access/gist/gistvacuum.c pgsql/src/backend/access/gist/gistvacuum.c
*** pgsql-orig/src/backend/access/gist/gistvacuum.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/gist/gistvacuum.c 2006-02-10 18:56:43.000000000 +0900
*************** gistVacuumUpdate(GistVacuum *gv, BlockNu
*** 149,155 ****
if (curlenaddon)
{
/* insert updated tuples */
! if (gistnospace(page, addon, curlenaddon))
{
/* there is no space on page to insert tuples */
IndexTuple *vec;
--- 149,155 ----
if (curlenaddon)
{
/* insert updated tuples */
! if (gistnospace(page, addon, curlenaddon, 0 /* XXX: Should pctfree be used here? */))
{
/* there is no space on page to insert tuples */
IndexTuple *vec;
diff -cpr pgsql-orig/src/backend/access/gist/gistxlog.c pgsql/src/backend/access/gist/gistxlog.c
*** pgsql-orig/src/backend/access/gist/gistxlog.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/gist/gistxlog.c 2006-02-10 18:56:43.000000000 +0900
*************** gistContinueInsert(gistIncompleteInsert
*** 690,696 ****
}
}
! if (gistnospace(pages[numbuffer - 1], itup, lenitup))
{
/* no space left on page, so we should split */
buffers[numbuffer] = XLogReadBuffer(true, index, P_NEW);
--- 690,696 ----
}
}
! if (gistnospace(pages[numbuffer - 1], itup, lenitup, 0))
{
/* no space left on page, so we should split */
buffers[numbuffer] = XLogReadBuffer(true, index, P_NEW);
diff -cpr pgsql-orig/src/backend/access/hash/hash.c pgsql/src/backend/access/hash/hash.c
*** pgsql-orig/src/backend/access/hash/hash.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/hash/hash.c 2006-02-10 18:56:43.000000000 +0900
*************** hashbuild(PG_FUNCTION_ARGS)
*** 54,59 ****
--- 54,69 ----
double reltuples;
HashBuildState buildstate;
+ /* sanity checks */
+ if (indexInfo->ii_PctFree < -1 ||
+ indexInfo->ii_PctFree > HASH_MAX_PCTFREE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PCTFREE %d must be between 0 and %d",
+ indexInfo->ii_PctFree, HASH_MAX_PCTFREE)));
+
+ elog(DEBUG1, "hashbuild (PCTFREE=%d)", INDEX_GET_PCTFREE(index, hash_free_percent));
+
/*
* We expect to be called exactly once for any index relation. If that's
* not the case, big trouble's what we have.
diff -cpr pgsql-orig/src/backend/access/hash/hashpage.c pgsql/src/backend/access/hash/hashpage.c
*** pgsql-orig/src/backend/access/hash/hashpage.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/hash/hashpage.c 2006-02-10 18:56:43.000000000 +0900
***************
*** 30,35 ****
--- 30,36 ----
#include "access/genam.h"
#include "access/hash.h"
+ #include "catalog/index.h"
#include "miscadmin.h"
#include "storage/lmgr.h"
#include "utils/lsyscache.h"
*************** static void _hash_splitbucket(Relation r
*** 42,47 ****
--- 43,49 ----
uint32 maxbucket,
uint32 highmask, uint32 lowmask);
+ int hash_free_percent = 25;
/*
* We use high-concurrency locking on hash indexes (see README for an overview
*************** _hash_metapinit(Relation rel)
*** 251,257 ****
RelationGetDescr(rel)->attrs[0]->atttypmod);
item_width = MAXALIGN(sizeof(IndexTupleData)) + MAXALIGN(data_width) +
sizeof(ItemIdData); /* include the line pointer */
! ffactor = (BLCKSZ * 3 / 4) / item_width;
/* keep to a sane range */
if (ffactor < 10)
ffactor = 10;
--- 253,260 ----
RelationGetDescr(rel)->attrs[0]->atttypmod);
item_width = MAXALIGN(sizeof(IndexTupleData)) + MAXALIGN(data_width) +
sizeof(ItemIdData); /* include the line pointer */
! ffactor = BLCKSZ * (100 - INDEX_GET_PCTFREE(rel, hash_free_percent)) /
! 100 / item_width;
/* keep to a sane range */
if (ffactor < 10)
ffactor = 10;
diff -cpr pgsql-orig/src/backend/access/nbtree/nbtinsert.c pgsql/src/backend/access/nbtree/nbtinsert.c
*** pgsql-orig/src/backend/access/nbtree/nbtinsert.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/nbtree/nbtinsert.c 2006-02-10 18:56:43.000000000 +0900
***************
*** 17,22 ****
--- 17,23 ----
#include "access/heapam.h"
#include "access/nbtree.h"
+ #include "catalog/index.h"
#include "miscadmin.h"
*************** typedef struct
*** 24,29 ****
--- 25,31 ----
{
/* context data for _bt_checksplitloc */
Size newitemsz; /* size of new item to be inserted */
+ int pctfree; /* used when insert at right most */
bool is_leaf; /* T if splitting a leaf page */
bool is_rightmost; /* T if splitting a rightmost page */
*************** _bt_split(Relation rel, Buffer buf, Offs
*** 928,941 ****
* it needs to go into!)
*
* If the page is the rightmost page on its level, we instead try to arrange
! * for twice as much free space on the right as on the left. In this way,
* when we are inserting successively increasing keys (consider sequences,
! * timestamps, etc) we will end up with a tree whose pages are about 67% full,
* instead of the 50% full result that we'd get without this special case.
! * (We could bias it even further to make the initially-loaded tree more full.
! * But since the steady-state load for a btree is about 70%, we'd likely just
! * be making more page-splitting work for ourselves later on, when we start
! * seeing updates to existing tuples.)
*
* We are passed the intended insert position of the new tuple, expressed as
* the offsetnumber of the tuple it must go in front of. (This could be
--- 930,940 ----
* it needs to go into!)
*
* If the page is the rightmost page on its level, we instead try to arrange
! * for reserving PCTFREE% of free space on left page. In this way,
* when we are inserting successively increasing keys (consider sequences,
! * timestamps, etc) we will end up with a tree whose pages are about PCTFREE% full,
* instead of the 50% full result that we'd get without this special case.
! * This is the same as initially-loaded tree.
*
* We are passed the intended insert position of the new tuple, expressed as
* the offsetnumber of the tuple it must go in front of. (This could be
*************** _bt_findsplitloc(Relation rel,
*** 969,974 ****
--- 968,974 ----
/* Passed-in newitemsz is MAXALIGNED but does not include line pointer */
newitemsz += sizeof(ItemIdData);
state.newitemsz = newitemsz;
+ state.pctfree = INDEX_GET_PCTFREE(rel, btree_free_percent);
state.is_leaf = P_ISLEAF(opaque);
state.is_rightmost = P_RIGHTMOST(opaque);
state.have_split = false;
*************** _bt_checksplitloc(FindSplitData *state,
*** 1099,1108 ****
if (state->is_rightmost)
{
/*
! * On a rightmost page, try to equalize right free space with
! * twice the left free space. See comments for _bt_findsplitloc.
*/
! delta = (2 * leftfree) - rightfree;
}
else
{
--- 1099,1109 ----
if (state->is_rightmost)
{
/*
! * On a rightmost page, try to reserve PCTFREE% of free space
! * on left page. See comments for _bt_findsplitloc.
*/
! delta = ((100 - state->pctfree) * leftfree)
! - (state->pctfree * rightfree);
}
else
{
diff -cpr pgsql-orig/src/backend/access/nbtree/nbtree.c pgsql/src/backend/access/nbtree/nbtree.c
*** pgsql-orig/src/backend/access/nbtree/nbtree.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/nbtree/nbtree.c 2006-02-10 18:56:43.000000000 +0900
*************** btbuild(PG_FUNCTION_ARGS)
*** 71,76 ****
--- 71,86 ----
double reltuples;
BTBuildState buildstate;
+ /* sanity checks */
+ if (indexInfo->ii_PctFree < -1 ||
+ indexInfo->ii_PctFree > BTREE_MAX_PCTFREE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PCTFREE %d must be between 0 and %d",
+ indexInfo->ii_PctFree, BTREE_MAX_PCTFREE)));
+
+ elog(DEBUG1, "btbuild (PCTFREE=%d)", INDEX_GET_PCTFREE(index, btree_free_percent));
+
/*
* bootstrap processing does something strange, so don't use sort/build
* for initial catalog indices. at some point i need to look harder at
diff -cpr pgsql-orig/src/backend/access/nbtree/nbtsort.c pgsql/src/backend/access/nbtree/nbtsort.c
*** pgsql-orig/src/backend/access/nbtree/nbtsort.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/access/nbtree/nbtsort.c 2006-02-10 18:56:43.000000000 +0900
***************
*** 65,70 ****
--- 65,71 ----
#include "access/nbtree.h"
#include "access/xlog.h"
+ #include "catalog/index.h"
#include "miscadmin.h"
#include "storage/smgr.h"
#include "utils/tuplesort.h"
*************** typedef struct BTWriteState
*** 120,125 ****
--- 121,127 ----
static Page _bt_blnewpage(uint32 level);
+ static Size _bt_full_threshold(Relation index, Size pagesize, bool leaf);
static BTPageState *_bt_pagestate(BTWriteState *wstate, uint32 level);
static void _bt_slideleft(Page page);
static void _bt_sortaddtup(Page page, Size itemsize,
*************** static void _bt_uppershutdown(BTWriteSta
*** 130,135 ****
--- 132,138 ----
static void _bt_load(BTWriteState *wstate,
BTSpool *btspool, BTSpool *btspool2);
+ int btree_free_percent = 10;
/*
* Interface routines
*************** _bt_blwritepage(BTWriteState *wstate, Pa
*** 327,332 ****
--- 330,351 ----
}
/*
+ * The steady-state load factor for btrees is usually estimated at 70%.
+ * We choose to pack leaf pages to 90% and upper pages to 70% as defaults.
+ */
+ static Size
+ _bt_full_threshold(Relation index, Size pagesize, bool leaf)
+ {
+ int pctfree = INDEX_GET_PCTFREE(index, btree_free_percent);
+ if (!leaf)
+ {
+ /* XXX: Is this reasonable? */
+ pctfree = Min(30, 3 * pctfree);
+ }
+ return pagesize * pctfree / 100;
+ }
+
+ /*
* allocate and initialize a new BTPageState. the returned structure
* is suitable for immediate use by _bt_buildadd.
*/
*************** _bt_pagestate(BTWriteState *wstate, uint
*** 346,355 ****
state->btps_lastoff = P_HIKEY;
state->btps_level = level;
/* set "full" threshold based on level. See notes at head of file. */
! if (level > 0)
! state->btps_full = (PageGetPageSize(state->btps_page) * 3) / 10;
! else
! state->btps_full = PageGetPageSize(state->btps_page) / 10;
/* no parent level, yet */
state->btps_next = NULL;
--- 365,372 ----
state->btps_lastoff = P_HIKEY;
state->btps_level = level;
/* set "full" threshold based on level. See notes at head of file. */
! state->btps_full = _bt_full_threshold(wstate->index,
! PageGetPageSize(state->btps_page), level == 0);
/* no parent level, yet */
state->btps_next = NULL;
diff -cpr pgsql-orig/src/backend/bootstrap/bootparse.y pgsql/src/backend/bootstrap/bootparse.y
*** pgsql-orig/src/backend/bootstrap/bootparse.y 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/bootstrap/bootparse.y 2006-02-10 18:56:43.000000000 +0900
*************** Boot_DeclareIndexStmt:
*** 251,257 ****
NULL,
$10,
NULL, NIL,
! false, false, false,
false, false, true, false);
do_end();
}
--- 251,257 ----
NULL,
$10,
NULL, NIL,
! false, false, false, -1,
false, false, true, false);
do_end();
}
*************** Boot_DeclareUniqueIndexStmt:
*** 269,275 ****
NULL,
$11,
NULL, NIL,
! true, false, false,
false, false, true, false);
do_end();
}
--- 269,275 ----
NULL,
$11,
NULL, NIL,
! true, false, false, -1,
false, false, true, false);
do_end();
}
diff -cpr pgsql-orig/src/backend/catalog/index.c pgsql/src/backend/catalog/index.c
*** pgsql-orig/src/backend/catalog/index.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/catalog/index.c 2006-02-10 18:56:43.000000000 +0900
*************** UpdateIndexRelation(Oid indexoid,
*** 410,415 ****
--- 410,416 ----
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
values[Anum_pg_index_indisclustered - 1] = BoolGetDatum(false);
+ values[Anum_pg_index_indpctfree - 1] = Int16GetDatum(indexInfo->ii_PctFree);
values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey);
values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass);
values[Anum_pg_index_indexprs - 1] = exprsDatum;
*************** BuildIndexInfo(Relation index)
*** 902,907 ****
--- 903,909 ----
/* other info */
ii->ii_Unique = indexStruct->indisunique;
+ ii->ii_PctFree = indexStruct->indpctfree;
return ii;
}
*************** IndexGetRelation(Oid indexId)
*** 1616,1622 ****
* reindex_index - This routine is used to recreate a single index
*/
void
! reindex_index(Oid indexId)
{
Relation iRel,
heapRelation;
--- 1618,1624 ----
* reindex_index - This routine is used to recreate a single index
*/
void
! reindex_index(Oid indexId, int pctfree)
{
Relation iRel,
heapRelation;
*************** reindex_index(Oid indexId)
*** 1663,1668 ****
--- 1665,1676 ----
/* Fetch info needed for index_build */
indexInfo = BuildIndexInfo(iRel);
+ if (pctfree != -1)
+ {
+ /* PCTFREE is specified, so update catalog. */
+ CatalogSetIndexPctfree(iRel, pctfree);
+ indexInfo->ii_PctFree = pctfree;
+ }
if (inplace)
{
*************** reindex_index(Oid indexId)
*** 1703,1709 ****
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, bool toast_too)
{
Relation rel;
Oid toast_relid;
--- 1711,1717 ----
* CommandCounterIncrement will occur after each index rebuild.
*/
bool
! reindex_relation(Oid relid, bool toast_too, int pctfree)
{
Relation rel;
Oid toast_relid;
*************** reindex_relation(Oid relid, bool toast_t
*** 1761,1767 ****
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
! reindex_index(indexOid);
CommandCounterIncrement();
--- 1769,1775 ----
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
! reindex_index(indexOid, pctfree);
CommandCounterIncrement();
*************** reindex_relation(Oid relid, bool toast_t
*** 1784,1790 ****
* still hold the lock on the master table.
*/
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid, false);
return result;
}
--- 1792,1798 ----
* still hold the lock on the master table.
*/
if (toast_too && OidIsValid(toast_relid))
! result |= reindex_relation(toast_relid, false, pctfree);
return result;
}
diff -cpr pgsql-orig/src/backend/catalog/indexing.c pgsql/src/backend/catalog/indexing.c
*** pgsql-orig/src/backend/catalog/indexing.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/catalog/indexing.c 2006-02-10 18:56:43.000000000 +0900
***************
*** 16,24 ****
--- 16,26 ----
#include "postgres.h"
#include "access/genam.h"
+ #include "access/heapam.h"
#include "catalog/index.h"
#include "catalog/indexing.h"
#include "executor/executor.h"
+ #include "utils/syscache.h"
/*
*************** CatalogUpdateIndexes(Relation heapRel, H
*** 149,151 ****
--- 151,214 ----
CatalogIndexInsert(indstate, heapTuple);
CatalogCloseIndexes(indstate);
}
+
+ void
+ CatalogSetIndexPctfree(Relation index, int pctfree)
+ {
+ Relation indexes;
+ HeapTuple tuple;
+ Form_pg_index indexDesc;
+ int max_pctfree;
+ Oid indexOid = RelationGetRelid(index);
+
+ /*
+ * Limit target to a sane range
+ */
+ Assert(index->rd_rel);
+ switch (index->rd_rel->relam)
+ {
+ case BTREE_AM_OID:
+ max_pctfree = BTREE_MAX_PCTFREE;
+ break;
+ case HASH_AM_OID:
+ max_pctfree = HASH_MAX_PCTFREE;
+ break;
+ case GIST_AM_OID:
+ max_pctfree = GIST_MAX_PCTFREE;
+ break;
+ default:
+ elog(WARNING, "unknown index type: %d", index->rd_rel->relam);
+ max_pctfree = 100;
+ break;
+ }
+ if (pctfree < -1 || pctfree > max_pctfree)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PCTFREE %d must be between 0 and %d",
+ pctfree, max_pctfree)));
+
+ indexes = heap_open(IndexRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy(INDEXRELID,
+ ObjectIdGetDatum(indexOid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for index %u", indexOid);
+
+ indexDesc = (Form_pg_index) GETSTRUCT(tuple);
+
+ /*
+ * XXX: Is it legal to overwrite index->rd_index->indpctfree?
+ * There was a bug that REINDEX just after ALTER INDEX SET PCTFREE
+ * didn't use the specified value. This is intended to fix it, but
+ * invalidating cache of index->rd_index might be better.
+ */
+ index->rd_index->indpctfree = pctfree;
+
+ indexDesc->indpctfree = pctfree;
+ simple_heap_update(indexes, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(indexes, tuple);
+
+ heap_freetuple(tuple);
+ heap_close(indexes, RowExclusiveLock);
+ }
diff -cpr pgsql-orig/src/backend/commands/cluster.c pgsql/src/backend/commands/cluster.c
*** pgsql-orig/src/backend/commands/cluster.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/commands/cluster.c 2006-02-10 18:56:43.000000000 +0900
*************** rebuild_relation(Relation OldHeap, Oid i
*** 547,553 ****
* all-new at this point). We do not need CommandCounterIncrement()
* because reindex_relation does it.
*/
! reindex_relation(tableOid, false);
}
/*
--- 547,553 ----
* all-new at this point). We do not need CommandCounterIncrement()
* because reindex_relation does it.
*/
! reindex_relation(tableOid, false, /* use last pctfree */ -1);
}
/*
diff -cpr pgsql-orig/src/backend/commands/indexcmds.c pgsql/src/backend/commands/indexcmds.c
*** pgsql-orig/src/backend/commands/indexcmds.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/commands/indexcmds.c 2006-02-10 18:56:43.000000000 +0900
*************** static bool relationHasPrimaryKey(Relati
*** 79,84 ****
--- 79,85 ----
* 'isconstraint': index is for a PRIMARY KEY or UNIQUE constraint,
* so build a pg_constraint entry for it.
* 'is_alter_table': this is due to an ALTER rather than a CREATE operation.
+ * 'pctfree': fill factor.
* 'check_rights': check for CREATE rights in the namespace. (This should
* be true except when ALTER is deleting/recreating an index.)
* 'skip_build': make the catalog entries but leave the index file empty;
*************** DefineIndex(RangeVar *heapRelation,
*** 97,102 ****
--- 98,104 ----
bool unique,
bool primary,
bool isconstraint,
+ int pctfree,
bool is_alter_table,
bool check_rights,
bool skip_build,
*************** DefineIndex(RangeVar *heapRelation,
*** 378,383 ****
--- 380,386 ----
indexInfo->ii_Predicate = make_ands_implicit(predicate);
indexInfo->ii_PredicateState = NIL;
indexInfo->ii_Unique = unique;
+ indexInfo->ii_PctFree = pctfree;
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
ComputeIndexAttrs(indexInfo, classObjectId, attributeList,
*************** RemoveIndex(RangeVar *relation, DropBeha
*** 914,920 ****
* Recreate a specific index.
*/
void
! ReindexIndex(RangeVar *indexRelation)
{
Oid indOid;
HeapTuple tuple;
--- 917,923 ----
* Recreate a specific index.
*/
void
! ReindexIndex(RangeVar *indexRelation, int pctfree)
{
Oid indOid;
HeapTuple tuple;
*************** ReindexIndex(RangeVar *indexRelation)
*** 939,945 ****
ReleaseSysCache(tuple);
! reindex_index(indOid);
}
/*
--- 942,948 ----
ReleaseSysCache(tuple);
! reindex_index(indOid, pctfree);
}
/*
*************** ReindexIndex(RangeVar *indexRelation)
*** 947,953 ****
* Recreate all indexes of a table (and of its toast table, if any)
*/
void
! ReindexTable(RangeVar *relation)
{
Oid heapOid;
HeapTuple tuple;
--- 950,956 ----
* Recreate all indexes of a table (and of its toast table, if any)
*/
void
! ReindexTable(RangeVar *relation, int pctfree)
{
Oid heapOid;
HeapTuple tuple;
*************** ReindexTable(RangeVar *relation)
*** 980,986 ****
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, true))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
--- 983,989 ----
ReleaseSysCache(tuple);
! if (!reindex_relation(heapOid, true, pctfree))
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes",
relation->relname)));
*************** ReindexTable(RangeVar *relation)
*** 994,1000 ****
* separate transaction, so we can release the lock on it right away.
*/
void
! ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
{
Relation relationRelation;
HeapScanDesc scan;
--- 997,1003 ----
* separate transaction, so we can release the lock on it right away.
*/
void
! ReindexDatabase(const char *databaseName, int pctfree, bool do_system, bool do_user)
{
Relation relationRelation;
HeapScanDesc scan;
*************** ReindexDatabase(const char *databaseName
*** 1099,1105 ****
StartTransactionCommand();
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
! if (reindex_relation(relid, true))
ereport(NOTICE,
(errmsg("table \"%s\" was reindexed",
get_rel_name(relid))));
--- 1102,1108 ----
StartTransactionCommand();
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
! if (reindex_relation(relid, true, pctfree))
ereport(NOTICE,
(errmsg("table \"%s\" was reindexed",
get_rel_name(relid))));
diff -cpr pgsql-orig/src/backend/commands/tablecmds.c pgsql/src/backend/commands/tablecmds.c
*** pgsql-orig/src/backend/commands/tablecmds.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/commands/tablecmds.c 2006-02-10 18:56:43.000000000 +0900
*************** static void ATPrepSetTableSpace(AlteredT
*** 245,250 ****
--- 245,252 ----
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
bool enable, bool skip_system);
+ static void ATPrepSetPctfree(Relation rel);
+ static void ATExecSetPctfree(Relation rel, Node *newValue);
static void copy_relation_data(Relation rel, SMgrRelation dst);
static void update_ri_trigger_args(Oid relid,
const char *oldname,
*************** ExecuteTruncate(List *relations)
*** 629,635 ****
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, true);
}
}
--- 631,637 ----
/*
* Reconstruct the indexes to match, and we're done.
*/
! reindex_relation(heap_relid, true, /* use last pctfree */ -1);
}
}
*************** ATPrepCmd(List **wqueue, Relation rel, A
*** 2008,2013 ****
--- 2010,2019 ----
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
+ case AT_SetPctfree:
+ ATPrepSetPctfree(rel);
+ pass = AT_PASS_ADD_CONSTR;
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
*************** ATExecCmd(AlteredTableInfo *tab, Relatio
*** 2183,2188 ****
--- 2189,2197 ----
case AT_DisableTrigUser: /* DISABLE TRIGGER USER */
ATExecEnableDisableTrigger(rel, NULL, false, true);
break;
+ case AT_SetPctfree: /* SET PCTFREE */
+ ATExecSetPctfree(rel, cmd->def);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
*************** ATRewriteTables(List **wqueue)
*** 2297,2303 ****
* which is all-new anyway). We do not need
* CommandCounterIncrement() because reindex_relation does it.
*/
! reindex_relation(tab->relid, false);
}
else
{
--- 2306,2312 ----
* which is all-new anyway). We do not need
* CommandCounterIncrement() because reindex_relation does it.
*/
! reindex_relation(tab->relid, false, /* use last pctfree */ -1);
}
else
{
*************** ATExecAddIndex(AlteredTableInfo *tab, Re
*** 3697,3702 ****
--- 3706,3712 ----
stmt->unique,
stmt->primary,
stmt->isconstraint,
+ stmt->pctfree,
true, /* is_alter_table */
check_rights,
skip_build,
*************** ATExecAddConstraint(AlteredTableInfo *ta
*** 3797,3802 ****
--- 3807,3847 ----
}
/*
+ * ALTER IONDEX SET PCTFREE
+ */
+ static void
+ ATPrepSetPctfree(Relation rel)
+ {
+ if (rel->rd_rel->relkind != RELKIND_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a index",
+ RelationGetRelationName(rel))));
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+ }
+
+ static void
+ ATExecSetPctfree(Relation rel, Node *newValue)
+ {
+ int pctfree;
+
+ Assert(rel->rd_rel->relkind == RELKIND_INDEX);
+ Assert(IsA(newValue, Integer));
+
+ pctfree = intVal(newValue);
+ CatalogSetIndexPctfree(rel, pctfree);
+ CommandCounterIncrement();
+
+ /* Only catalog was changed. Actual change delays until next reindex. */
+ elog(NOTICE, "PCTFREE for %s has changed to %d. Do REINDEX to reorganize.",
+ RelationGetRelationName(rel), pctfree);
+ }
+
+ /*
* Add a foreign-key constraint to a single table
*
* Subroutine for ATExecAddConstraint. Must already hold exclusive
*************** AlterTableCreateToastTable(Oid relOid, b
*** 5991,5996 ****
--- 6036,6042 ----
indexInfo->ii_Predicate = NIL;
indexInfo->ii_PredicateState = NIL;
indexInfo->ii_Unique = true;
+ indexInfo->ii_PctFree = -1;
classObjectId[0] = OID_BTREE_OPS_OID;
classObjectId[1] = INT4_BTREE_OPS_OID;
diff -cpr pgsql-orig/src/backend/nodes/copyfuncs.c pgsql/src/backend/nodes/copyfuncs.c
*** pgsql-orig/src/backend/nodes/copyfuncs.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/nodes/copyfuncs.c 2006-02-10 18:56:43.000000000 +0900
*************** _copyConstraint(Constraint *from)
*** 1622,1627 ****
--- 1622,1628 ----
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_NODE_FIELD(keys);
+ COPY_SCALAR_FIELD(pctfree);
COPY_STRING_FIELD(indexspace);
return newnode;
*************** _copyIndexStmt(IndexStmt *from)
*** 2002,2007 ****
--- 2003,2009 ----
COPY_SCALAR_FIELD(unique);
COPY_SCALAR_FIELD(primary);
COPY_SCALAR_FIELD(isconstraint);
+ COPY_SCALAR_FIELD(pctfree);
return newnode;
}
*************** _copyReindexStmt(ReindexStmt *from)
*** 2554,2559 ****
--- 2556,2562 ----
COPY_SCALAR_FIELD(kind);
COPY_NODE_FIELD(relation);
COPY_STRING_FIELD(name);
+ COPY_SCALAR_FIELD(pctfree);
COPY_SCALAR_FIELD(do_system);
COPY_SCALAR_FIELD(do_user);
diff -cpr pgsql-orig/src/backend/nodes/equalfuncs.c pgsql/src/backend/nodes/equalfuncs.c
*** pgsql-orig/src/backend/nodes/equalfuncs.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/nodes/equalfuncs.c 2006-02-10 18:56:43.000000000 +0900
*************** _equalIndexStmt(IndexStmt *a, IndexStmt
*** 965,970 ****
--- 965,971 ----
COMPARE_SCALAR_FIELD(unique);
COMPARE_SCALAR_FIELD(primary);
COMPARE_SCALAR_FIELD(isconstraint);
+ COMPARE_SCALAR_FIELD(pctfree);
return true;
}
*************** _equalReindexStmt(ReindexStmt *a, Reinde
*** 1428,1433 ****
--- 1429,1435 ----
COMPARE_SCALAR_FIELD(kind);
COMPARE_NODE_FIELD(relation);
COMPARE_STRING_FIELD(name);
+ COMPARE_SCALAR_FIELD(pctfree);
COMPARE_SCALAR_FIELD(do_system);
COMPARE_SCALAR_FIELD(do_user);
*************** _equalConstraint(Constraint *a, Constrai
*** 1686,1691 ****
--- 1688,1694 ----
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_NODE_FIELD(keys);
+ COMPARE_SCALAR_FIELD(pctfree);
COMPARE_STRING_FIELD(indexspace);
return true;
diff -cpr pgsql-orig/src/backend/nodes/outfuncs.c pgsql/src/backend/nodes/outfuncs.c
*** pgsql-orig/src/backend/nodes/outfuncs.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/nodes/outfuncs.c 2006-02-10 18:56:43.000000000 +0900
*************** _outIndexStmt(StringInfo str, IndexStmt
*** 1345,1350 ****
--- 1345,1351 ----
WRITE_BOOL_FIELD(unique);
WRITE_BOOL_FIELD(primary);
WRITE_BOOL_FIELD(isconstraint);
+ WRITE_INT_FIELD(pctfree);
}
static void
*************** _outConstraint(StringInfo str, Constrain
*** 1743,1754 ****
--- 1744,1757 ----
case CONSTR_PRIMARY:
appendStringInfo(str, "PRIMARY_KEY");
WRITE_NODE_FIELD(keys);
+ WRITE_INT_FIELD(pctfree);
WRITE_STRING_FIELD(indexspace);
break;
case CONSTR_UNIQUE:
appendStringInfo(str, "UNIQUE");
WRITE_NODE_FIELD(keys);
+ WRITE_INT_FIELD(pctfree);
WRITE_STRING_FIELD(indexspace);
break;
diff -cpr pgsql-orig/src/backend/parser/analyze.c pgsql/src/backend/parser/analyze.c
*** pgsql-orig/src/backend/parser/analyze.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/parser/analyze.c 2006-02-10 18:56:43.000000000 +0900
*************** transformIndexConstraints(ParseState *ps
*** 1219,1224 ****
--- 1219,1225 ----
index->relation = cxt->relation;
index->accessMethod = DEFAULT_INDEX_TYPE;
+ index->pctfree = constraint->pctfree;
index->tableSpace = constraint->indexspace;
index->indexParams = NIL;
index->whereClause = NULL;
diff -cpr pgsql-orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y
*** pgsql-orig/src/backend/parser/gram.y 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/parser/gram.y 2006-02-10 18:56:43.000000000 +0900
*************** static void doNegateFloat(Value *v);
*** 161,168 ****
%type <node> alter_column_default opclass_item alter_using
%type <ival> add_drop
! %type <node> alter_table_cmd alter_rel_cmd
! %type <list> alter_table_cmds alter_rel_cmds
%type <dbehavior> opt_drop_behavior
--- 161,168 ----
%type <node> alter_column_default opclass_item alter_using
%type <ival> add_drop
! %type <node> alter_table_cmd alter_index_cmd alter_rel_cmd
! %type <list> alter_table_cmds alter_index_cmds
%type <dbehavior> opt_drop_behavior
*************** static void doNegateFloat(Value *v);
*** 250,256 ****
%type <list> extract_list overlay_list position_list
%type <list> substr_list trim_list
! %type <ival> opt_interval
%type <node> overlay_placing substr_from substr_for
%type <boolean> opt_instead opt_analyze
--- 250,256 ----
%type <list> extract_list overlay_list position_list
%type <list> substr_list trim_list
! %type <ival> opt_interval opt_pctfree
%type <node> overlay_placing substr_from substr_for
%type <boolean> opt_instead opt_analyze
*************** static void doNegateFloat(Value *v);
*** 386,392 ****
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
! PARTIAL PASSWORD PLACING POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
--- 386,392 ----
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
! PARTIAL PASSWORD PLACING PCTFREE POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE
*************** AlterTableStmt:
*** 1313,1319 ****
n->relkind = OBJECT_TABLE;
$$ = (Node *)n;
}
! | ALTER INDEX relation_expr alter_rel_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n->relation = $3;
--- 1313,1319 ----
n->relkind = OBJECT_TABLE;
$$ = (Node *)n;
}
! | ALTER INDEX relation_expr alter_index_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n->relation = $3;
*************** alter_table_cmds:
*** 1328,1333 ****
--- 1328,1338 ----
| alter_table_cmds ',' alter_table_cmd { $$ = lappend($1, $3); }
;
+ alter_index_cmds:
+ alter_index_cmd { $$ = list_make1($1); }
+ | alter_index_cmds ',' alter_index_cmd { $$ = lappend($1, $3); }
+ ;
+
/* Subcommands that are for ALTER TABLE only */
alter_table_cmd:
/* ALTER TABLE <relation> ADD [COLUMN] <coldef> */
*************** alter_table_cmd:
*** 1500,1508 ****
}
;
! alter_rel_cmds:
! alter_rel_cmd { $$ = list_make1($1); }
! | alter_rel_cmds ',' alter_rel_cmd { $$ = lappend($1, $3); }
;
/* Subcommands that are for ALTER TABLE or ALTER INDEX */
--- 1505,1525 ----
}
;
! /* Subcommands that are for ALTER INDEX only */
! alter_index_cmd:
! /* ALTER INDEX <index> SET PCTFREE <IntegerOnly> */
! /* XXX: Need revert command? (ex. SET PCTFREE DEFAULT) */
! SET PCTFREE IntegerOnly
! {
! AlterTableCmd *n = makeNode(AlterTableCmd);
! n->subtype = AT_SetPctfree;
! n->def = (Node *) $3;
! $$ = (Node *)n;
! }
! | alter_rel_cmd
! {
! $$ = $1;
! }
;
/* Subcommands that are for ALTER TABLE or ALTER INDEX */
*************** ColConstraintElem:
*** 1844,1849 ****
--- 1861,1867 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
*************** ColConstraintElem:
*** 1855,1864 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
--- 1873,1883 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
*************** ColConstraintElem:
*** 1866,1875 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->indexspace = $2;
$$ = (Node *)n;
}
! | PRIMARY KEY OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
--- 1885,1895 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->pctfree = $2;
! n->indexspace = $3;
$$ = (Node *)n;
}
! | PRIMARY KEY opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
*************** ColConstraintElem:
*** 1877,1883 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->indexspace = $3;
$$ = (Node *)n;
}
| CHECK '(' a_expr ')'
--- 1897,1904 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = NULL;
! n->pctfree = $3;
! n->indexspace = $4;
$$ = (Node *)n;
}
| CHECK '(' a_expr ')'
*************** ColConstraintElem:
*** 1888,1893 ****
--- 1909,1915 ----
n->raw_expr = $3;
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
*************** ColConstraintElem:
*** 1907,1912 ****
--- 1929,1935 ----
}
n->cooked_expr = NULL;
n->keys = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
*************** ConstraintElem:
*** 2028,2037 ****
n->name = NULL;
n->raw_expr = $3;
n->cooked_expr = NULL;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE '(' columnList ')' OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
--- 2051,2061 ----
n->name = NULL;
n->raw_expr = $3;
n->cooked_expr = NULL;
+ n->pctfree = -1;
n->indexspace = NULL;
$$ = (Node *)n;
}
! | UNIQUE '(' columnList ')' opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
*************** ConstraintElem:
*** 2039,2048 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $3;
! n->indexspace = $5;
$$ = (Node *)n;
}
! | PRIMARY KEY '(' columnList ')' OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
--- 2063,2073 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $3;
! n->pctfree = $5;
! n->indexspace = $6;
$$ = (Node *)n;
}
! | PRIMARY KEY '(' columnList ')' opt_pctfree OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_PRIMARY;
*************** ConstraintElem:
*** 2050,2056 ****
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $4;
! n->indexspace = $6;
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
--- 2075,2082 ----
n->raw_expr = NULL;
n->cooked_expr = NULL;
n->keys = $4;
! n->pctfree = $6;
! n->indexspace = $7;
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
*************** opt_granted_by: GRANTED BY RoleId {
*** 3505,3511 ****
*****************************************************************************/
IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name
! access_method_clause '(' index_params ')' OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
--- 3531,3537 ----
*****************************************************************************/
IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name
! access_method_clause '(' index_params ')' opt_pctfree OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
*************** IndexStmt: CREATE index_opt_unique INDEX
*** 3513,3520 ****
n->relation = $6;
n->accessMethod = $7;
n->indexParams = $9;
! n->tableSpace = $11;
! n->whereClause = $12;
$$ = (Node *)n;
}
;
--- 3539,3547 ----
n->relation = $6;
n->accessMethod = $7;
n->indexParams = $9;
! n->pctfree = $11;
! n->tableSpace = $12;
! n->whereClause = $13;
$$ = (Node *)n;
}
;
*************** DropCastStmt: DROP CAST '(' Typename AS
*** 3956,3985 ****
*****************************************************************************/
ReindexStmt:
! REINDEX reindex_type qualified_name opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->relation = $3;
n->name = NULL;
$$ = (Node *)n;
}
! | REINDEX SYSTEM_P name opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
n->relation = NULL;
n->do_system = true;
n->do_user = false;
$$ = (Node *)n;
}
! | REINDEX DATABASE name opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
n->relation = NULL;
n->do_system = true;
n->do_user = true;
$$ = (Node *)n;
--- 3983,4015 ----
*****************************************************************************/
ReindexStmt:
! REINDEX reindex_type qualified_name opt_pctfree opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->relation = $3;
+ n->pctfree = $4;
n->name = NULL;
$$ = (Node *)n;
}
! | REINDEX SYSTEM_P name opt_pctfree opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
+ n->pctfree = $4;
n->relation = NULL;
n->do_system = true;
n->do_user = false;
$$ = (Node *)n;
}
! | REINDEX DATABASE name opt_pctfree opt_force
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = OBJECT_DATABASE;
n->name = $3;
n->relation = NULL;
+ n->pctfree = $4;
n->do_system = true;
n->do_user = true;
$$ = (Node *)n;
*************** reindex_type:
*** 3991,3996 ****
--- 4021,4038 ----
| TABLE { $$ = OBJECT_TABLE; }
;
+ opt_pctfree:
+ PCTFREE Iconst
+ {
+ $$ = $2;
+ }
+ | /*EMPTY*/
+ {
+ /* Insert "-1" meaning "use default" */
+ $$ = -1;
+ }
+ ;
+
opt_force: FORCE { $$ = TRUE; }
| /* EMPTY */ { $$ = FALSE; }
;
diff -cpr pgsql-orig/src/backend/parser/keywords.c pgsql/src/backend/parser/keywords.c
*** pgsql-orig/src/backend/parser/keywords.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/parser/keywords.c 2006-02-10 18:56:43.000000000 +0900
*************** static const ScanKeyword ScanKeywords[]
*** 256,261 ****
--- 256,262 ----
{"owner", OWNER},
{"partial", PARTIAL},
{"password", PASSWORD},
+ {"pctfree", PCTFREE},
{"placing", PLACING},
{"position", POSITION},
{"precision", PRECISION},
diff -cpr pgsql-orig/src/backend/tcop/utility.c pgsql/src/backend/tcop/utility.c
*** pgsql-orig/src/backend/tcop/utility.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/tcop/utility.c 2006-02-10 18:56:43.000000000 +0900
*************** ProcessUtility(Node *parsetree,
*** 794,799 ****
--- 794,800 ----
stmt->unique,
stmt->primary,
stmt->isconstraint,
+ stmt->pctfree,
false, /* is_alter_table */
true, /* check_rights */
false, /* skip_build */
*************** ProcessUtility(Node *parsetree,
*** 1052,1064 ****
switch (stmt->kind)
{
case OBJECT_INDEX:
! ReindexIndex(stmt->relation);
break;
case OBJECT_TABLE:
! ReindexTable(stmt->relation);
break;
case OBJECT_DATABASE:
! ReindexDatabase(stmt->name,
stmt->do_system, stmt->do_user);
break;
default:
--- 1053,1065 ----
switch (stmt->kind)
{
case OBJECT_INDEX:
! ReindexIndex(stmt->relation, stmt->pctfree);
break;
case OBJECT_TABLE:
! ReindexTable(stmt->relation, stmt->pctfree);
break;
case OBJECT_DATABASE:
! ReindexDatabase(stmt->name, stmt->pctfree,
stmt->do_system, stmt->do_user);
break;
default:
diff -cpr pgsql-orig/src/backend/utils/misc/guc.c pgsql/src/backend/utils/misc/guc.c
*** pgsql-orig/src/backend/utils/misc/guc.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/utils/misc/guc.c 2006-02-10 18:56:43.000000000 +0900
***************
*** 29,34 ****
--- 29,35 ----
#include "utils/guc_tables.h"
#include "access/twophase.h"
+ #include "catalog/index.h"
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "commands/async.h"
*************** static struct config_int ConfigureNamesI
*** 1570,1575 ****
--- 1571,1603 ----
0, 0, INT_MAX, assign_tcp_keepalives_count, show_tcp_keepalives_count
},
+ {
+ {"btree_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for btree pages during index creation."),
+ NULL
+ },
+ &btree_free_percent,
+ 10, 0, BTREE_MAX_PCTFREE, NULL, NULL
+ },
+
+ {
+ {"hash_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for hash pages during index creation."),
+ NULL
+ },
+ &hash_free_percent,
+ 25, 0, HASH_MAX_PCTFREE, NULL, NULL
+ },
+
+ {
+ {"gist_free_percent", PGC_SUSET, RESOURCES,
+ gettext_noop("Reserved free space for gist pages during index creation."),
+ NULL
+ },
+ &gist_free_percent,
+ 10, 0, GIST_MAX_PCTFREE, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL
diff -cpr pgsql-orig/src/backend/utils/misc/postgresql.conf.sample pgsql/src/backend/utils/misc/postgresql.conf.sample
*** pgsql-orig/src/backend/utils/misc/postgresql.conf.sample 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/backend/utils/misc/postgresql.conf.sample 2006-02-10 18:56:43.000000000 +0900
***************
*** 371,376 ****
--- 371,379 ----
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#statement_timeout = 0 # 0 is disabled, in milliseconds
+ #btree_free_percent = 10 # 0-50% of free space
+ #hash_free_percent = 25 # 0-50% of free space
+ #gist_free_percent = 10 # 0-50% of free space
# - Locale and Formatting -
diff -cpr pgsql-orig/src/bin/psql/tab-complete.c pgsql/src/bin/psql/tab-complete.c
*** pgsql-orig/src/bin/psql/tab-complete.c 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/bin/psql/tab-complete.c 2006-02-10 18:56:43.000000000 +0900
*************** psql_completion(char *text, int start, i
*** 632,638 ****
pg_strcasecmp(prev2_wd, "INDEX") == 0)
{
static const char *const list_ALTERINDEX[] =
! {"SET TABLESPACE", "OWNER TO", "RENAME TO", NULL};
COMPLETE_WITH_LIST(list_ALTERINDEX);
}
--- 632,638 ----
pg_strcasecmp(prev2_wd, "INDEX") == 0)
{
static const char *const list_ALTERINDEX[] =
! {"SET TABLESPACE", "SET PCTFREE", "OWNER TO", "RENAME TO", NULL};
COMPLETE_WITH_LIST(list_ALTERINDEX);
}
diff -cpr pgsql-orig/src/include/access/gist_private.h pgsql/src/include/access/gist_private.h
*** pgsql-orig/src/include/access/gist_private.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/access/gist_private.h 2006-02-10 18:56:43.000000000 +0900
*************** typedef struct
*** 181,186 ****
--- 181,187 ----
Relation r;
IndexTuple *itup; /* in/out, points to compressed entry */
int ituplen; /* length of itup */
+ Size freespace; /* free space to be left */
GISTInsertStack *stack;
bool needInsertComplete;
*************** extern void gistcheckpage(Relation rel,
*** 267,273 ****
extern Buffer gistNewBuffer(Relation r);
extern OffsetNumber gistfillbuffer(Relation r, Page page, IndexTuple *itup,
int len, OffsetNumber off);
! extern bool gistnospace(Page page, IndexTuple *itvec, int len);
extern IndexTuple *gistextractbuffer(Buffer buffer, int *len /* out */ );
extern IndexTuple *gistjoinvector(
IndexTuple *itvec, int *len,
--- 268,274 ----
extern Buffer gistNewBuffer(Relation r);
extern OffsetNumber gistfillbuffer(Relation r, Page page, IndexTuple *itup,
int len, OffsetNumber off);
! extern bool gistnospace(Page page, IndexTuple *itvec, int len, Size freespace);
extern IndexTuple *gistextractbuffer(Buffer buffer, int *len /* out */ );
extern IndexTuple *gistjoinvector(
IndexTuple *itvec, int *len,
diff -cpr pgsql-orig/src/include/catalog/index.h pgsql/src/include/catalog/index.h
*** pgsql-orig/src/include/catalog/index.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/catalog/index.h 2006-02-10 18:56:43.000000000 +0900
***************
*** 21,26 ****
--- 21,41 ----
#define DEFAULT_INDEX_TYPE "btree"
+ /*
+ * Knobs for fill factor control during index building.
+ */
+ #define BTREE_MAX_PCTFREE 50
+ #define HASH_MAX_PCTFREE 50
+ #define GIST_MAX_PCTFREE 50
+ extern int btree_free_percent;
+ extern int hash_free_percent;
+ extern int gist_free_percent;
+
+ #define INDEX_GET_PCTFREE(indexRel, defaultVal) \
+ ((indexRel)->rd_index->indpctfree != -1 \
+ ? (indexRel)->rd_index->indpctfree \
+ : (defaultVal))
+
/* Typedef for callback function for IndexBuildHeapScan */
typedef void (*IndexBuildCallback) (Relation index,
HeapTuple htup,
*************** extern double IndexBuildHeapScan(Relatio
*** 69,75 ****
IndexBuildCallback callback,
void *callback_state);
! extern void reindex_index(Oid indexId);
! extern bool reindex_relation(Oid relid, bool toast_too);
#endif /* INDEX_H */
--- 84,90 ----
IndexBuildCallback callback,
void *callback_state);
! extern void reindex_index(Oid indexId, int pctfree);
! extern bool reindex_relation(Oid relid, bool toast_too, int pctfree);
#endif /* INDEX_H */
diff -cpr pgsql-orig/src/include/catalog/indexing.h pgsql/src/include/catalog/indexing.h
*** pgsql-orig/src/include/catalog/indexing.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/catalog/indexing.h 2006-02-10 18:56:43.000000000 +0900
*************** extern void CatalogCloseIndexes(CatalogI
*** 33,38 ****
--- 33,39 ----
extern void CatalogIndexInsert(CatalogIndexState indstate,
HeapTuple heapTuple);
extern void CatalogUpdateIndexes(Relation heapRel, HeapTuple heapTuple);
+ extern void CatalogSetIndexPctfree(Relation index, int pctfree);
/*
diff -cpr pgsql-orig/src/include/catalog/pg_attribute.h pgsql/src/include/catalog/pg_attribute.h
*** pgsql-orig/src/include/catalog/pg_attribute.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/catalog/pg_attribute.h 2006-02-10 18:56:43.000000000 +0900
*************** DATA(insert ( 1259 tableoid 26 0 4 -
*** 454,462 ****
{ 0, {"indisunique"}, 16, -1, 1, 4, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisprimary"}, 16, -1, 1, 5, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisclustered"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
! { 0, {"indkey"}, 22, -1, -1, 7, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indclass"}, 30, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indexprs"}, 25, -1, -1, 9, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \
! { 0, {"indpred"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }
#endif /* PG_ATTRIBUTE_H */
--- 454,463 ----
{ 0, {"indisunique"}, 16, -1, 1, 4, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisprimary"}, 16, -1, 1, 5, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
{ 0, {"indisclustered"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \
! { 0, {"indpctfree"}, 21, -1, 2, 7, 0, -1, -1, true, 'p', 's', true, false, false, true, 0 }, \
! { 0, {"indkey"}, 22, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indclass"}, 30, -1, -1, 9, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \
! { 0, {"indexprs"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \
! { 0, {"indpred"}, 25, -1, -1, 11, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }
#endif /* PG_ATTRIBUTE_H */
diff -cpr pgsql-orig/src/include/catalog/pg_index.h pgsql/src/include/catalog/pg_index.h
*** pgsql-orig/src/include/catalog/pg_index.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/catalog/pg_index.h 2006-02-10 18:56:43.000000000 +0900
*************** CATALOG(pg_index,2610) BKI_WITHOUT_OIDS
*** 41,46 ****
--- 41,47 ----
bool indisunique; /* is this a unique index? */
bool indisprimary; /* is this index for primary key? */
bool indisclustered; /* is this the index last clustered by? */
+ int2 indpctfree; /* fill factor; reserved free space */
/* VARIABLE LENGTH FIELDS: */
int2vector indkey; /* column numbers of indexed cols, or 0 */
*************** typedef FormData_pg_index *Form_pg_index
*** 63,78 ****
* compiler constants for pg_index
* ----------------
*/
! #define Natts_pg_index 10
#define Anum_pg_index_indexrelid 1
#define Anum_pg_index_indrelid 2
#define Anum_pg_index_indnatts 3
#define Anum_pg_index_indisunique 4
#define Anum_pg_index_indisprimary 5
#define Anum_pg_index_indisclustered 6
! #define Anum_pg_index_indkey 7
! #define Anum_pg_index_indclass 8
! #define Anum_pg_index_indexprs 9
! #define Anum_pg_index_indpred 10
#endif /* PG_INDEX_H */
--- 64,80 ----
* compiler constants for pg_index
* ----------------
*/
! #define Natts_pg_index 11
#define Anum_pg_index_indexrelid 1
#define Anum_pg_index_indrelid 2
#define Anum_pg_index_indnatts 3
#define Anum_pg_index_indisunique 4
#define Anum_pg_index_indisprimary 5
#define Anum_pg_index_indisclustered 6
! #define Anum_pg_index_indpctfree 7
! #define Anum_pg_index_indkey 8
! #define Anum_pg_index_indclass 9
! #define Anum_pg_index_indexprs 10
! #define Anum_pg_index_indpred 11
#endif /* PG_INDEX_H */
diff -cpr pgsql-orig/src/include/commands/defrem.h pgsql/src/include/commands/defrem.h
*** pgsql-orig/src/include/commands/defrem.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/commands/defrem.h 2006-02-10 18:56:43.000000000 +0900
*************** extern void DefineIndex(RangeVar *heapRe
*** 29,43 ****
bool unique,
bool primary,
bool isconstraint,
bool is_alter_table,
bool check_rights,
bool skip_build,
bool quiet);
extern void RemoveIndex(RangeVar *relation, DropBehavior behavior);
! extern void ReindexIndex(RangeVar *indexRelation);
! extern void ReindexTable(RangeVar *relation);
extern void ReindexDatabase(const char *databaseName,
! bool do_system, bool do_user);
extern char *makeObjectName(const char *name1, const char *name2,
const char *label);
extern char *ChooseRelationName(const char *name1, const char *name2,
--- 29,44 ----
bool unique,
bool primary,
bool isconstraint,
+ int pctfree,
bool is_alter_table,
bool check_rights,
bool skip_build,
bool quiet);
extern void RemoveIndex(RangeVar *relation, DropBehavior behavior);
! extern void ReindexIndex(RangeVar *indexRelation, int pctfree);
! extern void ReindexTable(RangeVar *relation, int pctfree);
extern void ReindexDatabase(const char *databaseName,
! int pctfree, bool do_system, bool do_user);
extern char *makeObjectName(const char *name1, const char *name2,
const char *label);
extern char *ChooseRelationName(const char *name1, const char *name2,
diff -cpr pgsql-orig/src/include/nodes/execnodes.h pgsql/src/include/nodes/execnodes.h
*** pgsql-orig/src/include/nodes/execnodes.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/nodes/execnodes.h 2006-02-10 18:56:43.000000000 +0900
***************
*** 40,45 ****
--- 40,46 ----
* Predicate partial-index predicate, or NIL if none
* PredicateState exec state for predicate, or NIL if none
* Unique is it a unique index?
+ * PctFree fill factor
* ----------------
*/
typedef struct IndexInfo
*************** typedef struct IndexInfo
*** 52,57 ****
--- 53,59 ----
List *ii_Predicate; /* list of Expr */
List *ii_PredicateState; /* list of ExprState */
bool ii_Unique;
+ int ii_PctFree;
} IndexInfo;
/* ----------------
diff -cpr pgsql-orig/src/include/nodes/parsenodes.h pgsql/src/include/nodes/parsenodes.h
*** pgsql-orig/src/include/nodes/parsenodes.h 2006-02-10 18:49:13.000000000 +0900
--- pgsql/src/include/nodes/parsenodes.h 2006-02-10 18:56:43.000000000 +0900
*************** typedef enum AlterTableType
*** 837,843 ****
AT_EnableTrigAll, /* ENABLE TRIGGER ALL */
AT_DisableTrigAll, /* DISABLE TRIGGER ALL */
AT_EnableTrigUser, /* ENABLE TRIGGER USER */
! AT_DisableTrigUser /* DISABLE TRIGGER USER */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
--- 837,844 ----
AT_EnableTrigAll, /* ENABLE TRIGGER ALL */
AT_DisableTrigAll, /* DISABLE TRIGGER ALL */
AT_EnableTrigUser, /* ENABLE TRIGGER USER */
! AT_DisableTrigUser, /* DISABLE TRIGGER USER */
! AT_SetPctfree, /* SET PCTFREE */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
*************** typedef struct Constraint
*** 1042,1047 ****
--- 1043,1050 ----
Node *raw_expr; /* expr, as untransformed parse tree */
char *cooked_expr; /* expr, as nodeToString representation */
List *keys; /* String nodes naming referenced column(s) */
+ int pctfree; /* index tablespace for PKEY/UNIQUE
+ * constraints; -1 for default */
char *indexspace; /* index tablespace for PKEY/UNIQUE
* constraints; NULL for default */
} Constraint;
*************** typedef struct IndexStmt
*** 1395,1400 ****
--- 1398,1404 ----
bool unique; /* is index unique? */
bool primary; /* is index on primary key? */
bool isconstraint; /* is it from a CONSTRAINT clause? */
+ int pctfree; /* fill factor; reserved free space */
} IndexStmt;
/* ----------------------
*************** typedef struct ReindexStmt
*** 1797,1802 ****
--- 1801,1807 ----
ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, OBJECT_DATABASE */
RangeVar *relation; /* Table or index to reindex */
const char *name; /* name of database to reindex */
+ int pctfree; /* index fill factor */
bool do_system; /* include system tables in database case */
bool do_user; /* include user tables in database case */
} ReindexStmt;
Re: TODO-Item: B-tree fillfactor control
On Fri, 2006-02-10 at 19:12 +0900, ITAGAKI Takahiro wrote:
This is a revised patch for index fillfactor control:
- Split MAX_PCTFREE into three for each index method.
- B-tree indexes use their own settings when rightmost page is split.
- Fix a bug that GUC is modified when index building is canceled.
- Add some documentations.
Simon Riggs <simon@2ndquadrant.com> wrote:
Do you have any performance numbers for the extreme settings? It may be
worth having different max limits for each of the index types, since
they differ so widely in algorithms.Different max limits are done.
I worry about whether index works properly on high PCTFREE settings. I found
hash has its own sanity checking, but I don't know other indexes have.
Thanks.
I'm surprised that you do not use the parameter to control the RIGHTMOST
index block split factor for B-trees, which remains at a constant 67%.
The PCTFREE only seems to apply at CREATE INDEX time.Thanks for pointing out. I did not inadvertently use fillfactor on
the rightmost page. With the revised patch, PCTFREE will be considered
in such cases.# CREATE TABLE test (i int);
# INSERT INTO test SELECT generate_series(1, 100000);
# CREATE INDEX btree ON test USING btree (i) PCTFREE 0;
# SELECT relpages from pg_class where relname ='btree';
relpages | 249
# INSERT INTO test SELECT generate_series(100001, 200000);
# SELECT relpages from pg_class where relname ='btree';
relpages | 497 <-- +99.6%
This is great.
But default settings will change. Is this ok?
| | patched |
| now | free=10 | free=0 |
-----------------+-----+---------+--------+-
leaf (REINDEX) | 10 | 10 | 0 |
leaf (RIGHTMOST) | 30 | 10 | 0 | = leaf
node (REINDEX) | 30 | 30 | 0 | = 3*leaf
I think thats appropriate; lets see what others think.
If we support PCTFREE for compatibility reasons should we not also
support the alternative FILLFACTOR syntax also? I see no reason to
favour Oracle/DB2 compatability at the expense of SQLServer
compatibility.There are few synonyms in PostgreSQL, so I think it is better for us to
adopt only either one. I like FILLFACTOR personally, but compatibility
with Oracle is more important to users around me.
OK, no probs.
Reading through rest of patch now.
Best Regards, Simon Riggs
Re: TODO-Item: B-tree fillfactor control
On Fri, 2006-02-10 at 19:12 +0900, ITAGAKI Takahiro wrote:
Simon Riggs <simon@2ndquadrant.com> wrote:
I'm surprised that you do not use the parameter to control the RIGHTMOST
index block split factor for B-trees, which remains at a constant 67%.
The PCTFREE only seems to apply at CREATE INDEX time.Thanks for pointing out. I did not inadvertently use fillfactor on
the rightmost page. With the revised patch, PCTFREE will be considered
in such cases.# CREATE TABLE test (i int);
# INSERT INTO test SELECT generate_series(1, 100000);
# CREATE INDEX btree ON test USING btree (i) PCTFREE 0;
# SELECT relpages from pg_class where relname ='btree';
relpages | 249
# INSERT INTO test SELECT generate_series(100001, 200000);
# SELECT relpages from pg_class where relname ='btree';
relpages | 497 <-- +99.6%
This additional functionality looks like it would work for b-trees. I've
not looked at this for GIST and hash indexes.
The reduction in index size should give useful performance gains on
larger, growing tables with increasing keys. We'll test that.
Best Regards, Simon Riggs