agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH 7/8] Alter table set compression
15+ messages / 6 participants
[nested] [flat]
* [PATCH 7/8] Alter table set compression
@ 2021-03-10 09:04 Dilip Kumar <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Dilip Kumar @ 2021-03-10 09:04 UTC (permalink / raw)
Add support for changing the compression method associated
with a column. There are only built-in methods so we don't
need to rewrite the table, only the new tuples will be
compressed with the new compression method.
Dilip Kumar based on the patches from Ildus Kurbangaliev.
Design input from Robert Haas and Tomas Vondra.
Reviewed by Robert Haas, Tomas Vondra, Alexander Korotkov and Justin Pryzby
Discussions:
https://www.postgresql.org/message-id/[email protected]
https://www.postgresql.org/message-id/CA%2BTgmoaKDW1Oi9V%3Djc9hOGyf77NbkNEABuqgHD1Cq%3D%3D1QsOcxg%40...
https://www.postgresql.org/message-id/CA%2BTgmobSDVgUage9qQ5P_%3DF_9jaMkCgyKxUQGtFQU7oN4kX-AA%40mail...
https://www.postgresql.org/message-id/20201005160355.byp74sh3ejsv7wrj%40development
https://www.postgresql.org/message-id/CAFiTN-tzTTT2oqWdRGLv1dvvS5MC1W%2BLE%2B3bqWPJUZj4GnHOJg%40mail...
---
doc/src/sgml/ref/alter_table.sgml | 16 ++
src/backend/commands/tablecmds.c | 198 +++++++++++++++-----
src/backend/parser/gram.y | 9 +
src/bin/psql/tab-complete.c | 2 +-
src/include/nodes/parsenodes.h | 3 +-
src/test/regress/expected/compression.out | 47 ++++-
src/test/regress/expected/compression_1.out | 48 ++++-
src/test/regress/sql/compression.sql | 20 ++
8 files changed, 296 insertions(+), 47 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 38e416d183..e147831640 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -54,6 +54,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
+ ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -104,6 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
+ COMPRESSION <replaceable class="parameter">compression_method</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
@@ -384,6 +386,20 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <literal>SET COMPRESSION <replaceable class="parameter">compression_method</replaceable></literal>
+ </term>
+ <listitem>
+ <para>
+ This sets the compression method for a column. The supported compression
+ methods are <literal>pglz</literal> and <literal>lz4</literal>.
+ <literal>lz4</literal> is available only if <literal>--with-lz4</literal>
+ was used when building <productname>PostgreSQL</productname>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]</literal></term>
<listitem>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a0d2af0cde..bb284741ce 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -532,6 +532,8 @@ static void ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKM
static void ATExecGenericOptions(Relation rel, List *options);
static void ATExecSetRowSecurity(Relation rel, bool rls);
static void ATExecForceNoForceRowSecurity(Relation rel, bool force_rls);
+static ObjectAddress ATExecSetCompression(AlteredTableInfo *tab, Relation rel,
+ const char *column, Node *newValue, LOCKMODE lockmode);
static void index_copy_data(Relation rel, RelFileNode newrnode);
static const char *storage_name(char c);
@@ -3977,6 +3979,7 @@ AlterTableGetLockLevel(List *cmds)
*/
case AT_GenericOptions:
case AT_AlterColumnGenericOptions:
+ case AT_SetCompression:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -4511,7 +4514,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_DisableRowSecurity:
case AT_ForceRowSecurity:
case AT_NoForceRowSecurity:
- ATSimplePermissions(rel, ATT_TABLE);
+ case AT_SetCompression:
+ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
/* These commands never recurse */
/* No command-specific prep needed */
pass = AT_PASS_MISC;
@@ -4924,6 +4928,10 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
Assert(rel->rd_rel->relkind == RELKIND_INDEX);
ATExecAlterCollationRefreshVersion(rel, cmd->object);
break;
+ case AT_SetCompression:
+ address = ATExecSetCompression(tab, rel, cmd->name, cmd->def,
+ lockmode);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -7812,6 +7820,67 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options,
return address;
}
+/*
+ * Helper function for ATExecSetStorage and ATExecSetCompression
+ *
+ * Set the attcompression and/or attstorage for the respective index attribute
+ * if the respective input values are valid.
+ */
+static void
+ApplyChangesToIndexes(Relation rel, Relation attrelation, AttrNumber attnum,
+ char newcompression, char newstorage, LOCKMODE lockmode)
+{
+ HeapTuple tuple;
+ ListCell *lc;
+ Form_pg_attribute attrtuple;
+
+ foreach(lc, RelationGetIndexList(rel))
+ {
+ Oid indexoid = lfirst_oid(lc);
+ Relation indrel;
+ AttrNumber indattnum = 0;
+
+ indrel = index_open(indexoid, lockmode);
+
+ for (int i = 0; i < indrel->rd_index->indnatts; i++)
+ {
+ if (indrel->rd_index->indkey.values[i] == attnum)
+ {
+ indattnum = i + 1;
+ break;
+ }
+ }
+
+ if (indattnum == 0)
+ {
+ index_close(indrel, lockmode);
+ continue;
+ }
+
+ tuple = SearchSysCacheCopyAttNum(RelationGetRelid(indrel), indattnum);
+
+ if (HeapTupleIsValid(tuple))
+ {
+ attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
+
+ if (CompressionMethodIsValid(newcompression))
+ attrtuple->attcompression = newcompression;
+
+ if (newstorage != '\0')
+ attrtuple->attstorage = newstorage;
+
+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ attrtuple->attnum);
+
+ heap_freetuple(tuple);
+ }
+
+ index_close(indrel, lockmode);
+ }
+}
/*
* ALTER TABLE ALTER COLUMN SET STORAGE
*
@@ -7827,7 +7896,6 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
Form_pg_attribute attrtuple;
AttrNumber attnum;
ObjectAddress address;
- ListCell *lc;
Assert(IsA(newValue, String));
storagemode = strVal(newValue);
@@ -7891,47 +7959,8 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
* Apply the change to indexes as well (only for simple index columns,
* matching behavior of index.c ConstructTupleDescriptor()).
*/
- foreach(lc, RelationGetIndexList(rel))
- {
- Oid indexoid = lfirst_oid(lc);
- Relation indrel;
- AttrNumber indattnum = 0;
-
- indrel = index_open(indexoid, lockmode);
-
- for (int i = 0; i < indrel->rd_index->indnatts; i++)
- {
- if (indrel->rd_index->indkey.values[i] == attnum)
- {
- indattnum = i + 1;
- break;
- }
- }
-
- if (indattnum == 0)
- {
- index_close(indrel, lockmode);
- continue;
- }
-
- tuple = SearchSysCacheCopyAttNum(RelationGetRelid(indrel), indattnum);
-
- if (HeapTupleIsValid(tuple))
- {
- attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
- attrtuple->attstorage = newstorage;
-
- CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
-
- InvokeObjectPostAlterHook(RelationRelationId,
- RelationGetRelid(rel),
- attrtuple->attnum);
-
- heap_freetuple(tuple);
- }
-
- index_close(indrel, lockmode);
- }
+ ApplyChangesToIndexes(rel, attrelation, attnum, InvalidCompressionMethod,
+ newstorage, lockmode);
table_close(attrelation, RowExclusiveLock);
@@ -15118,6 +15147,89 @@ ATExecGenericOptions(Relation rel, List *options)
heap_freetuple(tuple);
}
+/*
+ * ALTER TABLE ALTER COLUMN SET COMPRESSION
+ *
+ * Return value is the address of the modified column
+ */
+static ObjectAddress
+ATExecSetCompression(AlteredTableInfo *tab,
+ Relation rel,
+ const char *column,
+ Node *newValue,
+ LOCKMODE lockmode)
+{
+ Relation attrel;
+ HeapTuple tuple;
+ Form_pg_attribute atttableform;
+ AttrNumber attnum;
+ char *compression;
+ char typstorage;
+ Oid cmoid;
+ Datum values[Natts_pg_attribute];
+ bool nulls[Natts_pg_attribute];
+ bool replace[Natts_pg_attribute];
+ ObjectAddress address;
+
+ Assert(IsA(newValue, String));
+ compression = strVal(newValue);
+
+ attrel = table_open(AttributeRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheAttName(RelationGetRelid(rel), column);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ column, RelationGetRelationName(rel))));
+
+ /* prevent them from altering a system attribute */
+ atttableform = (Form_pg_attribute) GETSTRUCT(tuple);
+ attnum = atttableform->attnum;
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"", column)));
+
+ typstorage = get_typstorage(atttableform->atttypid);
+
+ /* prevent from setting compression methods for uncompressible type */
+ if (!IsStorageCompressible(typstorage))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column data type %s does not support compression",
+ format_type_be(atttableform->atttypid))));
+
+ /* initialize buffers for new tuple values */
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(replace, false, sizeof(replace));
+
+ /* get the attribute compression method. */
+ cmoid = GetAttributeCompression(atttableform, compression);
+
+ atttableform->attcompression = cmoid;
+ CatalogTupleUpdate(attrel, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ atttableform->attnum);
+
+ ReleaseSysCache(tuple);
+
+ /* apply changes to the index column as well */
+ ApplyChangesToIndexes(rel, attrel, attnum, cmoid, '\0', lockmode);
+ table_close(attrel, RowExclusiveLock);
+
+ /* make changes visible */
+ CommandCounterIncrement();
+
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), atttableform->attnum);
+ return address;
+}
+
+
/*
* Preparation phase for SET LOGGED/UNLOGGED
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f8bc11d28b..07aa3c9330 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2308,6 +2308,15 @@ alter_table_cmd:
n->missing_ok = true;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET (COMPRESSION <cm>) */
+ | ALTER opt_column ColId SET optColumnCompression
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetCompression;
+ n->name = $3;
+ n->def = (Node *) makeString($5);
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */
| DROP opt_column IF_P EXISTS ColId opt_drop_behavior
{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index fdd1bdd92a..ebe07a17cd 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2115,7 +2115,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> SET */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET"))
- COMPLETE_WITH("(", "DEFAULT", "NOT NULL", "STATISTICS", "STORAGE");
+ COMPLETE_WITH("(", "COMPRESSION", "DEFAULT", "NOT NULL", "STATISTICS", "STORAGE");
/* ALTER TABLE ALTER [COLUMN] <foo> SET ( */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "(") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "("))
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 56e24529f1..b20c03f691 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1902,7 +1902,8 @@ typedef enum AlterTableType
AT_AddIdentity, /* ADD IDENTITY */
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
- AT_AlterCollationRefreshVersion /* ALTER COLLATION ... REFRESH VERSION */
+ AT_AlterCollationRefreshVersion, /* ALTER COLLATION ... REFRESH VERSION */
+ AT_SetCompression /* SET COMPRESSION */
} AlterTableType;
typedef struct ReplicaIdentityStmt
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 9ddffc8b18..19625708a9 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -247,12 +247,57 @@ CREATE TABLE cmdata2 (f1 text);
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
f1 | text | | | | extended | lz4 | |
+-- test alter compression method
+ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
+INSERT INTO cmdata VALUES (repeat('123456789',4004));
+\d+ cmdata
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | lz4 | |
+Indexes:
+ "idx" btree (f1)
+
+SELECT pg_column_compression(f1) FROM cmdata;
+ pg_column_compression
+-----------------------
+ pglz
+ lz4
+(2 rows)
+
+-- test alter compression method for the materialized view
+ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
+\d+ mv
+ Materialized view "public.mv"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ x | text | | | | extended | lz4 | |
+View definition:
+ SELECT cmdata1.f1 AS x
+ FROM cmdata1;
+
+-- test alter compression method for the partitioned table
+ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4;
+-- new data should be compressed with the current compression method
+INSERT INTO cmpart VALUES (repeat('123456789',1004));
+INSERT INTO cmpart VALUES (repeat('123456789',4004));
+SELECT pg_column_compression(f1) FROM cmpart;
+ pg_column_compression
+-----------------------
+ lz4
+ pglz
+ pglz
+ lz4
+(4 rows)
+
-- check data is ok
SELECT length(f1) FROM cmdata;
length
--------
10000
-(1 row)
+ 36036
+(2 rows)
SELECT length(f1) FROM cmdata1;
length
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
index 03a7f46554..bd642634a6 100644
--- a/src/test/regress/expected/compression_1.out
+++ b/src/test/regress/expected/compression_1.out
@@ -256,12 +256,58 @@ CREATE TABLE cmdata2 (f1 text);
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
f1 | text | | | | extended | pglz | |
+-- test alter compression method
+ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
+ERROR: unsupported LZ4 compression method
+DETAIL: This functionality requires the server to be built with lz4 support.
+HINT: You need to rebuild PostgreSQL using --with-lz4.
+INSERT INTO cmdata VALUES (repeat('123456789',4004));
+\d+ cmdata
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | pglz | |
+Indexes:
+ "idx" btree (f1)
+
+SELECT pg_column_compression(f1) FROM cmdata;
+ pg_column_compression
+-----------------------
+ pglz
+ pglz
+(2 rows)
+
+-- test alter compression method for the materialized view
+ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
+ERROR: relation "mv" does not exist
+\d+ mv
+-- test alter compression method for the partitioned table
+ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ERROR: relation "cmpart1" does not exist
+ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4;
+ERROR: unsupported LZ4 compression method
+DETAIL: This functionality requires the server to be built with lz4 support.
+HINT: You need to rebuild PostgreSQL using --with-lz4.
+-- new data should be compressed with the current compression method
+INSERT INTO cmpart VALUES (repeat('123456789',1004));
+ERROR: relation "cmpart" does not exist
+LINE 1: INSERT INTO cmpart VALUES (repeat('123456789',1004));
+ ^
+INSERT INTO cmpart VALUES (repeat('123456789',4004));
+ERROR: relation "cmpart" does not exist
+LINE 1: INSERT INTO cmpart VALUES (repeat('123456789',4004));
+ ^
+SELECT pg_column_compression(f1) FROM cmpart;
+ERROR: relation "cmpart" does not exist
+LINE 1: SELECT pg_column_compression(f1) FROM cmpart;
+ ^
-- check data is ok
SELECT length(f1) FROM cmdata;
length
--------
10000
-(1 row)
+ 36036
+(2 rows)
SELECT length(f1) FROM cmdata1;
ERROR: relation "cmdata1" does not exist
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index 6c9b24f1f7..00b1ff1137 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -111,6 +111,26 @@ DROP TABLE cmdata2;
CREATE TABLE cmdata2 (f1 text);
\d+ cmdata2
+-- test alter compression method
+ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
+INSERT INTO cmdata VALUES (repeat('123456789',4004));
+\d+ cmdata
+SELECT pg_column_compression(f1) FROM cmdata;
+
+-- test alter compression method for the materialized view
+ALTER MATERIALIZED VIEW mv ALTER COLUMN x SET COMPRESSION lz4;
+\d+ mv
+
+-- test alter compression method for the partitioned table
+ALTER TABLE cmpart1 ALTER COLUMN f1 SET COMPRESSION pglz;
+ALTER TABLE cmpart2 ALTER COLUMN f1 SET COMPRESSION lz4;
+
+-- new data should be compressed with the current compression method
+INSERT INTO cmpart VALUES (repeat('123456789',1004));
+INSERT INTO cmpart VALUES (repeat('123456789',4004));
+
+SELECT pg_column_compression(f1) FROM cmpart;
+
-- check data is ok
SELECT length(f1) FROM cmdata;
SELECT length(f1) FROM cmdata1;
--
2.17.0
--cvVnyQ+4j833TQvp
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0008-default-to-with-lz4.patch"
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-08-01 15:37 Simon Riggs <[email protected]>
0 siblings, 2 replies; 15+ messages in thread
From: Simon Riggs @ 2022-08-01 15:37 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Amit Kapila <[email protected]>; pgsql-hackers
On Fri, 29 Jul 2022 at 13:49, Simon Riggs <[email protected]> wrote:
>
> On Thu, 28 Jul 2022 at 19:50, Tom Lane <[email protected]> wrote:
> >
> > Simon Riggs <[email protected]> writes:
> > > Thanks for the nudge. New version attached.
> >
> > I also see a speed improvement from this
> > ---
> > DROP TABLE IF EXISTS hash_speed;
> > CREATE unlogged TABLE hash_speed (x integer);
> > INSERT INTO hash_speed SELECT random()*10000000 FROM
> > generate_series(1,10000000) x;
> > vacuum hash_speed;
> > \timing on
> > CREATE INDEX ON hash_speed USING hash (x);
> > ---
> > Also, it seems like we've left some money on the table by not
> > exploiting downstream the knowledge that this sorting happened.
> > During an index build, it's no longer necessary for
> > _hash_pgaddtup to do _hash_binsearch, and therefore also not
> > _hash_get_indextuple_hashkey: we could just always append the new
> > tuple at the end. Perhaps checking it against the last existing
> > tuple is worth the trouble as a bug guard, but for sure we don't
> > need the log2(N) comparisons that _hash_binsearch will do.
>
> Hmm, I had that in an earlier version of the patch, not sure why it
> dropped out since I wrote it last year, but then I've got lots of
> future WIP patches in the area of hash indexes.
...
> > At this point the cfbot will start to bleat that the patch of
> > record doesn't apply, so I'm going to mark the CF entry committed.
> > If anyone wants to produce a follow-on patch, please make a
> > new entry.
>
> Will do. Thanks.
Using the above test case, I'm getting a further 4-7% improvement on
already committed code with the attached patch, which follows your
proposal.
The patch passes info via a state object, useful to avoid API churn in
later patches.
Adding to CFapp again.
--
Simon Riggs http://www.EnterpriseDB.com/
Attachments:
[application/octet-stream] hash_inserted_sorted.v2.patch (7.0K, ../../CANbhV-GBc5JoG0AneUGPZZW3o4OK5LjBGeKe_icpC3R1McrZWQ@mail.gmail.com/2-hash_inserted_sorted.v2.patch)
download | inline diff:
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index c361509d68..7d8176465c 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -39,6 +39,7 @@ typedef struct
HSpool *spool; /* NULL if not using spooling */
double indtuples; /* # tuples accepted into index */
Relation heapRel; /* heap relation descriptor */
+ HashInsertState istate; /* insert state */
} HashBuildState;
static void hashbuildCallback(Relation index,
@@ -118,6 +119,7 @@ hashbuild(Relation heap, Relation index, IndexInfo *indexInfo)
uint32 num_buckets;
long sort_threshold;
HashBuildState buildstate;
+ HashInsertStateData insertstate;
/*
* We expect to be called exactly once for any index relation. If that's
@@ -157,10 +159,15 @@ hashbuild(Relation heap, Relation index, IndexInfo *indexInfo)
else
sort_threshold = Min(sort_threshold, NLocBuffer);
+ insertstate.sorted = false;
if (num_buckets >= (uint32) sort_threshold)
- buildstate.spool = _h_spoolinit(heap, index, num_buckets);
+ {
+ insertstate.sorted = true;
+ buildstate.spool = _h_spoolinit(heap, index, num_buckets, (HashInsertState) &insertstate);
+ }
else
buildstate.spool = NULL;
+ buildstate.istate = (HashInsertState) &insertstate;
/* prepare to build the index */
buildstate.indtuples = 0;
@@ -212,6 +219,7 @@ hashbuildCallback(Relation index,
void *state)
{
HashBuildState *buildstate = (HashBuildState *) state;
+ HashInsertState istate = buildstate->istate;
Datum index_values[1];
bool index_isnull[1];
IndexTuple itup;
@@ -231,7 +239,7 @@ hashbuildCallback(Relation index,
itup = index_form_tuple(RelationGetDescr(index),
index_values, index_isnull);
itup->t_tid = *tid;
- _hash_doinsert(index, itup, buildstate->heapRel);
+ _hash_doinsert(index, itup, buildstate->heapRel, istate);
pfree(itup);
}
@@ -254,6 +262,7 @@ hashinsert(Relation rel, Datum *values, bool *isnull,
Datum index_values[1];
bool index_isnull[1];
IndexTuple itup;
+ HashInsertStateData istate;
/* convert data to a hash key; on failure, do not insert anything */
if (!_hash_convert_tuple(rel,
@@ -265,7 +274,9 @@ hashinsert(Relation rel, Datum *values, bool *isnull,
itup = index_form_tuple(RelationGetDescr(rel), index_values, index_isnull);
itup->t_tid = *ht_ctid;
- _hash_doinsert(rel, itup, heapRel);
+ istate.sorted = false;
+
+ _hash_doinsert(rel, itup, heapRel, (HashInsertState) &istate);
pfree(itup);
diff --git a/src/backend/access/hash/hashinsert.c b/src/backend/access/hash/hashinsert.c
index 4f2fecb908..6c26215cb0 100644
--- a/src/backend/access/hash/hashinsert.c
+++ b/src/backend/access/hash/hashinsert.c
@@ -34,7 +34,7 @@ static void _hash_vacuum_one_page(Relation rel, Relation hrel,
* and hashinsert. By here, itup is completely filled in.
*/
void
-_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel)
+_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel, HashInsertState istate)
{
Buffer buf = InvalidBuffer;
Buffer bucket_buf;
@@ -198,7 +198,7 @@ restart_insert:
START_CRIT_SECTION();
/* found page with enough space, so add the item here */
- itup_off = _hash_pgaddtup(rel, buf, itemsz, itup);
+ itup_off = _hash_pgaddtup(rel, buf, itemsz, itup, istate->sorted);
MarkBufferDirty(buf);
/* metapage operations */
@@ -266,7 +266,7 @@ restart_insert:
* page are sorted by hashkey value.
*/
OffsetNumber
-_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup)
+_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup, bool sorted)
{
OffsetNumber itup_off;
Page page;
@@ -275,9 +275,18 @@ _hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup)
_hash_checkpage(rel, buf, LH_BUCKET_PAGE | LH_OVERFLOW_PAGE);
page = BufferGetPage(buf);
- /* Find where to insert the tuple (preserving page's hashkey ordering) */
- hashkey = _hash_get_indextuple_hashkey(itup);
- itup_off = _hash_binsearch(page, hashkey);
+ /*
+ * Find where to insert the tuple (preserving page's hashkey ordering).
+ * If the input is already sorted by hashkey, then we can avoid the
+ * binary search and just add it to the end of the page.
+ */
+ if (sorted)
+ itup_off = PageGetMaxOffsetNumber(page) + 1;
+ else
+ {
+ hashkey = _hash_get_indextuple_hashkey(itup);
+ itup_off = _hash_binsearch(page, hashkey);
+ }
if (PageAddItem(page, (Item) itup, itemsize, itup_off, false, false)
== InvalidOffsetNumber)
diff --git a/src/backend/access/hash/hashsort.c b/src/backend/access/hash/hashsort.c
index 19563148d0..6213855a4c 100644
--- a/src/backend/access/hash/hashsort.c
+++ b/src/backend/access/hash/hashsort.c
@@ -50,6 +50,8 @@ struct HSpool
uint32 high_mask;
uint32 low_mask;
uint32 max_buckets;
+
+ HashInsertState istate;
};
@@ -57,7 +59,7 @@ struct HSpool
* create and initialize a spool structure
*/
HSpool *
-_h_spoolinit(Relation heap, Relation index, uint32 num_buckets)
+_h_spoolinit(Relation heap, Relation index, uint32 num_buckets, HashInsertState istate)
{
HSpool *hspool = (HSpool *) palloc0(sizeof(HSpool));
@@ -89,6 +91,8 @@ _h_spoolinit(Relation heap, Relation index, uint32 num_buckets)
NULL,
TUPLESORT_NONE);
+ hspool->istate = istate;
+
return hspool;
}
@@ -145,7 +149,7 @@ _h_indexbuild(HSpool *hspool, Relation heapRel)
Assert(hashkey >= lasthashkey);
#endif
- _hash_doinsert(hspool->index, itup, heapRel);
+ _hash_doinsert(hspool->index, itup, heapRel, hspool->istate);
pgstat_progress_update_param(PROGRESS_CREATEIDX_TUPLES_DONE,
++tups_done);
diff --git a/src/include/access/hash.h b/src/include/access/hash.h
index da372841c4..f0b016de96 100644
--- a/src/include/access/hash.h
+++ b/src/include/access/hash.h
@@ -357,6 +357,12 @@ typedef struct HashOptions
#define HASHOPTIONS_PROC 3
#define HASHNProcs 3
+typedef struct HashInsertStateData
+{
+ bool sorted;
+} HashInsertStateData;
+
+typedef HashInsertStateData *HashInsertState;
/* public routines */
@@ -390,9 +396,9 @@ extern void hashadjustmembers(Oid opfamilyoid,
/* private routines */
/* hashinsert.c */
-extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel);
+extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel, HashInsertState istate);
extern OffsetNumber _hash_pgaddtup(Relation rel, Buffer buf,
- Size itemsize, IndexTuple itup);
+ Size itemsize, IndexTuple itup, bool sorted);
extern void _hash_pgaddmultitup(Relation rel, Buffer buf, IndexTuple *itups,
OffsetNumber *itup_offsets, uint16 nitups);
@@ -446,7 +452,8 @@ extern bool _hash_first(IndexScanDesc scan, ScanDirection dir);
/* hashsort.c */
typedef struct HSpool HSpool; /* opaque struct in hashsort.c */
-extern HSpool *_h_spoolinit(Relation heap, Relation index, uint32 num_buckets);
+extern HSpool *_h_spoolinit(Relation heap, Relation index, uint32 num_buckets,
+ HashInsertState istate);
extern void _h_spooldestroy(HSpool *hspool);
extern void _h_spool(HSpool *hspool, ItemPointer self,
Datum *values, bool *isnull);
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-08-05 19:46 David Zhang <[email protected]>
parent: Simon Riggs <[email protected]>
1 sibling, 1 reply; 15+ messages in thread
From: David Zhang @ 2022-08-05 19:46 UTC (permalink / raw)
To: Simon Riggs <[email protected]>; Tom Lane <[email protected]>; +Cc: Amit Kapila <[email protected]>; pgsql-hackers
On 2022-08-01 8:37 a.m., Simon Riggs wrote:
> Using the above test case, I'm getting a further 4-7% improvement on
> already committed code with the attached patch, which follows your
> proposal.
I ran two test cases: for committed patch `hash_sort_by_hash.v3.patch`, I can see about 6 ~ 7% improvement; and after applied patch `hash_inserted_sorted.v2.patch`, I see about ~3% improvement. All the test results are based on 10 times average on two different machines.
Best regards,
--
David
Software Engineer
Highgo Software Inc. (Canada)
www.highgo.ca
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-08-30 16:27 Simon Riggs <[email protected]>
parent: David Zhang <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Simon Riggs @ 2022-08-30 16:27 UTC (permalink / raw)
To: David Zhang <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Fri, 5 Aug 2022 at 20:46, David Zhang <[email protected]> wrote:
>
> On 2022-08-01 8:37 a.m., Simon Riggs wrote:
> > Using the above test case, I'm getting a further 4-7% improvement on
> > already committed code with the attached patch, which follows your
> > proposal.
>
> I ran two test cases: for committed patch `hash_sort_by_hash.v3.patch`, I can see about 6 ~ 7% improvement; and after applied patch `hash_inserted_sorted.v2.patch`, I see about ~3% improvement. All the test results are based on 10 times average on two different machines.
Thanks for testing David.
It's a shame you only see 3%, but that's still worth it.
--
Simon Riggs http://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-09-21 01:31 David Rowley <[email protected]>
parent: Simon Riggs <[email protected]>
1 sibling, 2 replies; 15+ messages in thread
From: David Rowley @ 2022-09-21 01:31 UTC (permalink / raw)
To: Simon Riggs <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Tue, 2 Aug 2022 at 03:37, Simon Riggs <[email protected]> wrote:
> Using the above test case, I'm getting a further 4-7% improvement on
> already committed code with the attached patch, which follows your
> proposal.
>
> The patch passes info via a state object, useful to avoid API churn in
> later patches.
Hi Simon,
I took this patch for a spin and saw a 2.5% performance increase using
the random INT test that Tom posted. The index took an average of
7227.47 milliseconds on master and 7045.05 with the patch applied.
On making a pass of the changes, I noted down a few things.
1. In _h_spoolinit() the HSpool is allocated with palloc and then
you're setting the istate field to a pointer to the HashInsertState
which is allocated on the stack by the only calling function
(hashbuild()). Looking at hashbuild(), it looks like the return value
of _h_spoolinit is never put anywhere to make it available outside of
the function, so it does not seem like there is an actual bug there.
However, it just seems like a bug waiting to happen. If _h_spoolinit()
is pallocing memory, then we really shouldn't be setting pointer
fields in that memory to point to something on the stack. It might be
nicer if the istate field in HSpool was a HashInsertStateData and
_h_spoolinit() just memcpy'd the contents of that parameter. That
would make HSpool 4 bytes smaller and save additional pointer
dereferences in _hash_doinsert().
2. There are quite a few casts that are not required. e.g:
_hash_doinsert(rel, itup, heapRel, (HashInsertState) &istate);
buildstate.spool = _h_spoolinit(heap, index, num_buckets,
(HashInsertState) &insertstate);
buildstate.istate = (HashInsertState) &insertstate;
This is just my opinion, but I don't really see the value in having a
typedef for a pointer to HashInsertStateData. I can understand that if
the struct was local to a .c file, but you've got the struct and
pointer typedef in the same header. I understand we often do this in
the code, but I feel like we do it less often in newer code. e.g we do
it in aset.c but not generation.c (which is much newer than aset.c).
My personal preference would be just to name the struct
HashInsertState and have no extra pointer typedefs.
3. Just a minor nitpick. Line wraps at 80 chars. You're doing this
sometimes but not others. This seems just to be due to the additional
function parameters that have been added.
4. I added the following Assert to _hash_pgaddtup() as I expected the
itup_off to be set to the same thing before and after this change. I
see the Assert is failing in the regression tests.
Assert(PageGetMaxOffsetNumber(page) + 1 ==
_hash_binsearch(page, _hash_get_indextuple_hashkey(itup)));
I think this is because _hash_binsearch() returns the offset with the
first tuple with the given hashkey, so if there are duplicate hashkey
values then it looks like PageAddItemExtended() will set needshuffle
and memmove() the existing item(s) up one slot. I don't know this
hash index building code very well, but I wonder if it's worth having
another version of _hash_binsearch() that can be used to make
_hash_pgaddtup() put any duplicate hashkeys after the existing ones
rather than before and shuffle the others up? It sounds like that
might speed up normal insertions when there are many duplicate values
to hash.
I wonder if this might be the reason the random INT test didn't come
out as good as your original test which had unique values. The unique
values test would do less shuffling during PageAddItemExtended(). If
so, that implies that skipping the binary search is only part of the
gains here and that not shuffling tuples accounts for quite a bit of
the gain you're seeing. If so, then it would be good to not have to
shuffle duplicate hashkey tuples up in the page during normal
insertions as well as when building the index.
In any case, it would be nice to have some way to assert that we don't
accidentally pass sorted==true to _hash_pgaddtup() when there's an
existing item on the page with a higher hash value. Maybe we could
just look at the hash value of the last tuple on the page and ensure
it's <= to the current one?
5. I think it would be nicer to move the insertstate.sorted = false;
into the else branch in hashbuild(). However, you might have to do
that anyway if you were to do what I mentioned in #1.
David
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-09-21 11:43 Simon Riggs <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: Simon Riggs @ 2022-09-21 11:43 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Wed, 21 Sept 2022 at 02:32, David Rowley <[email protected]> wrote:
>
> On Tue, 2 Aug 2022 at 03:37, Simon Riggs <[email protected]> wrote:
> > Using the above test case, I'm getting a further 4-7% improvement on
> > already committed code with the attached patch, which follows your
> > proposal.
> >
> > The patch passes info via a state object, useful to avoid API churn in
> > later patches.
>
> Hi Simon,
>
> I took this patch for a spin and saw a 2.5% performance increase using
> the random INT test that Tom posted. The index took an average of
> 7227.47 milliseconds on master and 7045.05 with the patch applied.
Hi David,
Thanks for tests and review. I'm just jumping on a plane, so may not
respond in detail until next Mon.
--
Simon Riggs http://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-16 04:33 Simon Riggs <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 2 replies; 15+ messages in thread
From: Simon Riggs @ 2022-11-16 04:33 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Wed, 21 Sept 2022 at 02:32, David Rowley <[email protected]> wrote:
>
> I took this patch for a spin and saw a 2.5% performance increase using
> the random INT test that Tom posted. The index took an average of
> 7227.47 milliseconds on master and 7045.05 with the patch applied.
Thanks for the review, apologies for the delay in acting upon your comments.
My tests show the sorted and random tests are BOTH 4.6% faster with
the v3 changes using 5-test avg, but you'll be pleased to know your
kit is about 15.5% faster than mine, comparing absolute execution
times.
> On making a pass of the changes, I noted down a few things.
> 2. There are quite a few casts that are not required. e.g:
>
> _hash_doinsert(rel, itup, heapRel, (HashInsertState) &istate);
> buildstate.spool = _h_spoolinit(heap, index, num_buckets,
> (HashInsertState) &insertstate);
> buildstate.istate = (HashInsertState) &insertstate;
Removed
> 3. Just a minor nitpick. Line wraps at 80 chars. You're doing this
> sometimes but not others. This seems just to be due to the additional
> function parameters that have been added.
Done
> 4. I added the following Assert to _hash_pgaddtup() as I expected the
> itup_off to be set to the same thing before and after this change. I
> see the Assert is failing in the regression tests.
>
> Assert(PageGetMaxOffsetNumber(page) + 1 ==
> _hash_binsearch(page, _hash_get_indextuple_hashkey(itup)));
>
> I think this is because _hash_binsearch() returns the offset with the
> first tuple with the given hashkey, so if there are duplicate hashkey
> values then it looks like PageAddItemExtended() will set needshuffle
> and memmove() the existing item(s) up one slot. I don't know this
> hash index building code very well, but I wonder if it's worth having
> another version of _hash_binsearch() that can be used to make
> _hash_pgaddtup() put any duplicate hashkeys after the existing ones
> rather than before and shuffle the others up? It sounds like that
> might speed up normal insertions when there are many duplicate values
> to hash.
Sounds reasonable.
I tried changing src/backend/access/hash/hashinsert.c, line 307 (on
patched file) from
- itup_off = _hash_binsearch(page, hashkey);
to
+ itup_off = _hash_binsearch_last(page, hashkey) + 1;
since exactly such a function already exists in code.
But this seems to cause a consistent ~1% regression in performance,
which surprises me.
Test was the random INSERT SELECT with 10E6 rows after the CREATE INDEX.
Not sure what to suggest, but the above change is not included in v3.
> I wonder if this might be the reason the random INT test didn't come
> out as good as your original test which had unique values. The unique
> values test would do less shuffling during PageAddItemExtended(). If
> so, that implies that skipping the binary search is only part of the
> gains here and that not shuffling tuples accounts for quite a bit of
> the gain you're seeing. If so, then it would be good to not have to
> shuffle duplicate hashkey tuples up in the page during normal
> insertions as well as when building the index.
There is still a 1.4% lead for the sorted test over the random one, in my tests.
> In any case, it would be nice to have some way to assert that we don't
> accidentally pass sorted==true to _hash_pgaddtup() when there's an
> existing item on the page with a higher hash value. Maybe we could
> just look at the hash value of the last tuple on the page and ensure
> it's <= to the current one?
Done
> 5. I think it would be nicer to move the insertstate.sorted = false;
> into the else branch in hashbuild(). However, you might have to do
> that anyway if you were to do what I mentioned in #1.
Done
> 1. In _h_spoolinit() the HSpool is allocated with palloc and then
> you're setting the istate field to a pointer to the HashInsertState
> which is allocated on the stack by the only calling function
> (hashbuild()). Looking at hashbuild(), it looks like the return value
> of _h_spoolinit is never put anywhere to make it available outside of
> the function, so it does not seem like there is an actual bug there.
> However, it just seems like a bug waiting to happen. If _h_spoolinit()
> is pallocing memory, then we really shouldn't be setting pointer
> fields in that memory to point to something on the stack. It might be
> nicer if the istate field in HSpool was a HashInsertStateData and
> _h_spoolinit() just memcpy'd the contents of that parameter. That
> would make HSpool 4 bytes smaller and save additional pointer
> dereferences in _hash_doinsert().
> This is just my opinion, but I don't really see the value in having a
> typedef for a pointer to HashInsertStateData. I can understand that if
> the struct was local to a .c file, but you've got the struct and
> pointer typedef in the same header. I understand we often do this in
> the code, but I feel like we do it less often in newer code. e.g we do
> it in aset.c but not generation.c (which is much newer than aset.c).
> My personal preference would be just to name the struct
> HashInsertState and have no extra pointer typedefs.
Not done, but not disagreeing either, just not very comfortable
actually making those changes.
--
Simon Riggs http://www.EnterpriseDB.com/
Attachments:
[application/octet-stream] hash_inserted_sorted.v3.patch (7.5K, ../../CANbhV-EFzF-VF7ALfoJ4Kgc30f5oJ7qFF_AhrXNjH7OPBfgX_g@mail.gmail.com/2-hash_inserted_sorted.v3.patch)
download | inline diff:
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index c361509d68..67102997f1 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -39,6 +39,7 @@ typedef struct
HSpool *spool; /* NULL if not using spooling */
double indtuples; /* # tuples accepted into index */
Relation heapRel; /* heap relation descriptor */
+ HashInsertState istate; /* insert state */
} HashBuildState;
static void hashbuildCallback(Relation index,
@@ -118,6 +119,7 @@ hashbuild(Relation heap, Relation index, IndexInfo *indexInfo)
uint32 num_buckets;
long sort_threshold;
HashBuildState buildstate;
+ HashInsertStateData insertstate;
/*
* We expect to be called exactly once for any index relation. If that's
@@ -158,9 +160,16 @@ hashbuild(Relation heap, Relation index, IndexInfo *indexInfo)
sort_threshold = Min(sort_threshold, NLocBuffer);
if (num_buckets >= (uint32) sort_threshold)
- buildstate.spool = _h_spoolinit(heap, index, num_buckets);
+ {
+ insertstate.sorted = true;
+ buildstate.spool = _h_spoolinit(heap, index, num_buckets, &insertstate);
+ }
else
+ {
+ insertstate.sorted = false;
buildstate.spool = NULL;
+ }
+ buildstate.istate = &insertstate;
/* prepare to build the index */
buildstate.indtuples = 0;
@@ -212,6 +221,7 @@ hashbuildCallback(Relation index,
void *state)
{
HashBuildState *buildstate = (HashBuildState *) state;
+ HashInsertState istate = buildstate->istate;
Datum index_values[1];
bool index_isnull[1];
IndexTuple itup;
@@ -231,7 +241,7 @@ hashbuildCallback(Relation index,
itup = index_form_tuple(RelationGetDescr(index),
index_values, index_isnull);
itup->t_tid = *tid;
- _hash_doinsert(index, itup, buildstate->heapRel);
+ _hash_doinsert(index, itup, buildstate->heapRel, istate);
pfree(itup);
}
@@ -254,6 +264,7 @@ hashinsert(Relation rel, Datum *values, bool *isnull,
Datum index_values[1];
bool index_isnull[1];
IndexTuple itup;
+ HashInsertStateData istate;
/* convert data to a hash key; on failure, do not insert anything */
if (!_hash_convert_tuple(rel,
@@ -265,7 +276,9 @@ hashinsert(Relation rel, Datum *values, bool *isnull,
itup = index_form_tuple(RelationGetDescr(rel), index_values, index_isnull);
itup->t_tid = *ht_ctid;
- _hash_doinsert(rel, itup, heapRel);
+ istate.sorted = false;
+
+ _hash_doinsert(rel, itup, heapRel, &istate);
pfree(itup);
diff --git a/src/backend/access/hash/hashinsert.c b/src/backend/access/hash/hashinsert.c
index 4f2fecb908..bf251f346d 100644
--- a/src/backend/access/hash/hashinsert.c
+++ b/src/backend/access/hash/hashinsert.c
@@ -34,7 +34,8 @@ static void _hash_vacuum_one_page(Relation rel, Relation hrel,
* and hashinsert. By here, itup is completely filled in.
*/
void
-_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel)
+_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel,
+ HashInsertState istate)
{
Buffer buf = InvalidBuffer;
Buffer bucket_buf;
@@ -198,7 +199,7 @@ restart_insert:
START_CRIT_SECTION();
/* found page with enough space, so add the item here */
- itup_off = _hash_pgaddtup(rel, buf, itemsz, itup);
+ itup_off = _hash_pgaddtup(rel, buf, itemsz, itup, istate->sorted);
MarkBufferDirty(buf);
/* metapage operations */
@@ -266,7 +267,7 @@ restart_insert:
* page are sorted by hashkey value.
*/
OffsetNumber
-_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup)
+_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup, bool sorted)
{
OffsetNumber itup_off;
Page page;
@@ -275,9 +276,36 @@ _hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup)
_hash_checkpage(rel, buf, LH_BUCKET_PAGE | LH_OVERFLOW_PAGE);
page = BufferGetPage(buf);
- /* Find where to insert the tuple (preserving page's hashkey ordering) */
- hashkey = _hash_get_indextuple_hashkey(itup);
- itup_off = _hash_binsearch(page, hashkey);
+ /*
+ * Find where to insert the tuple (preserving page's hashkey ordering).
+ * If the input is already sorted by hashkey, then we can avoid the
+ * binary search and just add it to the end of the page (but check!).
+ *
+ * If we need to binary search, find the last matching value, not the
+ * first, since this means less data movement when we have duplicates.
+ */
+ if (sorted)
+ {
+ OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
+ itup_off = maxoff + 1;
+
+#ifdef USE_ASSERT_CHECKING
+ if (OffsetNumberIsValid(maxoff))
+ {
+ IndexTuple max_itup = (IndexTuple) PageGetItem(page,
+ PageGetItemId(page, maxoff));
+ uint32 max_hashkey = _hash_get_indextuple_hashkey(max_itup);
+
+ hashkey = _hash_get_indextuple_hashkey(itup);
+ Assert(max_hashkey <= hashkey);
+ }
+#endif
+ }
+ else
+ {
+ hashkey = _hash_get_indextuple_hashkey(itup);
+ itup_off = _hash_binsearch(page, hashkey);
+ }
if (PageAddItem(page, (Item) itup, itemsize, itup_off, false, false)
== InvalidOffsetNumber)
diff --git a/src/backend/access/hash/hashsort.c b/src/backend/access/hash/hashsort.c
index 19563148d0..6213855a4c 100644
--- a/src/backend/access/hash/hashsort.c
+++ b/src/backend/access/hash/hashsort.c
@@ -50,6 +50,8 @@ struct HSpool
uint32 high_mask;
uint32 low_mask;
uint32 max_buckets;
+
+ HashInsertState istate;
};
@@ -57,7 +59,7 @@ struct HSpool
* create and initialize a spool structure
*/
HSpool *
-_h_spoolinit(Relation heap, Relation index, uint32 num_buckets)
+_h_spoolinit(Relation heap, Relation index, uint32 num_buckets, HashInsertState istate)
{
HSpool *hspool = (HSpool *) palloc0(sizeof(HSpool));
@@ -89,6 +91,8 @@ _h_spoolinit(Relation heap, Relation index, uint32 num_buckets)
NULL,
TUPLESORT_NONE);
+ hspool->istate = istate;
+
return hspool;
}
@@ -145,7 +149,7 @@ _h_indexbuild(HSpool *hspool, Relation heapRel)
Assert(hashkey >= lasthashkey);
#endif
- _hash_doinsert(hspool->index, itup, heapRel);
+ _hash_doinsert(hspool->index, itup, heapRel, hspool->istate);
pgstat_progress_update_param(PROGRESS_CREATEIDX_TUPLES_DONE,
++tups_done);
diff --git a/src/include/access/hash.h b/src/include/access/hash.h
index da372841c4..ff032d3fd3 100644
--- a/src/include/access/hash.h
+++ b/src/include/access/hash.h
@@ -357,6 +357,12 @@ typedef struct HashOptions
#define HASHOPTIONS_PROC 3
#define HASHNProcs 3
+typedef struct HashInsertStateData
+{
+ bool sorted;
+} HashInsertStateData;
+
+typedef HashInsertStateData *HashInsertState;
/* public routines */
@@ -390,9 +396,10 @@ extern void hashadjustmembers(Oid opfamilyoid,
/* private routines */
/* hashinsert.c */
-extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel);
+extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel,
+ HashInsertState istate);
extern OffsetNumber _hash_pgaddtup(Relation rel, Buffer buf,
- Size itemsize, IndexTuple itup);
+ Size itemsize, IndexTuple itup, bool sorted);
extern void _hash_pgaddmultitup(Relation rel, Buffer buf, IndexTuple *itups,
OffsetNumber *itup_offsets, uint16 nitups);
@@ -446,7 +453,8 @@ extern bool _hash_first(IndexScanDesc scan, ScanDirection dir);
/* hashsort.c */
typedef struct HSpool HSpool; /* opaque struct in hashsort.c */
-extern HSpool *_h_spoolinit(Relation heap, Relation index, uint32 num_buckets);
+extern HSpool *_h_spoolinit(Relation heap, Relation index, uint32 num_buckets,
+ HashInsertState istate);
extern void _h_spooldestroy(HSpool *hspool);
extern void _h_spool(HSpool *hspool, ItemPointer self,
Datum *values, bool *isnull);
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-17 14:34 Tomas Vondra <[email protected]>
parent: Simon Riggs <[email protected]>
1 sibling, 1 reply; 15+ messages in thread
From: Tomas Vondra @ 2022-11-17 14:34 UTC (permalink / raw)
To: Simon Riggs <[email protected]>; David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
Hi,
I did some simple benchmark with v2 and v3, using the attached script,
which essentially just builds hash index on random data, with different
data types and maintenance_work_mem values. And what I see is this
(median of 10 runs):
machine data type m_w_m master v2 v3
------------------------------------------------------------
i5 bigint 128MB 9652 9402 9669
32MB 9545 9291 9535
4MB 9599 9371 9741
int 128MB 9666 9475 9676
32MB 9530 9347 9528
4MB 9595 9394 9624
text 128MB 9755 9596 9897
32MB 9711 9547 9846
4MB 9808 9744 10024
xeon bigint 128MB 10790 10555 10812
32MB 10690 10373 10579
4MB 10682 10351 10650
int 128MB 11258 10550 10712
32MB 10963 10272 10410
4MB 11152 10366 10589
text 128MB 10935 10694 10930
32MB 10822 10672 10861
4MB 10835 10684 10895
Or, relative to master:
machine data type memory v2 v3
----------------------------------------------------------
i5 bigint 128MB 97.40% 100.17%
32MB 97.34% 99.90%
4MB 97.62% 101.48%
int 128MB 98.03% 100.11%
32MB 98.08% 99.98%
4MB 97.91% 100.31%
text 128MB 98.37% 101.46%
32MB 98.32% 101.40%
4MB 99.35% 102.20%
xeon bigint 128MB 97.82% 100.20%
32MB 97.03% 98.95%
4MB 96.89% 99.70%
int 128MB 93.71% 95.15%
32MB 93.70% 94.95%
4MB 92.95% 94.95%
text 128MB 97.80% 99.96%
32MB 98.62% 100.36%
4MB 98.61% 100.55%
So to me it seems v2 performs demonstrably better, v3 is consistently
slower - not only compared to v2, but often also to master.
Attached is the script I used and the raw results - this includes also
results for logged tables - the improvement is smaller, but the
conclusions are otherwise similar.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/csv] i5.csv (17.9K, ../../[email protected]/2-i5.csv)
download | inline:
unlogged int pg-master 4MB 1 9654.418
unlogged int pg-master 32MB 1 9531.940
unlogged int pg-master 128MB 1 9676.285
unlogged int pg-master 4MB 2 9583.072
unlogged int pg-master 32MB 2 9527.424
unlogged int pg-master 128MB 2 9666.685
unlogged int pg-master 4MB 3 9581.031
unlogged int pg-master 32MB 3 9520.868
unlogged int pg-master 128MB 3 9652.160
unlogged int pg-master 4MB 4 9617.728
unlogged int pg-master 32MB 4 9532.923
unlogged int pg-master 128MB 4 9665.569
unlogged int pg-master 4MB 5 9590.640
unlogged int pg-master 32MB 5 9533.886
unlogged int pg-master 128MB 5 9668.846
unlogged int pg-master 4MB 6 9598.494
unlogged int pg-master 32MB 6 9528.925
unlogged int pg-master 128MB 6 9659.308
unlogged int pg-master 4MB 7 9605.972
unlogged int pg-master 32MB 7 9536.492
unlogged int pg-master 128MB 7 9662.790
unlogged int pg-master 4MB 8 9569.671
unlogged int pg-master 32MB 8 9522.346
unlogged int pg-master 128MB 8 9666.571
unlogged int pg-patched-v2 4MB 1 9392.726
unlogged int pg-patched-v2 32MB 1 9340.266
unlogged int pg-patched-v2 128MB 1 9478.272
unlogged int pg-patched-v2 4MB 2 9407.779
unlogged int pg-patched-v2 32MB 2 9339.967
unlogged int pg-patched-v2 128MB 2 9477.475
unlogged int pg-patched-v2 4MB 3 9403.061
unlogged int pg-patched-v2 32MB 3 9336.728
unlogged int pg-patched-v2 128MB 3 9465.251
unlogged int pg-patched-v2 4MB 4 9387.758
unlogged int pg-patched-v2 32MB 4 9346.300
unlogged int pg-patched-v2 128MB 4 9470.000
unlogged int pg-patched-v2 4MB 5 9394.634
unlogged int pg-patched-v2 32MB 5 9357.497
unlogged int pg-patched-v2 128MB 5 9474.249
unlogged int pg-patched-v2 4MB 6 9392.015
unlogged int pg-patched-v2 32MB 6 9350.377
unlogged int pg-patched-v2 128MB 6 9478.068
unlogged int pg-patched-v2 4MB 7 9397.745
unlogged int pg-patched-v2 32MB 7 9348.429
unlogged int pg-patched-v2 128MB 7 9476.509
unlogged int pg-patched-v2 4MB 8 9388.341
unlogged int pg-patched-v2 32MB 8 9358.987
unlogged int pg-patched-v2 128MB 8 9471.368
unlogged int pg-patched-v3 4MB 1 9673.855
unlogged int pg-patched-v3 32MB 1 9528.298
unlogged int pg-patched-v3 128MB 1 9680.650
unlogged int pg-patched-v3 4MB 2 9620.113
unlogged int pg-patched-v3 32MB 2 9528.132
unlogged int pg-patched-v3 128MB 2 9674.623
unlogged int pg-patched-v3 4MB 3 9635.342
unlogged int pg-patched-v3 32MB 3 9524.181
unlogged int pg-patched-v3 128MB 3 9678.119
unlogged int pg-patched-v3 4MB 4 9624.571
unlogged int pg-patched-v3 32MB 4 9526.947
unlogged int pg-patched-v3 128MB 4 9679.381
unlogged int pg-patched-v3 4MB 5 9621.756
unlogged int pg-patched-v3 32MB 5 9522.237
unlogged int pg-patched-v3 128MB 5 9668.972
unlogged int pg-patched-v3 4MB 6 9624.051
unlogged int pg-patched-v3 32MB 6 9531.034
unlogged int pg-patched-v3 128MB 6 9670.998
unlogged int pg-patched-v3 4MB 7 9624.062
unlogged int pg-patched-v3 32MB 7 9530.375
unlogged int pg-patched-v3 128MB 7 9671.800
unlogged int pg-patched-v3 4MB 8 9627.529
unlogged int pg-patched-v3 32MB 8 9530.377
unlogged int pg-patched-v3 128MB 8 9681.275
unlogged bigint pg-master 4MB 1 9643.725
unlogged bigint pg-master 32MB 1 9531.486
unlogged bigint pg-master 128MB 1 9655.398
unlogged bigint pg-master 4MB 2 9603.711
unlogged bigint pg-master 32MB 2 9541.767
unlogged bigint pg-master 128MB 2 9640.398
unlogged bigint pg-master 4MB 3 9591.157
unlogged bigint pg-master 32MB 3 9546.680
unlogged bigint pg-master 128MB 3 9649.760
unlogged bigint pg-master 4MB 4 9596.985
unlogged bigint pg-master 32MB 4 9538.558
unlogged bigint pg-master 128MB 4 9665.169
unlogged bigint pg-master 4MB 5 9598.192
unlogged bigint pg-master 32MB 5 9544.901
unlogged bigint pg-master 128MB 5 9650.479
unlogged bigint pg-master 4MB 6 9597.652
unlogged bigint pg-master 32MB 6 9544.621
unlogged bigint pg-master 128MB 6 9652.930
unlogged bigint pg-master 4MB 7 9603.189
unlogged bigint pg-master 32MB 7 9544.733
unlogged bigint pg-master 128MB 7 9654.036
unlogged bigint pg-master 4MB 8 9600.358
unlogged bigint pg-master 32MB 8 9550.625
unlogged bigint pg-master 128MB 8 9651.939
unlogged bigint pg-patched-v2 4MB 1 9386.658
unlogged bigint pg-patched-v2 32MB 1 9287.060
unlogged bigint pg-patched-v2 128MB 1 9395.348
unlogged bigint pg-patched-v2 4MB 2 9361.412
unlogged bigint pg-patched-v2 32MB 2 9291.066
unlogged bigint pg-patched-v2 128MB 2 9403.449
unlogged bigint pg-patched-v2 4MB 3 9359.636
unlogged bigint pg-patched-v2 32MB 3 9285.652
unlogged bigint pg-patched-v2 128MB 3 9398.311
unlogged bigint pg-patched-v2 4MB 4 9369.361
unlogged bigint pg-patched-v2 32MB 4 9296.932
unlogged bigint pg-patched-v2 128MB 4 9407.299
unlogged bigint pg-patched-v2 4MB 5 9382.918
unlogged bigint pg-patched-v2 32MB 5 9290.341
unlogged bigint pg-patched-v2 128MB 5 9399.579
unlogged bigint pg-patched-v2 4MB 6 9377.171
unlogged bigint pg-patched-v2 32MB 6 9288.233
unlogged bigint pg-patched-v2 128MB 6 9407.460
unlogged bigint pg-patched-v2 4MB 7 9372.959
unlogged bigint pg-patched-v2 32MB 7 9290.774
unlogged bigint pg-patched-v2 128MB 7 9398.297
unlogged bigint pg-patched-v2 4MB 8 9369.561
unlogged bigint pg-patched-v2 32MB 8 9295.631
unlogged bigint pg-patched-v2 128MB 8 9404.836
unlogged bigint pg-patched-v3 4MB 1 9679.933
unlogged bigint pg-patched-v3 32MB 1 9528.787
unlogged bigint pg-patched-v3 128MB 1 9665.300
unlogged bigint pg-patched-v3 4MB 2 9741.811
unlogged bigint pg-patched-v3 32MB 2 9531.529
unlogged bigint pg-patched-v3 128MB 2 9666.290
unlogged bigint pg-patched-v3 4MB 3 9742.754
unlogged bigint pg-patched-v3 32MB 3 9543.048
unlogged bigint pg-patched-v3 128MB 3 9677.763
unlogged bigint pg-patched-v3 4MB 4 9745.662
unlogged bigint pg-patched-v3 32MB 4 9533.794
unlogged bigint pg-patched-v3 128MB 4 9668.448
unlogged bigint pg-patched-v3 4MB 5 9740.789
unlogged bigint pg-patched-v3 32MB 5 9537.139
unlogged bigint pg-patched-v3 128MB 5 9665.373
unlogged bigint pg-patched-v3 4MB 6 9732.447
unlogged bigint pg-patched-v3 32MB 6 9539.617
unlogged bigint pg-patched-v3 128MB 6 9668.795
unlogged bigint pg-patched-v3 4MB 7 9742.392
unlogged bigint pg-patched-v3 32MB 7 9554.964
unlogged bigint pg-patched-v3 128MB 7 9677.288
unlogged bigint pg-patched-v3 4MB 8 9724.184
unlogged bigint pg-patched-v3 32MB 8 9532.340
unlogged bigint pg-patched-v3 128MB 8 9670.946
unlogged text pg-master 4MB 1 9852.816
unlogged text pg-master 32MB 1 9703.457
unlogged text pg-master 128MB 1 9752.660
unlogged text pg-master 4MB 2 9791.363
unlogged text pg-master 32MB 2 9702.918
unlogged text pg-master 128MB 2 9809.302
unlogged text pg-master 4MB 3 9795.882
unlogged text pg-master 32MB 3 9697.877
unlogged text pg-master 128MB 3 9748.856
unlogged text pg-master 4MB 4 9806.968
unlogged text pg-master 32MB 4 9711.327
unlogged text pg-master 128MB 4 9758.096
unlogged text pg-master 4MB 5 9809.259
unlogged text pg-master 32MB 5 9715.507
unlogged text pg-master 128MB 5 9762.164
unlogged text pg-master 4MB 6 9801.702
unlogged text pg-master 32MB 6 9709.935
unlogged text pg-master 128MB 6 9749.215
unlogged text pg-master 4MB 7 9813.346
unlogged text pg-master 32MB 7 9721.608
unlogged text pg-master 128MB 7 9762.012
unlogged text pg-master 4MB 8 9816.285
unlogged text pg-master 32MB 8 9730.965
unlogged text pg-master 128MB 8 9752.016
unlogged text pg-patched-v2 4MB 1 9759.529
unlogged text pg-patched-v2 32MB 1 9529.866
unlogged text pg-patched-v2 128MB 1 9595.115
unlogged text pg-patched-v2 4MB 2 9699.837
unlogged text pg-patched-v2 32MB 2 9540.821
unlogged text pg-patched-v2 128MB 2 9583.278
unlogged text pg-patched-v2 4MB 3 9720.143
unlogged text pg-patched-v2 32MB 3 9547.115
unlogged text pg-patched-v2 128MB 3 9583.830
unlogged text pg-patched-v2 4MB 4 9724.284
unlogged text pg-patched-v2 32MB 4 9547.064
unlogged text pg-patched-v2 128MB 4 9618.794
unlogged text pg-patched-v2 4MB 5 9736.147
unlogged text pg-patched-v2 32MB 5 9547.430
unlogged text pg-patched-v2 128MB 5 9591.221
unlogged text pg-patched-v2 4MB 6 9755.154
unlogged text pg-patched-v2 32MB 6 9544.444
unlogged text pg-patched-v2 128MB 6 9598.656
unlogged text pg-patched-v2 4MB 7 9752.151
unlogged text pg-patched-v2 32MB 7 9547.068
unlogged text pg-patched-v2 128MB 7 9600.866
unlogged text pg-patched-v2 4MB 8 9758.531
unlogged text pg-patched-v2 32MB 8 9550.969
unlogged text pg-patched-v2 128MB 8 9596.875
unlogged text pg-patched-v3 4MB 1 9959.885
unlogged text pg-patched-v3 32MB 1 9838.144
unlogged text pg-patched-v3 128MB 1 9897.041
unlogged text pg-patched-v3 4MB 2 9962.552
unlogged text pg-patched-v3 32MB 2 9986.427
unlogged text pg-patched-v3 128MB 2 9909.668
unlogged text pg-patched-v3 4MB 3 9968.361
unlogged text pg-patched-v3 32MB 3 9847.030
unlogged text pg-patched-v3 128MB 3 9895.187
unlogged text pg-patched-v3 4MB 4 10050.635
unlogged text pg-patched-v3 32MB 4 9845.488
unlogged text pg-patched-v3 128MB 4 9892.616
unlogged text pg-patched-v3 4MB 5 10018.493
unlogged text pg-patched-v3 32MB 5 9842.516
unlogged text pg-patched-v3 128MB 5 9918.404
unlogged text pg-patched-v3 4MB 6 10029.638
unlogged text pg-patched-v3 32MB 6 9838.194
unlogged text pg-patched-v3 128MB 6 9907.513
unlogged text pg-patched-v3 4MB 7 10034.448
unlogged text pg-patched-v3 32MB 7 9855.767
unlogged text pg-patched-v3 128MB 7 9897.823
unlogged text pg-patched-v3 4MB 8 10028.736
unlogged text pg-patched-v3 32MB 8 9849.087
unlogged text pg-patched-v3 128MB 8 9893.401
logged int pg-master 4MB 1 12412.107
logged int pg-master 32MB 1 12324.859
logged int pg-master 128MB 1 12447.700
logged int pg-master 4MB 2 12360.720
logged int pg-master 32MB 2 12335.505
logged int pg-master 128MB 2 12434.590
logged int pg-master 4MB 3 12424.423
logged int pg-master 32MB 3 12340.026
logged int pg-master 128MB 3 12430.393
logged int pg-master 4MB 4 12403.257
logged int pg-master 32MB 4 12322.572
logged int pg-master 128MB 4 12431.849
logged int pg-master 4MB 5 12418.625
logged int pg-master 32MB 5 12329.745
logged int pg-master 128MB 5 12453.723
logged int pg-master 4MB 6 12404.710
logged int pg-master 32MB 6 12354.664
logged int pg-master 128MB 6 12439.437
logged int pg-master 4MB 7 12391.891
logged int pg-master 32MB 7 12339.912
logged int pg-master 128MB 7 12423.014
logged int pg-master 4MB 8 12418.589
logged int pg-master 32MB 8 12342.617
logged int pg-master 128MB 8 12474.345
logged int pg-patched-v2 4MB 1 12220.128
logged int pg-patched-v2 32MB 1 12131.785
logged int pg-patched-v2 128MB 1 12226.402
logged int pg-patched-v2 4MB 2 12165.967
logged int pg-patched-v2 32MB 2 12112.299
logged int pg-patched-v2 128MB 2 12204.030
logged int pg-patched-v2 4MB 3 12236.172
logged int pg-patched-v2 32MB 3 12109.460
logged int pg-patched-v2 128MB 3 12193.945
logged int pg-patched-v2 4MB 4 12217.759
logged int pg-patched-v2 32MB 4 12112.089
logged int pg-patched-v2 128MB 4 12193.679
logged int pg-patched-v2 4MB 5 12176.960
logged int pg-patched-v2 32MB 5 12137.273
logged int pg-patched-v2 128MB 5 12203.934
logged int pg-patched-v2 4MB 6 12248.255
logged int pg-patched-v2 32MB 6 12109.845
logged int pg-patched-v2 128MB 6 12187.750
logged int pg-patched-v2 4MB 7 12223.401
logged int pg-patched-v2 32MB 7 12105.661
logged int pg-patched-v2 128MB 7 12192.662
logged int pg-patched-v2 4MB 8 12205.275
logged int pg-patched-v2 32MB 8 12120.241
logged int pg-patched-v2 128MB 8 12177.690
logged int pg-patched-v3 4MB 1 12349.103
logged int pg-patched-v3 32MB 1 12266.271
logged int pg-patched-v3 128MB 1 12333.351
logged int pg-patched-v3 4MB 2 12322.407
logged int pg-patched-v3 32MB 2 12267.312
logged int pg-patched-v3 128MB 2 12325.944
logged int pg-patched-v3 4MB 3 12409.966
logged int pg-patched-v3 32MB 3 12203.094
logged int pg-patched-v3 128MB 3 12302.365
logged int pg-patched-v3 4MB 4 12403.390
logged int pg-patched-v3 32MB 4 12213.453
logged int pg-patched-v3 128MB 4 12330.197
logged int pg-patched-v3 4MB 5 12345.208
logged int pg-patched-v3 32MB 5 12215.865
logged int pg-patched-v3 128MB 5 12312.705
logged int pg-patched-v3 4MB 6 12363.495
logged int pg-patched-v3 32MB 6 12226.611
logged int pg-patched-v3 128MB 6 12307.019
logged int pg-patched-v3 4MB 7 12416.736
logged int pg-patched-v3 32MB 7 12220.107
logged int pg-patched-v3 128MB 7 12306.833
logged int pg-patched-v3 4MB 8 12417.482
logged int pg-patched-v3 32MB 8 12239.163
logged int pg-patched-v3 128MB 8 12312.553
logged bigint pg-master 4MB 1 12461.978
logged bigint pg-master 32MB 1 12296.830
logged bigint pg-master 128MB 1 12445.515
logged bigint pg-master 4MB 2 12393.383
logged bigint pg-master 32MB 2 12315.553
logged bigint pg-master 128MB 2 12461.007
logged bigint pg-master 4MB 3 12396.803
logged bigint pg-master 32MB 3 12324.643
logged bigint pg-master 128MB 3 12470.186
logged bigint pg-master 4MB 4 12402.858
logged bigint pg-master 32MB 4 12310.737
logged bigint pg-master 128MB 4 12464.206
logged bigint pg-master 4MB 5 12404.769
logged bigint pg-master 32MB 5 12322.453
logged bigint pg-master 128MB 5 12478.433
logged bigint pg-master 4MB 6 12381.325
logged bigint pg-master 32MB 6 12326.174
logged bigint pg-master 128MB 6 12470.786
logged bigint pg-master 4MB 7 12396.002
logged bigint pg-master 32MB 7 12308.792
logged bigint pg-master 128MB 7 12467.052
logged bigint pg-master 4MB 8 12380.436
logged bigint pg-master 32MB 8 12319.985
logged bigint pg-master 128MB 8 12452.097
logged bigint pg-patched-v2 4MB 1 12344.675
logged bigint pg-patched-v2 32MB 1 12151.125
logged bigint pg-patched-v2 128MB 1 12293.647
logged bigint pg-patched-v2 4MB 2 12289.101
logged bigint pg-patched-v2 32MB 2 12185.116
logged bigint pg-patched-v2 128MB 2 12248.925
logged bigint pg-patched-v2 4MB 3 12276.101
logged bigint pg-patched-v2 32MB 3 12160.026
logged bigint pg-patched-v2 128MB 3 12239.541
logged bigint pg-patched-v2 4MB 4 12331.309
logged bigint pg-patched-v2 32MB 4 12204.184
logged bigint pg-patched-v2 128MB 4 12269.058
logged bigint pg-patched-v2 4MB 5 12285.750
logged bigint pg-patched-v2 32MB 5 12177.471
logged bigint pg-patched-v2 128MB 5 12262.924
logged bigint pg-patched-v2 4MB 6 12319.237
logged bigint pg-patched-v2 32MB 6 12176.342
logged bigint pg-patched-v2 128MB 6 12241.115
logged bigint pg-patched-v2 4MB 7 12331.982
logged bigint pg-patched-v2 32MB 7 12176.000
logged bigint pg-patched-v2 128MB 7 12271.615
logged bigint pg-patched-v2 4MB 8 12306.363
logged bigint pg-patched-v2 32MB 8 12168.511
logged bigint pg-patched-v2 128MB 8 12254.381
logged bigint pg-patched-v3 4MB 1 12553.033
logged bigint pg-patched-v3 32MB 1 12313.387
logged bigint pg-patched-v3 128MB 1 12403.234
logged bigint pg-patched-v3 4MB 2 12409.704
logged bigint pg-patched-v3 32MB 2 12275.883
logged bigint pg-patched-v3 128MB 2 12388.745
logged bigint pg-patched-v3 4MB 3 12410.263
logged bigint pg-patched-v3 32MB 3 12278.993
logged bigint pg-patched-v3 128MB 3 12383.159
logged bigint pg-patched-v3 4MB 4 12395.535
logged bigint pg-patched-v3 32MB 4 12297.179
logged bigint pg-patched-v3 128MB 4 12398.280
logged bigint pg-patched-v3 4MB 5 12425.926
logged bigint pg-patched-v3 32MB 5 12301.850
logged bigint pg-patched-v3 128MB 5 12414.708
logged bigint pg-patched-v3 4MB 6 12411.449
logged bigint pg-patched-v3 32MB 6 12285.242
logged bigint pg-patched-v3 128MB 6 12406.891
logged bigint pg-patched-v3 4MB 7 12432.602
logged bigint pg-patched-v3 32MB 7 12276.781
logged bigint pg-patched-v3 128MB 7 12379.757
logged bigint pg-patched-v3 4MB 8 12406.400
logged bigint pg-patched-v3 32MB 8 12294.711
logged bigint pg-patched-v3 128MB 8 12408.950
logged text pg-master 4MB 1 12607.605
logged text pg-master 32MB 1 12454.827
logged text pg-master 128MB 1 12480.176
logged text pg-master 4MB 2 12550.605
logged text pg-master 32MB 2 12477.362
logged text pg-master 128MB 2 12514.874
logged text pg-master 4MB 3 12580.854
logged text pg-master 32MB 3 12476.969
logged text pg-master 128MB 3 12478.464
logged text pg-master 4MB 4 12559.952
logged text pg-master 32MB 4 12453.355
logged text pg-master 128MB 4 12489.514
logged text pg-master 4MB 5 12555.466
logged text pg-master 32MB 5 12457.918
logged text pg-master 128MB 5 12464.880
logged text pg-master 4MB 6 12593.790
logged text pg-master 32MB 6 12451.693
logged text pg-master 128MB 6 12491.989
logged text pg-master 4MB 7 12581.542
logged text pg-master 32MB 7 12454.555
logged text pg-master 128MB 7 12489.010
logged text pg-master 4MB 8 12616.921
logged text pg-master 32MB 8 12494.751
logged text pg-master 128MB 8 12486.112
logged text pg-patched-v2 4MB 1 12510.591
logged text pg-patched-v2 32MB 1 12339.051
logged text pg-patched-v2 128MB 1 12371.742
logged text pg-patched-v2 4MB 2 12431.712
logged text pg-patched-v2 32MB 2 12326.221
logged text pg-patched-v2 128MB 2 12439.648
logged text pg-patched-v2 4MB 3 12462.852
logged text pg-patched-v2 32MB 3 12353.925
logged text pg-patched-v2 128MB 3 12370.095
logged text pg-patched-v2 4MB 4 12443.636
logged text pg-patched-v2 32MB 4 12362.287
logged text pg-patched-v2 128MB 4 12368.157
logged text pg-patched-v2 4MB 5 12460.642
logged text pg-patched-v2 32MB 5 12359.844
logged text pg-patched-v2 128MB 5 12361.652
logged text pg-patched-v2 4MB 6 12440.107
logged text pg-patched-v2 32MB 6 12338.172
logged text pg-patched-v2 128MB 6 12371.768
logged text pg-patched-v2 4MB 7 12458.406
logged text pg-patched-v2 32MB 7 12354.978
logged text pg-patched-v2 128MB 7 12367.744
logged text pg-patched-v2 4MB 8 12452.007
logged text pg-patched-v2 32MB 8 12344.556
logged text pg-patched-v2 128MB 8 12347.132
logged text pg-patched-v3 4MB 1 12809.303
logged text pg-patched-v3 32MB 1 12506.537
logged text pg-patched-v3 128MB 1 12560.845
logged text pg-patched-v3 4MB 2 12718.902
logged text pg-patched-v3 32MB 2 12488.004
logged text pg-patched-v3 128MB 2 12534.634
logged text pg-patched-v3 4MB 3 12674.206
logged text pg-patched-v3 32MB 3 12519.851
logged text pg-patched-v3 128MB 3 12512.919
logged text pg-patched-v3 4MB 4 12718.576
logged text pg-patched-v3 32MB 4 12528.687
logged text pg-patched-v3 128MB 4 12537.277
logged text pg-patched-v3 4MB 5 12744.102
logged text pg-patched-v3 32MB 5 12559.595
logged text pg-patched-v3 128MB 5 12558.291
logged text pg-patched-v3 4MB 6 12733.577
logged text pg-patched-v3 32MB 6 12539.048
logged text pg-patched-v3 128MB 6 12546.691
logged text pg-patched-v3 4MB 7 12728.413
logged text pg-patched-v3 32MB 7 12550.806
logged text pg-patched-v3 128MB 7 12558.547
logged text pg-patched-v3 4MB 8 12771.221
logged text pg-patched-v3 32MB 8 12547.191
logged text pg-patched-v3 128MB 8 12558.159
[text/csv] xeon.csv (18.1K, ../../[email protected]/3-xeon.csv)
download | inline:
unlogged int pg-master 4MB 1 11212.340
unlogged int pg-master 32MB 1 10957.066
unlogged int pg-master 128MB 1 11350.739
unlogged int pg-master 4MB 2 11001.621
unlogged int pg-master 32MB 2 11151.721
unlogged int pg-master 128MB 2 11254.778
unlogged int pg-master 4MB 3 11170.518
unlogged int pg-master 32MB 3 11017.943
unlogged int pg-master 128MB 3 11168.393
unlogged int pg-master 4MB 4 11175.069
unlogged int pg-master 32MB 4 10968.291
unlogged int pg-master 128MB 4 11261.850
unlogged int pg-master 4MB 5 11197.233
unlogged int pg-master 32MB 5 10880.404
unlogged int pg-master 128MB 5 11151.834
unlogged int pg-master 4MB 6 11124.715
unlogged int pg-master 32MB 6 10933.991
unlogged int pg-master 128MB 6 11140.898
unlogged int pg-master 4MB 7 11133.431
unlogged int pg-master 32MB 7 10863.751
unlogged int pg-master 128MB 7 11335.291
unlogged int pg-master 4MB 8 11127.581
unlogged int pg-master 32MB 8 11049.110
unlogged int pg-master 128MB 8 11448.109
unlogged int pg-patched-v2 4MB 1 10422.706
unlogged int pg-patched-v2 32MB 1 10387.676
unlogged int pg-patched-v2 128MB 1 10362.823
unlogged int pg-patched-v2 4MB 2 10368.231
unlogged int pg-patched-v2 32MB 2 10143.613
unlogged int pg-patched-v2 128MB 2 10567.534
unlogged int pg-patched-v2 4MB 3 10267.025
unlogged int pg-patched-v2 32MB 3 10395.650
unlogged int pg-patched-v2 128MB 3 10522.902
unlogged int pg-patched-v2 4MB 4 10368.040
unlogged int pg-patched-v2 32MB 4 10164.550
unlogged int pg-patched-v2 128MB 4 10565.762
unlogged int pg-patched-v2 4MB 5 10243.110
unlogged int pg-patched-v2 32MB 5 10388.358
unlogged int pg-patched-v2 128MB 5 10534.194
unlogged int pg-patched-v2 4MB 6 10363.787
unlogged int pg-patched-v2 32MB 6 10139.289
unlogged int pg-patched-v2 128MB 6 10566.823
unlogged int pg-patched-v2 4MB 7 10338.760
unlogged int pg-patched-v2 32MB 7 10380.130
unlogged int pg-patched-v2 128MB 7 10536.206
unlogged int pg-patched-v2 4MB 8 10374.062
unlogged int pg-patched-v2 32MB 8 10117.878
unlogged int pg-patched-v2 128MB 8 10563.836
unlogged int pg-patched-v3 4MB 1 10526.629
unlogged int pg-patched-v3 32MB 1 10370.187
unlogged int pg-patched-v3 128MB 1 10596.924
unlogged int pg-patched-v3 4MB 2 10650.804
unlogged int pg-patched-v3 32MB 2 10448.979
unlogged int pg-patched-v3 128MB 2 10808.370
unlogged int pg-patched-v3 4MB 3 10486.682
unlogged int pg-patched-v3 32MB 3 10629.850
unlogged int pg-patched-v3 128MB 3 10574.358
unlogged int pg-patched-v3 4MB 4 10750.080
unlogged int pg-patched-v3 32MB 4 10359.337
unlogged int pg-patched-v3 128MB 4 10845.713
unlogged int pg-patched-v3 4MB 5 10506.505
unlogged int pg-patched-v3 32MB 5 10629.962
unlogged int pg-patched-v3 128MB 5 10671.033
unlogged int pg-patched-v3 4MB 6 10664.558
unlogged int pg-patched-v3 32MB 6 10312.866
unlogged int pg-patched-v3 128MB 6 10684.104
unlogged int pg-patched-v3 4MB 7 10481.773
unlogged int pg-patched-v3 32MB 7 10657.261
unlogged int pg-patched-v3 128MB 7 10740.772
unlogged int pg-patched-v3 4MB 8 10656.313
unlogged int pg-patched-v3 32MB 8 10343.225
unlogged int pg-patched-v3 128MB 8 10823.012
unlogged bigint pg-master 4MB 1 10628.429
unlogged bigint pg-master 32MB 1 10691.303
unlogged bigint pg-master 128MB 1 10600.259
unlogged bigint pg-master 4MB 2 10692.894
unlogged bigint pg-master 32MB 2 10309.123
unlogged bigint pg-master 128MB 2 10862.653
unlogged bigint pg-master 4MB 3 10568.350
unlogged bigint pg-master 32MB 3 10689.521
unlogged bigint pg-master 128MB 3 10606.902
unlogged bigint pg-master 4MB 4 10689.133
unlogged bigint pg-master 32MB 4 10348.126
unlogged bigint pg-master 128MB 4 10877.114
unlogged bigint pg-master 4MB 5 10502.149
unlogged bigint pg-master 32MB 5 10700.148
unlogged bigint pg-master 128MB 5 10781.857
unlogged bigint pg-master 4MB 6 10839.496
unlogged bigint pg-master 32MB 6 10697.944
unlogged bigint pg-master 128MB 6 10795.242
unlogged bigint pg-master 4MB 7 10692.528
unlogged bigint pg-master 32MB 7 10698.890
unlogged bigint pg-master 128MB 7 10785.599
unlogged bigint pg-master 4MB 8 10675.856
unlogged bigint pg-master 32MB 8 10606.032
unlogged bigint pg-master 128MB 8 10888.056
unlogged bigint pg-patched-v2 4MB 1 10343.973
unlogged bigint pg-patched-v2 32MB 1 10417.847
unlogged bigint pg-patched-v2 128MB 1 10414.280
unlogged bigint pg-patched-v2 4MB 2 10393.730
unlogged bigint pg-patched-v2 32MB 2 10079.997
unlogged bigint pg-patched-v2 128MB 2 10577.851
unlogged bigint pg-patched-v2 4MB 3 10297.846
unlogged bigint pg-patched-v2 32MB 3 10402.952
unlogged bigint pg-patched-v2 128MB 3 10342.415
unlogged bigint pg-patched-v2 4MB 4 10387.920
unlogged bigint pg-patched-v2 32MB 4 10342.201
unlogged bigint pg-patched-v2 128MB 4 10604.631
unlogged bigint pg-patched-v2 4MB 5 10323.294
unlogged bigint pg-patched-v2 32MB 5 10420.152
unlogged bigint pg-patched-v2 128MB 5 10364.134
unlogged bigint pg-patched-v2 4MB 6 10357.407
unlogged bigint pg-patched-v2 32MB 6 10149.190
unlogged bigint pg-patched-v2 128MB 6 10597.723
unlogged bigint pg-patched-v2 4MB 7 10240.055
unlogged bigint pg-patched-v2 32MB 7 10417.985
unlogged bigint pg-patched-v2 128MB 7 10532.128
unlogged bigint pg-patched-v2 4MB 8 10404.367
unlogged bigint pg-patched-v2 32MB 8 10124.898
unlogged bigint pg-patched-v2 128MB 8 10596.701
unlogged bigint pg-patched-v3 4MB 1 10628.666
unlogged bigint pg-patched-v3 32MB 1 10626.936
unlogged bigint pg-patched-v3 128MB 1 10658.369
unlogged bigint pg-patched-v3 4MB 2 10672.050
unlogged bigint pg-patched-v3 32MB 2 10558.961
unlogged bigint pg-patched-v3 128MB 2 10843.341
unlogged bigint pg-patched-v3 4MB 3 10574.276
unlogged bigint pg-patched-v3 32MB 3 10373.086
unlogged bigint pg-patched-v3 128MB 3 10643.319
unlogged bigint pg-patched-v3 4MB 4 10677.804
unlogged bigint pg-patched-v3 32MB 4 10533.658
unlogged bigint pg-patched-v3 128MB 4 10861.931
unlogged bigint pg-patched-v3 4MB 5 10584.099
unlogged bigint pg-patched-v3 32MB 5 10677.443
unlogged bigint pg-patched-v3 128MB 5 10775.506
unlogged bigint pg-patched-v3 4MB 6 10677.822
unlogged bigint pg-patched-v3 32MB 6 10589.254
unlogged bigint pg-patched-v3 128MB 6 10858.160
unlogged bigint pg-patched-v3 4MB 7 10583.542
unlogged bigint pg-patched-v3 32MB 7 10662.514
unlogged bigint pg-patched-v3 128MB 7 10779.881
unlogged bigint pg-patched-v3 4MB 8 10678.157
unlogged bigint pg-patched-v3 32MB 8 10568.090
unlogged bigint pg-patched-v3 128MB 8 10851.657
unlogged text pg-master 4MB 1 10750.281
unlogged text pg-master 32MB 1 10860.047
unlogged text pg-master 128MB 1 10743.916
unlogged text pg-master 4MB 2 10877.610
unlogged text pg-master 32MB 2 10557.321
unlogged text pg-master 128MB 2 10960.359
unlogged text pg-master 4MB 3 10723.262
unlogged text pg-master 32MB 3 10854.333
unlogged text pg-master 128MB 3 10726.068
unlogged text pg-master 4MB 4 10875.284
unlogged text pg-master 32MB 4 10792.805
unlogged text pg-master 128MB 4 10966.824
unlogged text pg-master 4MB 5 10794.465
unlogged text pg-master 32MB 5 10847.348
unlogged text pg-master 128MB 5 10889.954
unlogged text pg-master 4MB 6 10879.935
unlogged text pg-master 32MB 6 10778.351
unlogged text pg-master 128MB 6 10963.641
unlogged text pg-master 4MB 7 10792.500
unlogged text pg-master 32MB 7 10873.086
unlogged text pg-master 128MB 7 10909.288
unlogged text pg-master 4MB 8 10879.258
unlogged text pg-master 32MB 8 10797.001
unlogged text pg-master 128MB 8 10968.347
unlogged text pg-patched-v2 4MB 1 10597.288
unlogged text pg-patched-v2 32MB 1 10714.570
unlogged text pg-patched-v2 128MB 1 10588.399
unlogged text pg-patched-v2 4MB 2 10729.379
unlogged text pg-patched-v2 32MB 2 10642.061
unlogged text pg-patched-v2 128MB 2 10783.502
unlogged text pg-patched-v2 4MB 3 10570.980
unlogged text pg-patched-v2 32MB 3 10700.801
unlogged text pg-patched-v2 128MB 3 10583.633
unlogged text pg-patched-v2 4MB 4 10726.862
unlogged text pg-patched-v2 32MB 4 10644.142
unlogged text pg-patched-v2 128MB 4 10814.617
unlogged text pg-patched-v2 4MB 5 10638.824
unlogged text pg-patched-v2 32MB 5 10716.085
unlogged text pg-patched-v2 128MB 5 10574.453
unlogged text pg-patched-v2 4MB 6 10727.265
unlogged text pg-patched-v2 32MB 6 10618.246
unlogged text pg-patched-v2 128MB 6 10651.588
unlogged text pg-patched-v2 4MB 7 10641.388
unlogged text pg-patched-v2 32MB 7 10737.864
unlogged text pg-patched-v2 128MB 7 10736.335
unlogged text pg-patched-v2 4MB 8 10731.489
unlogged text pg-patched-v2 32MB 8 10643.881
unlogged text pg-patched-v2 128MB 8 10813.416
unlogged text pg-patched-v3 4MB 1 10803.940
unlogged text pg-patched-v3 32MB 1 10913.823
unlogged text pg-patched-v3 128MB 1 10907.069
unlogged text pg-patched-v3 4MB 2 10947.279
unlogged text pg-patched-v3 32MB 2 10576.114
unlogged text pg-patched-v3 128MB 2 11023.850
unlogged text pg-patched-v3 4MB 3 10845.273
unlogged text pg-patched-v3 32MB 3 10900.117
unlogged text pg-patched-v3 128MB 3 10761.260
unlogged text pg-patched-v3 4MB 4 10947.209
unlogged text pg-patched-v3 32MB 4 10651.778
unlogged text pg-patched-v3 128MB 4 11012.948
unlogged text pg-patched-v3 4MB 5 10802.547
unlogged text pg-patched-v3 32MB 5 10927.977
unlogged text pg-patched-v3 128MB 5 10917.798
unlogged text pg-patched-v3 4MB 6 10939.281
unlogged text pg-patched-v3 32MB 6 10620.058
unlogged text pg-patched-v3 128MB 6 10942.310
unlogged text pg-patched-v3 4MB 7 10850.170
unlogged text pg-patched-v3 32MB 7 10902.310
unlogged text pg-patched-v3 128MB 7 10769.453
unlogged text pg-patched-v3 4MB 8 10959.524
unlogged text pg-patched-v3 32MB 8 10821.277
unlogged text pg-patched-v3 128MB 8 11007.030
logged int pg-master 4MB 1 14070.524
logged int pg-master 32MB 1 14290.047
logged int pg-master 128MB 1 14124.578
logged int pg-master 4MB 2 14212.239
logged int pg-master 32MB 2 14084.160
logged int pg-master 128MB 2 14406.176
logged int pg-master 4MB 3 14120.126
logged int pg-master 32MB 3 14186.494
logged int pg-master 128MB 3 14284.532
logged int pg-master 4MB 4 14196.473
logged int pg-master 32MB 4 14091.591
logged int pg-master 128MB 4 14388.937
logged int pg-master 4MB 5 14111.736
logged int pg-master 32MB 5 14166.541
logged int pg-master 128MB 5 14114.186
logged int pg-master 4MB 6 14216.504
logged int pg-master 32MB 6 13854.606
logged int pg-master 128MB 6 14362.464
logged int pg-master 4MB 7 14041.347
logged int pg-master 32MB 7 14210.396
logged int pg-master 128MB 7 14157.633
logged int pg-master 4MB 8 14194.607
logged int pg-master 32MB 8 13862.254
logged int pg-master 128MB 8 14371.949
logged int pg-patched-v2 4MB 1 13343.739
logged int pg-patched-v2 32MB 1 13439.926
logged int pg-patched-v2 128MB 1 13554.670
logged int pg-patched-v2 4MB 2 13463.995
logged int pg-patched-v2 32MB 2 13372.124
logged int pg-patched-v2 128MB 2 13646.689
logged int pg-patched-v2 4MB 3 13354.390
logged int pg-patched-v2 32MB 3 13244.944
logged int pg-patched-v2 128MB 3 13529.719
logged int pg-patched-v2 4MB 4 13455.179
logged int pg-patched-v2 32MB 4 13134.486
logged int pg-patched-v2 128MB 4 13666.412
logged int pg-patched-v2 4MB 5 13309.102
logged int pg-patched-v2 32MB 5 13442.928
logged int pg-patched-v2 128MB 5 13395.993
logged int pg-patched-v2 4MB 6 13433.933
logged int pg-patched-v2 32MB 6 13116.237
logged int pg-patched-v2 128MB 6 13646.345
logged int pg-patched-v2 4MB 7 13300.120
logged int pg-patched-v2 32MB 7 13435.504
logged int pg-patched-v2 128MB 7 13550.565
logged int pg-patched-v2 4MB 8 13459.419
logged int pg-patched-v2 32MB 8 13433.174
logged int pg-patched-v2 128MB 8 13408.484
logged int pg-patched-v3 4MB 1 13490.198
logged int pg-patched-v3 32MB 1 13646.577
logged int pg-patched-v3 128MB 1 13657.259
logged int pg-patched-v3 4MB 2 13658.084
logged int pg-patched-v3 32MB 2 13460.189
logged int pg-patched-v3 128MB 2 13793.902
logged int pg-patched-v3 4MB 3 13445.686
logged int pg-patched-v3 32MB 3 13611.286
logged int pg-patched-v3 128MB 3 13635.382
logged int pg-patched-v3 4MB 4 13669.891
logged int pg-patched-v3 32MB 4 13462.101
logged int pg-patched-v3 128MB 4 13822.953
logged int pg-patched-v3 4MB 5 13447.781
logged int pg-patched-v3 32MB 5 13602.899
logged int pg-patched-v3 128MB 5 13640.051
logged int pg-patched-v3 4MB 6 13579.855
logged int pg-patched-v3 32MB 6 13240.477
logged int pg-patched-v3 128MB 6 13663.211
logged int pg-patched-v3 4MB 7 13482.800
logged int pg-patched-v3 32MB 7 13572.045
logged int pg-patched-v3 128MB 7 13620.236
logged int pg-patched-v3 4MB 8 13581.799
logged int pg-patched-v3 32MB 8 13434.478
logged int pg-patched-v3 128MB 8 13646.132
logged bigint pg-master 4MB 1 13610.381
logged bigint pg-master 32MB 1 13498.360
logged bigint pg-master 128MB 1 13759.949
logged bigint pg-master 4MB 2 13707.573
logged bigint pg-master 32MB 2 13591.404
logged bigint pg-master 128MB 2 13805.760
logged bigint pg-master 4MB 3 13572.081
logged bigint pg-master 32MB 3 13541.145
logged bigint pg-master 128MB 3 13766.283
logged bigint pg-master 4MB 4 13630.748
logged bigint pg-master 32MB 4 13538.992
logged bigint pg-master 128MB 4 13744.320
logged bigint pg-master 4MB 5 13590.960
logged bigint pg-master 32MB 5 13620.649
logged bigint pg-master 128MB 5 13801.964
logged bigint pg-master 4MB 6 13642.170
logged bigint pg-master 32MB 6 13576.441
logged bigint pg-master 128MB 6 13796.579
logged bigint pg-master 4MB 7 13564.719
logged bigint pg-master 32MB 7 13474.866
logged bigint pg-master 128MB 7 13805.190
logged bigint pg-master 4MB 8 13671.749
logged bigint pg-master 32MB 8 13605.260
logged bigint pg-master 128MB 8 13932.353
logged bigint pg-patched-v2 4MB 1 13338.608
logged bigint pg-patched-v2 32MB 1 13208.483
logged bigint pg-patched-v2 128MB 1 13494.127
logged bigint pg-patched-v2 4MB 2 13439.477
logged bigint pg-patched-v2 32MB 2 13311.578
logged bigint pg-patched-v2 128MB 2 13505.263
logged bigint pg-patched-v2 4MB 3 13316.149
logged bigint pg-patched-v2 32MB 3 13431.897
logged bigint pg-patched-v2 128MB 3 13499.461
logged bigint pg-patched-v2 4MB 4 13373.850
logged bigint pg-patched-v2 32MB 4 13269.926
logged bigint pg-patched-v2 128MB 4 13595.697
logged bigint pg-patched-v2 4MB 5 13291.063
logged bigint pg-patched-v2 32MB 5 13461.136
logged bigint pg-patched-v2 128MB 5 13500.913
logged bigint pg-patched-v2 4MB 6 13430.141
logged bigint pg-patched-v2 32MB 6 13297.263
logged bigint pg-patched-v2 128MB 6 13659.613
logged bigint pg-patched-v2 4MB 7 13295.139
logged bigint pg-patched-v2 32MB 7 13477.709
logged bigint pg-patched-v2 128MB 7 13500.635
logged bigint pg-patched-v2 4MB 8 13446.702
logged bigint pg-patched-v2 32MB 8 13302.468
logged bigint pg-patched-v2 128MB 8 13648.416
logged bigint pg-patched-v3 4MB 1 13500.975
logged bigint pg-patched-v3 32MB 1 13398.592
logged bigint pg-patched-v3 128MB 1 13656.928
logged bigint pg-patched-v3 4MB 2 13665.086
logged bigint pg-patched-v3 32MB 2 13470.416
logged bigint pg-patched-v3 128MB 2 13819.244
logged bigint pg-patched-v3 4MB 3 13435.062
logged bigint pg-patched-v3 32MB 3 13482.011
logged bigint pg-patched-v3 128MB 3 13671.820
logged bigint pg-patched-v3 4MB 4 13686.230
logged bigint pg-patched-v3 32MB 4 13480.810
logged bigint pg-patched-v3 128MB 4 13845.384
logged bigint pg-patched-v3 4MB 5 13540.526
logged bigint pg-patched-v3 32MB 5 13659.770
logged bigint pg-patched-v3 128MB 5 13679.698
logged bigint pg-patched-v3 4MB 6 13682.895
logged bigint pg-patched-v3 32MB 6 13344.385
logged bigint pg-patched-v3 128MB 6 13850.666
logged bigint pg-patched-v3 4MB 7 13516.910
logged bigint pg-patched-v3 32MB 7 13658.643
logged bigint pg-patched-v3 128MB 7 13653.941
logged bigint pg-patched-v3 4MB 8 13633.775
logged bigint pg-patched-v3 32MB 8 13236.061
logged bigint pg-patched-v3 128MB 8 13851.448
logged text pg-master 4MB 1 13777.249
logged text pg-master 32MB 1 13888.921
logged text pg-master 128MB 1 13873.919
logged text pg-master 4MB 2 13930.924
logged text pg-master 32MB 2 13753.164
logged text pg-master 128MB 2 14050.112
logged text pg-master 4MB 3 13810.274
logged text pg-master 32MB 3 13867.771
logged text pg-master 128MB 3 13871.550
logged text pg-master 4MB 4 13938.252
logged text pg-master 32MB 4 13756.988
logged text pg-master 128MB 4 13947.946
logged text pg-master 4MB 5 13790.700
logged text pg-master 32MB 5 13927.941
logged text pg-master 128MB 5 13884.301
logged text pg-master 4MB 6 13933.665
logged text pg-master 32MB 6 13747.073
logged text pg-master 128MB 6 13873.619
logged text pg-master 4MB 7 13797.372
logged text pg-master 32MB 7 13936.502
logged text pg-master 128MB 7 13910.442
logged text pg-master 4MB 8 13871.309
logged text pg-master 32MB 8 13590.082
logged text pg-master 128MB 8 13885.759
logged text pg-patched-v2 4MB 1 13621.664
logged text pg-patched-v2 32MB 1 13860.162
logged text pg-patched-v2 128MB 1 13741.051
logged text pg-patched-v2 4MB 2 13824.299
logged text pg-patched-v2 32MB 2 13839.780
logged text pg-patched-v2 128MB 2 13725.468
logged text pg-patched-v2 4MB 3 13753.981
logged text pg-patched-v2 32MB 3 13642.119
logged text pg-patched-v2 128MB 3 13742.972
logged text pg-patched-v2 4MB 4 13685.873
logged text pg-patched-v2 32MB 4 13588.961
logged text pg-patched-v2 128MB 4 13608.742
logged text pg-patched-v2 4MB 5 13766.508
logged text pg-patched-v2 32MB 5 13657.970
logged text pg-patched-v2 128MB 5 13751.956
logged text pg-patched-v2 4MB 6 13679.060
logged text pg-patched-v2 32MB 6 13587.558
logged text pg-patched-v2 128MB 6 13768.611
logged text pg-patched-v2 4MB 7 13846.233
logged text pg-patched-v2 32MB 7 13652.141
logged text pg-patched-v2 128MB 7 13760.362
logged text pg-patched-v2 4MB 8 13676.576
logged text pg-patched-v2 32MB 8 13582.143
logged text pg-patched-v2 128MB 8 13733.602
logged text pg-patched-v3 4MB 1 13788.365
logged text pg-patched-v3 32MB 1 13839.957
logged text pg-patched-v3 128MB 1 13730.298
logged text pg-patched-v3 4MB 2 13909.452
logged text pg-patched-v3 32MB 2 13534.829
logged text pg-patched-v3 128MB 2 13970.487
logged text pg-patched-v3 4MB 3 13758.653
logged text pg-patched-v3 32MB 3 13836.189
logged text pg-patched-v3 128MB 3 13794.029
logged text pg-patched-v3 4MB 4 13919.305
logged text pg-patched-v3 32MB 4 13542.498
logged text pg-patched-v3 128MB 4 13979.752
logged text pg-patched-v3 4MB 5 13790.413
logged text pg-patched-v3 32MB 5 13868.960
logged text pg-patched-v3 128MB 5 13731.331
logged text pg-patched-v3 4MB 6 13922.091
logged text pg-patched-v3 32MB 6 13547.376
logged text pg-patched-v3 128MB 6 13990.894
logged text pg-patched-v3 4MB 7 13773.408
logged text pg-patched-v3 32MB 7 13869.844
logged text pg-patched-v3 128MB 7 13990.783
logged text pg-patched-v3 4MB 8 13830.274
logged text pg-patched-v3 32MB 8 13851.681
logged text pg-patched-v3 128MB 8 13746.365
[application/x-shellscript] bench.sh (1.8K, ../../[email protected]/4-bench.sh)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-23 13:04 David Rowley <[email protected]>
parent: Simon Riggs <[email protected]>
1 sibling, 1 reply; 15+ messages in thread
From: David Rowley @ 2022-11-23 13:04 UTC (permalink / raw)
To: Simon Riggs <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Wed, 16 Nov 2022 at 17:33, Simon Riggs <[email protected]> wrote:
>
> Thanks for the review, apologies for the delay in acting upon your comments.
>
> My tests show the sorted and random tests are BOTH 4.6% faster with
> the v3 changes using 5-test avg, but you'll be pleased to know your
> kit is about 15.5% faster than mine, comparing absolute execution
> times.
Thanks for the updated patch.
I started to look at this again and I'm starting to think that the
HashInsertState struct is the wrong approach for passing along the
sorted flag to _hash_doinsert(). The reason I think this is that in
hashbuild() when setting buildstate.spool to NULL, you're also making
the decision about what to set the sorted flag to. However, in
reality, we already know what we should be passing *every* time we
call _hash_doinsert(). The only place where we can pass the sorted
option as true is in _h_indexbuild() when we're doing the sorted
version of the index build. Trying to make that decision any sooner
seems error-prone.
I understand you have made HashInsertState so that we don't need to
add new parameters should we ever need to pass something else along,
but I'm just thinking that if we ever need to add more, then we should
just reconsider this in the future. I think for today, the better
option is just to add the bool sorted as a parameter to
_hash_doinsert() and pass it as true in the single case where it's
valid to do so. That seems less likely that we'll inherit some
options from some other place after some future modification and end
up passing sorted as true when it should be false.
Another reason I didn't like the HashInsertState idea is that in the
v3 patch there's an HashInsertState in both HashBuildState and HSpool.
Because in the normal insert path (hashinsert), we've neither a
HashBuildState nor an HSpool, you're having to fake up a
HashInsertStateData to pass something along to _hash_doinsert() in
hashinsert(). When we're building an index, in the non-sorted index
build case, you're always passing the HashInsertStateData from the
HashBuildState, but when we're doing the sorted index build the one
from HSpool is passed. In other words, in each of the 3 calls to
_hash_doinsert(), the HashInsertStateData comes from a different
place.
Now, I do see that you've coded hashbuild() so both versions of the
HashInsertState point to the same HashInsertStateData, but I find it
unacceptable programming that in _h_spoolinit() the code palloc's the
memory for the HSpool and you're setting the istate field to the
HashInsertStateData that's on the stack. That just seems like a great
way to end up having istate pointing to junk should the HSpool ever
live beyond the hashbuild() call. If we really don't want HSpool to
live beyond hashbuild(), then it too should be a local variable to
hashbuild() instead of being palloc'ed in _h_spoolinit().
_h_spoolinit() could just be passed a pointer to the HSpool to
populate.
After getting rid of the HashInsertState code and just adding bool
sorted to _hash_doinsert() and _hash_pgaddtup(), the resulting patch
is much more simple:
v3:
src/backend/access/hash/hash.c | 19 ++++++++++++++++---
src/backend/access/hash/hashinsert.c | 40
++++++++++++++++++++++++++++++++++------
src/backend/access/hash/hashsort.c | 8 ++++++--
src/include/access/hash.h | 14 +++++++++++---
4 files changed, 67 insertions(+), 14 deletions(-)
v4:
src/backend/access/hash/hash.c | 4 ++--
src/backend/access/hash/hashinsert.c | 40 ++++++++++++++++++++++++++++--------
src/backend/access/hash/hashsort.c | 3 ++-
src/include/access/hash.h | 6 ++++--
4 files changed, 40 insertions(+), 13 deletions(-)
and v4 includes 7 extra lines in hashinsert.c for the Assert() I
mentioned in my previous email plus a bunch of extra comments.
I'd rather see this solved like v4 is doing it.
David
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index c361509d68..77fd147f68 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -231,7 +231,7 @@ hashbuildCallback(Relation index,
itup = index_form_tuple(RelationGetDescr(index),
index_values, index_isnull);
itup->t_tid = *tid;
- _hash_doinsert(index, itup, buildstate->heapRel);
+ _hash_doinsert(index, itup, buildstate->heapRel, false);
pfree(itup);
}
@@ -265,7 +265,7 @@ hashinsert(Relation rel, Datum *values, bool *isnull,
itup = index_form_tuple(RelationGetDescr(rel), index_values, index_isnull);
itup->t_tid = *ht_ctid;
- _hash_doinsert(rel, itup, heapRel);
+ _hash_doinsert(rel, itup, heapRel, false);
pfree(itup);
diff --git a/src/backend/access/hash/hashinsert.c b/src/backend/access/hash/hashinsert.c
index 23907d2e5b..6718ff18f3 100644
--- a/src/backend/access/hash/hashinsert.c
+++ b/src/backend/access/hash/hashinsert.c
@@ -32,9 +32,12 @@ static void _hash_vacuum_one_page(Relation rel, Relation hrel,
*
* This routine is called by the public interface routines, hashbuild
* and hashinsert. By here, itup is completely filled in.
+
+ * 'sorted' must only be passed as 'true' when inserts are done in hashkey
+ * order.
*/
void
-_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel)
+_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel, bool sorted)
{
Buffer buf = InvalidBuffer;
Buffer bucket_buf;
@@ -198,7 +201,7 @@ restart_insert:
START_CRIT_SECTION();
/* found page with enough space, so add the item here */
- itup_off = _hash_pgaddtup(rel, buf, itemsz, itup);
+ itup_off = _hash_pgaddtup(rel, buf, itemsz, itup, sorted);
MarkBufferDirty(buf);
/* metapage operations */
@@ -263,21 +266,42 @@ restart_insert:
*
* Returns the offset number at which the tuple was inserted. This function
* is responsible for preserving the condition that tuples in a hash index
- * page are sorted by hashkey value.
+ * page are sorted by hashkey value, however, if the caller is certain that
+ * the hashkey for the tuple being added is >= the hashkeys of all existing
+ * tuples on the page, then the 'sorted' flag may be passed as true.
*/
OffsetNumber
-_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup)
+_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup,
+ bool sorted)
{
OffsetNumber itup_off;
Page page;
- uint32 hashkey;
_hash_checkpage(rel, buf, LH_BUCKET_PAGE | LH_OVERFLOW_PAGE);
page = BufferGetPage(buf);
- /* Find where to insert the tuple (preserving page's hashkey ordering) */
- hashkey = _hash_get_indextuple_hashkey(itup);
- itup_off = _hash_binsearch(page, hashkey);
+ /*
+ * Find where to insert the tuple (preserving page's hashkey ordering).
+ * If the input is already sorted by hashkey, then we can avoid the
+ * binary search and just add it to the end of the page.
+ */
+ if (sorted)
+ {
+ itup_off = PageGetMaxOffsetNumber(page) + 1;
+
+ /* check the hashkey for itup is >= to the last existing tuple */
+ Assert(PageGetMaxOffsetNumber(page) == 0 ||
+ _hash_get_indextuple_hashkey((IndexTuple)
+ PageGetItem(page, PageGetItemId(page,
+ PageGetMaxOffsetNumber(page)))) <=
+ _hash_get_indextuple_hashkey(itup));
+ }
+ else
+ {
+ uint32 hashkey = _hash_get_indextuple_hashkey(itup);
+
+ itup_off = _hash_binsearch(page, hashkey);
+ }
if (PageAddItem(page, (Item) itup, itemsize, itup_off, false, false)
== InvalidOffsetNumber)
diff --git a/src/backend/access/hash/hashsort.c b/src/backend/access/hash/hashsort.c
index 19563148d0..a4ab8384e1 100644
--- a/src/backend/access/hash/hashsort.c
+++ b/src/backend/access/hash/hashsort.c
@@ -145,7 +145,8 @@ _h_indexbuild(HSpool *hspool, Relation heapRel)
Assert(hashkey >= lasthashkey);
#endif
- _hash_doinsert(hspool->index, itup, heapRel);
+ /* the tuples are sorted by hashkey, so pass 'sorted' as true */
+ _hash_doinsert(hspool->index, itup, heapRel, true);
pgstat_progress_update_param(PROGRESS_CREATEIDX_TUPLES_DONE,
++tups_done);
diff --git a/src/include/access/hash.h b/src/include/access/hash.h
index da372841c4..3ee37258d2 100644
--- a/src/include/access/hash.h
+++ b/src/include/access/hash.h
@@ -390,9 +390,11 @@ extern void hashadjustmembers(Oid opfamilyoid,
/* private routines */
/* hashinsert.c */
-extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel);
+extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel,
+ bool sorted);
extern OffsetNumber _hash_pgaddtup(Relation rel, Buffer buf,
- Size itemsize, IndexTuple itup);
+ Size itemsize, IndexTuple itup,
+ bool sorted);
extern void _hash_pgaddmultitup(Relation rel, Buffer buf, IndexTuple *itups,
OffsetNumber *itup_offsets, uint16 nitups);
Attachments:
[text/plain] hash_inserted_sorted.v4.patch (4.8K, ../../CAApHDvpiPzg8-gGAHasNDHmBsXF5+_gd+8RYyXNv6YcSo-6cpQ@mail.gmail.com/2-hash_inserted_sorted.v4.patch)
download | inline diff:
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index c361509d68..77fd147f68 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -231,7 +231,7 @@ hashbuildCallback(Relation index,
itup = index_form_tuple(RelationGetDescr(index),
index_values, index_isnull);
itup->t_tid = *tid;
- _hash_doinsert(index, itup, buildstate->heapRel);
+ _hash_doinsert(index, itup, buildstate->heapRel, false);
pfree(itup);
}
@@ -265,7 +265,7 @@ hashinsert(Relation rel, Datum *values, bool *isnull,
itup = index_form_tuple(RelationGetDescr(rel), index_values, index_isnull);
itup->t_tid = *ht_ctid;
- _hash_doinsert(rel, itup, heapRel);
+ _hash_doinsert(rel, itup, heapRel, false);
pfree(itup);
diff --git a/src/backend/access/hash/hashinsert.c b/src/backend/access/hash/hashinsert.c
index 23907d2e5b..6718ff18f3 100644
--- a/src/backend/access/hash/hashinsert.c
+++ b/src/backend/access/hash/hashinsert.c
@@ -32,9 +32,12 @@ static void _hash_vacuum_one_page(Relation rel, Relation hrel,
*
* This routine is called by the public interface routines, hashbuild
* and hashinsert. By here, itup is completely filled in.
+
+ * 'sorted' must only be passed as 'true' when inserts are done in hashkey
+ * order.
*/
void
-_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel)
+_hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel, bool sorted)
{
Buffer buf = InvalidBuffer;
Buffer bucket_buf;
@@ -198,7 +201,7 @@ restart_insert:
START_CRIT_SECTION();
/* found page with enough space, so add the item here */
- itup_off = _hash_pgaddtup(rel, buf, itemsz, itup);
+ itup_off = _hash_pgaddtup(rel, buf, itemsz, itup, sorted);
MarkBufferDirty(buf);
/* metapage operations */
@@ -263,21 +266,42 @@ restart_insert:
*
* Returns the offset number at which the tuple was inserted. This function
* is responsible for preserving the condition that tuples in a hash index
- * page are sorted by hashkey value.
+ * page are sorted by hashkey value, however, if the caller is certain that
+ * the hashkey for the tuple being added is >= the hashkeys of all existing
+ * tuples on the page, then the 'sorted' flag may be passed as true.
*/
OffsetNumber
-_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup)
+_hash_pgaddtup(Relation rel, Buffer buf, Size itemsize, IndexTuple itup,
+ bool sorted)
{
OffsetNumber itup_off;
Page page;
- uint32 hashkey;
_hash_checkpage(rel, buf, LH_BUCKET_PAGE | LH_OVERFLOW_PAGE);
page = BufferGetPage(buf);
- /* Find where to insert the tuple (preserving page's hashkey ordering) */
- hashkey = _hash_get_indextuple_hashkey(itup);
- itup_off = _hash_binsearch(page, hashkey);
+ /*
+ * Find where to insert the tuple (preserving page's hashkey ordering).
+ * If the input is already sorted by hashkey, then we can avoid the
+ * binary search and just add it to the end of the page.
+ */
+ if (sorted)
+ {
+ itup_off = PageGetMaxOffsetNumber(page) + 1;
+
+ /* check the hashkey for itup is >= to the last existing tuple */
+ Assert(PageGetMaxOffsetNumber(page) == 0 ||
+ _hash_get_indextuple_hashkey((IndexTuple)
+ PageGetItem(page, PageGetItemId(page,
+ PageGetMaxOffsetNumber(page)))) <=
+ _hash_get_indextuple_hashkey(itup));
+ }
+ else
+ {
+ uint32 hashkey = _hash_get_indextuple_hashkey(itup);
+
+ itup_off = _hash_binsearch(page, hashkey);
+ }
if (PageAddItem(page, (Item) itup, itemsize, itup_off, false, false)
== InvalidOffsetNumber)
diff --git a/src/backend/access/hash/hashsort.c b/src/backend/access/hash/hashsort.c
index 19563148d0..a4ab8384e1 100644
--- a/src/backend/access/hash/hashsort.c
+++ b/src/backend/access/hash/hashsort.c
@@ -145,7 +145,8 @@ _h_indexbuild(HSpool *hspool, Relation heapRel)
Assert(hashkey >= lasthashkey);
#endif
- _hash_doinsert(hspool->index, itup, heapRel);
+ /* the tuples are sorted by hashkey, so pass 'sorted' as true */
+ _hash_doinsert(hspool->index, itup, heapRel, true);
pgstat_progress_update_param(PROGRESS_CREATEIDX_TUPLES_DONE,
++tups_done);
diff --git a/src/include/access/hash.h b/src/include/access/hash.h
index da372841c4..3ee37258d2 100644
--- a/src/include/access/hash.h
+++ b/src/include/access/hash.h
@@ -390,9 +390,11 @@ extern void hashadjustmembers(Oid opfamilyoid,
/* private routines */
/* hashinsert.c */
-extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel);
+extern void _hash_doinsert(Relation rel, IndexTuple itup, Relation heapRel,
+ bool sorted);
extern OffsetNumber _hash_pgaddtup(Relation rel, Buffer buf,
- Size itemsize, IndexTuple itup);
+ Size itemsize, IndexTuple itup,
+ bool sorted);
extern void _hash_pgaddmultitup(Relation rel, Buffer buf, IndexTuple *itups,
OffsetNumber *itup_offsets, uint16 nitups);
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-23 13:07 David Rowley <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: David Rowley @ 2022-11-23 13:07 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Simon Riggs <[email protected]>; Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Fri, 18 Nov 2022 at 03:34, Tomas Vondra
<[email protected]> wrote:
> I did some simple benchmark with v2 and v3, using the attached script,
> which essentially just builds hash index on random data, with different
> data types and maintenance_work_mem values. And what I see is this
> (median of 10 runs):
> So to me it seems v2 performs demonstrably better, v3 is consistently
> slower - not only compared to v2, but often also to master.
Could this just be down to code alignment changes? There does not
really seem to be any fundamental differences which would explain
this.
David
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-23 13:27 Simon Riggs <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Simon Riggs @ 2022-11-23 13:27 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Wed, 23 Nov 2022 at 13:04, David Rowley <[email protected]> wrote:
> After getting rid of the HashInsertState code and just adding bool
> sorted to _hash_doinsert() and _hash_pgaddtup(), the resulting patch
> is much more simple:
Seems good to me and I wouldn't argue with any of your comments.
> and v4 includes 7 extra lines in hashinsert.c for the Assert() I
> mentioned in my previous email plus a bunch of extra comments.
Oh, I did already include that in v3 as requested.
> I'd rather see this solved like v4 is doing it.
Please do. No further comments. Thanks for your help
--
Simon Riggs http://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-23 19:08 Tomas Vondra <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Tomas Vondra @ 2022-11-23 19:08 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Simon Riggs <[email protected]>; Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On 11/23/22 14:07, David Rowley wrote:
> On Fri, 18 Nov 2022 at 03:34, Tomas Vondra
> <[email protected]> wrote:
>> I did some simple benchmark with v2 and v3, using the attached script,
>> which essentially just builds hash index on random data, with different
>> data types and maintenance_work_mem values. And what I see is this
>> (median of 10 runs):
>
>> So to me it seems v2 performs demonstrably better, v3 is consistently
>> slower - not only compared to v2, but often also to master.
>
> Could this just be down to code alignment changes? There does not
> really seem to be any fundamental differences which would explain
> this.
>
Could be, but then how do we know the speedup with v2 is not due to code
alignment too?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-24 02:47 David Rowley <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: David Rowley @ 2022-11-24 02:47 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Simon Riggs <[email protected]>; Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Thu, 24 Nov 2022 at 08:08, Tomas Vondra
<[email protected]> wrote:
> >> So to me it seems v2 performs demonstrably better, v3 is consistently
> >> slower - not only compared to v2, but often also to master.
> >
> > Could this just be down to code alignment changes? There does not
> > really seem to be any fundamental differences which would explain
> > this.
> >
>
> Could be, but then how do we know the speedup with v2 is not due to code
> alignment too?
It's a good question. Back when I was working on 913ec71d6, I had
similar problems that I saw wildly different performance gains
depending on which commit I patched with. I sorted that out by just
benchmarking on a bunch of different commits both patched and
unpatched.
I've attached a crude bash script which looks at every commit since
1st November 2022 that's changed anything in src/backend/* and runs a
benchmark with and without the v4 patch. That was 76 commits when I
tested. In each instance, with the test I ran, I saw between a 5 and
15% performance improvement with the v4 patch. No commit showed any
performance regression. That makes me fairly happy that there's a
genuine win with this patch.
I've attached the script and the benchmark files along with the
results and a chart.
David
#!/bin/bash
pgsrc=~/pg_src
patch=~/pg_src/hash_inserted_sorted.v4.patch
test_setup=~/setup.sql
test_run_prep=~/prewarm.sql
test_run=~/bench.sql
test_run_clean=~/bench_clean.sql
pg_install=~/pg
pg_data=~/pgdata
dbname=postgres
pgconf=~/postgresql.conf
cd $pgsrc
for sha in $(git log --format="%h" --since=2022-11-01 -- src/backend/*)
do
echo Testing $sha
git checkout $sha 2>&1 | grep -E "^HEAD"
./configure --prefix=$pg_install > /dev/null
make clean -s > /dev/null
make -j -s > /dev/null
make install -s > /dev/null
pg_ctl stop -D $pg_data > /dev/null
rm -rf $pg_data
initdb -D $pg_data > /dev/null 2>&1
cp $pgconf $pg_data
pg_ctl start -D $pg_data -l pg.log > /dev/null
psql -f $test_setup $dbname > /dev/null
psql -f $test_run_prep $dbname > /dev/null
for i in {1..3}
do
pgbench -n -t 1 -f $test_run $dbname | grep -E "^latency"
psql -f $test_run_clean $dbname > /dev/null
done
echo Testing $sha with $patch
patch -p1 < $patch > /dev/null
./configure --prefix=$pg_install > /dev/null
make clean -s > /dev/null
make -j -s > /dev/null
make install -s > /dev/null
pg_ctl stop -D $pg_data > /dev/null
pg_ctl start -D $pg_data -l pg.log > /dev/null
psql -f $test_run_prep $dbname > /dev/null
for i in {1..3}
do
pgbench -n -t 1 -f $test_run $dbname | grep -E "^latency"
psql -f $test_run_clean $dbname > /dev/null
done
patch -p1 -R < $patch > /dev/null
done
Testing 51b5834cd5
HEAD is now at 51b5834cd5 Provide options for postmaster to kill child processes with SIGABRT.
latency average = 2402.343 ms
latency average = 2420.909 ms
latency average = 2420.255 ms
Testing 51b5834cd5 with hash_inserted_sorted.v4.patch
latency average = 2287.420 ms
latency average = 2281.734 ms
latency average = 2270.310 ms
Testing f193883fc9
HEAD is now at f193883fc9 Replace SQLValueFunction by COERCE_SQL_SYNTAX
latency average = 2362.361 ms
latency average = 2393.386 ms
latency average = 2408.171 ms
Testing f193883fc9 with hash_inserted_sorted.v4.patch
latency average = 2265.948 ms
latency average = 2249.244 ms
latency average = 2264.912 ms
Testing 240e0dbacd
HEAD is now at 240e0dbacd Add additional checks while creating the initial decoding snapshot.
latency average = 2406.153 ms
latency average = 2415.519 ms
latency average = 2429.456 ms
Testing 240e0dbacd with hash_inserted_sorted.v4.patch
latency average = 2292.135 ms
latency average = 2273.964 ms
latency average = 2267.536 ms
Testing a4adc31f69
HEAD is now at a4adc31f69 lwlock: Fix quadratic behavior with very long wait lists
latency average = 2418.404 ms
latency average = 2408.542 ms
latency average = 2411.702 ms
Testing a4adc31f69 with hash_inserted_sorted.v4.patch
latency average = 2239.904 ms
latency average = 2224.753 ms
latency average = 2215.073 ms
Testing 061bf98fb8
HEAD is now at 061bf98fb8 pgstat: replace double lookup with IsSharedRelation()
latency average = 2395.514 ms
latency average = 2389.669 ms
latency average = 2415.148 ms
Testing 061bf98fb8 with hash_inserted_sorted.v4.patch
latency average = 2306.043 ms
latency average = 2284.291 ms
latency average = 2293.745 ms
Testing 75b8d3de98
HEAD is now at 75b8d3de98 Fix long-obsolete comment.
latency average = 2406.206 ms
latency average = 2421.647 ms
latency average = 2432.993 ms
Testing 75b8d3de98 with hash_inserted_sorted.v4.patch
latency average = 2282.809 ms
latency average = 2260.056 ms
latency average = 2259.387 ms
Testing fb32748e32
HEAD is now at fb32748e32 Switch SQLValueFunction on "name" to use COERCE_SQL_SYNTAX
latency average = 2398.222 ms
latency average = 2398.158 ms
latency average = 2412.667 ms
Testing fb32748e32 with hash_inserted_sorted.v4.patch
latency average = 2287.401 ms
latency average = 2277.280 ms
latency average = 2273.925 ms
Testing 8c954168cf
HEAD is now at 8c954168cf Fix mislabeling of PROC_QUEUE->links as PGPROC, fixing UBSan on 32bit
latency average = 2402.645 ms
latency average = 2408.780 ms
latency average = 2435.559 ms
Testing 8c954168cf with hash_inserted_sorted.v4.patch
latency average = 2274.398 ms
latency average = 2246.481 ms
latency average = 2258.064 ms
Testing 3d14e171e9
HEAD is now at 3d14e171e9 Add a SET option to the GRANT command.
latency average = 2370.585 ms
latency average = 2385.232 ms
latency average = 2352.468 ms
Testing 3d14e171e9 with hash_inserted_sorted.v4.patch
latency average = 2283.514 ms
latency average = 2255.163 ms
latency average = 2255.692 ms
Testing f84ff0c6d4
HEAD is now at f84ff0c6d4 Don't read MCV stats needlessly in eqjoinsel().
latency average = 2451.621 ms
latency average = 2445.336 ms
latency average = 2441.625 ms
Testing f84ff0c6d4 with hash_inserted_sorted.v4.patch
latency average = 2273.587 ms
latency average = 2281.773 ms
latency average = 2255.076 ms
Testing 1489b1ce72
HEAD is now at 1489b1ce72 Standardize rmgrdesc recovery conflict XID output.
latency average = 2454.695 ms
latency average = 2482.178 ms
latency average = 2455.765 ms
Testing 1489b1ce72 with hash_inserted_sorted.v4.patch
latency average = 2278.288 ms
latency average = 2264.149 ms
latency average = 2286.112 ms
Testing 6ff5aa1299
HEAD is now at 6ff5aa1299 Fix MERGE tuple count with DO NOTHING
latency average = 2438.202 ms
latency average = 2466.679 ms
latency average = 2441.644 ms
Testing 6ff5aa1299 with hash_inserted_sorted.v4.patch
latency average = 2250.029 ms
latency average = 2174.908 ms
latency average = 2176.969 ms
Testing 813492dacc
HEAD is now at 813492dacc Use correct type name in comments about freezing.
latency average = 2422.253 ms
latency average = 2425.635 ms
latency average = 2433.091 ms
Testing 813492dacc with hash_inserted_sorted.v4.patch
latency average = 2259.202 ms
latency average = 2255.037 ms
latency average = 2253.613 ms
Testing 01755490cf
HEAD is now at 01755490cf Fix outdated comment in ExecDelete
latency average = 2441.454 ms
latency average = 2495.011 ms
latency average = 2476.166 ms
Testing 01755490cf with hash_inserted_sorted.v4.patch
latency average = 2276.148 ms
latency average = 2237.806 ms
latency average = 2244.081 ms
Testing af3abca029
HEAD is now at af3abca029 Allow initdb to complete on systems without "locale" command
latency average = 2421.149 ms
latency average = 2409.146 ms
latency average = 2426.692 ms
Testing af3abca029 with hash_inserted_sorted.v4.patch
latency average = 2275.710 ms
latency average = 2261.024 ms
latency average = 2278.414 ms
Testing 3b304547a9
HEAD is now at 3b304547a9 doc: Fix wording of MERGE actions in README
latency average = 2432.049 ms
latency average = 2426.667 ms
latency average = 2425.029 ms
Testing 3b304547a9 with hash_inserted_sorted.v4.patch
latency average = 2228.444 ms
latency average = 2216.235 ms
latency average = 2222.636 ms
Testing aba2dbb3cf
HEAD is now at aba2dbb3cf Fix typos in comments
latency average = 2303.056 ms
latency average = 2407.131 ms
latency average = 2402.108 ms
Testing aba2dbb3cf with hash_inserted_sorted.v4.patch
latency average = 2263.234 ms
latency average = 2235.021 ms
latency average = 2248.850 ms
Testing aca9920409
HEAD is now at aca9920409 Update some more ObjectType switch statements to not have default
latency average = 2424.080 ms
latency average = 2463.660 ms
latency average = 2467.940 ms
Testing aca9920409 with hash_inserted_sorted.v4.patch
latency average = 2268.505 ms
latency average = 2260.860 ms
latency average = 2263.187 ms
Testing adaf34241a
HEAD is now at adaf34241a Improve ruleutils' printout of LATERAL references within subplans.
latency average = 2364.065 ms
latency average = 2427.333 ms
latency average = 2416.276 ms
Testing adaf34241a with hash_inserted_sorted.v4.patch
latency average = 2235.660 ms
latency average = 2170.204 ms
latency average = 2144.862 ms
Testing 5db195f76f
HEAD is now at 5db195f76f Fix slowdown in TAP tests due to recent walreceiver change.
latency average = 2410.043 ms
latency average = 2431.649 ms
latency average = 2437.555 ms
Testing 5db195f76f with hash_inserted_sorted.v4.patch
latency average = 2270.928 ms
latency average = 2250.962 ms
latency average = 2264.647 ms
Testing e9e26b5e71
HEAD is now at e9e26b5e71 Invent "multibitmapsets", and use them to speed up antijoin detection.
latency average = 2408.820 ms
latency average = 2428.216 ms
latency average = 2430.524 ms
Testing e9e26b5e71 with hash_inserted_sorted.v4.patch
latency average = 2287.628 ms
latency average = 2271.201 ms
latency average = 2294.732 ms
Testing 8e1db29cdb
HEAD is now at 8e1db29cdb Variable renaming in preparation for refactoring
latency average = 2517.555 ms
latency average = 2572.147 ms
latency average = 2584.518 ms
Testing 8e1db29cdb with hash_inserted_sorted.v4.patch
latency average = 2296.561 ms
latency average = 2306.250 ms
latency average = 2307.192 ms
Testing d1cb4e9f92
HEAD is now at d1cb4e9f92 Remove useless casts
latency average = 2498.345 ms
latency average = 2551.064 ms
latency average = 2557.233 ms
Testing d1cb4e9f92 with hash_inserted_sorted.v4.patch
latency average = 2318.721 ms
latency average = 2348.590 ms
latency average = 2346.206 ms
Testing 4eb3b11200
HEAD is now at 4eb3b11200 Turn HeapKeyTest macro into inline function
latency average = 2509.932 ms
latency average = 2600.525 ms
latency average = 2602.572 ms
Testing 4eb3b11200 with hash_inserted_sorted.v4.patch
latency average = 2347.831 ms
latency average = 2334.950 ms
latency average = 2325.223 ms
Testing c0f1e51ac7
HEAD is now at c0f1e51ac7 Remove unused include
latency average = 2526.558 ms
latency average = 2563.099 ms
latency average = 2603.873 ms
Testing c0f1e51ac7 with hash_inserted_sorted.v4.patch
latency average = 2329.869 ms
latency average = 2321.878 ms
latency average = 2323.433 ms
Testing 63c833f4bd
HEAD is now at 63c833f4bd Use multi-inserts for pg_ts_config_map
latency average = 2541.776 ms
latency average = 2543.263 ms
latency average = 2606.162 ms
Testing 63c833f4bd with hash_inserted_sorted.v4.patch
latency average = 2299.880 ms
latency average = 2318.037 ms
latency average = 2361.812 ms
Testing 1ff4161218
HEAD is now at 1ff4161218 Use multi-inserts for pg_enum
latency average = 2534.148 ms
latency average = 2583.887 ms
latency average = 2580.256 ms
Testing 1ff4161218 with hash_inserted_sorted.v4.patch
latency average = 2305.907 ms
latency average = 2308.932 ms
latency average = 2335.532 ms
Testing 09a72188cd
HEAD is now at 09a72188cd Avoid some overhead with open and close of catalog indexes
latency average = 2590.324 ms
latency average = 2610.252 ms
latency average = 2647.632 ms
Testing 09a72188cd with hash_inserted_sorted.v4.patch
latency average = 2288.947 ms
latency average = 2278.704 ms
latency average = 2288.870 ms
Testing 1eda3ce802
HEAD is now at 1eda3ce802 Mark argument of RegisterCustomRmgr() as const.
latency average = 2525.532 ms
latency average = 2557.133 ms
latency average = 2575.577 ms
Testing 1eda3ce802 with hash_inserted_sorted.v4.patch
latency average = 2317.446 ms
latency average = 2307.123 ms
latency average = 2314.614 ms
Testing 9e5405993c
HEAD is now at 9e5405993c Deduplicate freeze plans in freeze WAL records.
latency average = 2513.333 ms
latency average = 2524.062 ms
latency average = 2559.457 ms
Testing 9e5405993c with hash_inserted_sorted.v4.patch
latency average = 2284.289 ms
latency average = 2308.022 ms
latency average = 2312.370 ms
Testing 2fe3bdbd69
HEAD is now at 2fe3bdbd69 Check return value of pclose() correctly
latency average = 2630.249 ms
latency average = 2543.159 ms
latency average = 2606.355 ms
Testing 2fe3bdbd69 with hash_inserted_sorted.v4.patch
latency average = 2297.949 ms
latency average = 2264.590 ms
latency average = 2222.208 ms
Testing d627ce3b70
HEAD is now at d627ce3b70 Disallow setting archive_library and archive_command at the same time
latency average = 2605.760 ms
latency average = 2529.993 ms
latency average = 2584.864 ms
Testing d627ce3b70 with hash_inserted_sorted.v4.patch
latency average = 2311.481 ms
latency average = 2351.165 ms
latency average = 2345.112 ms
Testing 8b5262fa0e
HEAD is now at 8b5262fa0e Improve comments referring snapshot's subxip array.
latency average = 2531.827 ms
latency average = 2503.293 ms
latency average = 2614.431 ms
Testing 8b5262fa0e with hash_inserted_sorted.v4.patch
latency average = 2305.385 ms
latency average = 2330.122 ms
latency average = 2275.587 ms
Testing e848be60b5
HEAD is now at e848be60b5 Fix cleanup lock acquisition in SPLIT_ALLOCATE_PAGE replay.
latency average = 2563.815 ms
latency average = 2464.968 ms
latency average = 2569.916 ms
Testing e848be60b5 with hash_inserted_sorted.v4.patch
latency average = 2319.475 ms
latency average = 2323.132 ms
latency average = 2305.990 ms
Testing ad6c52846f
HEAD is now at ad6c52846f Add error context callback when tokenizing authentication files
latency average = 2568.077 ms
latency average = 2467.833 ms
latency average = 2537.239 ms
Testing ad6c52846f with hash_inserted_sorted.v4.patch
latency average = 2317.818 ms
latency average = 2330.041 ms
latency average = 2322.055 ms
Testing 783e8c69cb
HEAD is now at 783e8c69cb Invent open_auth_file() in hba.c to refactor authentication file opening
latency average = 2574.041 ms
latency average = 2650.330 ms
latency average = 2629.292 ms
Testing 783e8c69cb with hash_inserted_sorted.v4.patch
latency average = 2333.128 ms
latency average = 2364.182 ms
latency average = 2317.255 ms
Testing 5e1f3b9ebf
HEAD is now at 5e1f3b9ebf Make Bitmapsets be valid Nodes.
latency average = 2566.785 ms
latency average = 2460.722 ms
latency average = 2500.946 ms
Testing 5e1f3b9ebf with hash_inserted_sorted.v4.patch
latency average = 2304.605 ms
latency average = 2239.050 ms
latency average = 2209.607 ms
Testing c727f511bd
HEAD is now at c727f511bd Refactor aclcheck functions
latency average = 2612.968 ms
latency average = 2502.632 ms
latency average = 2480.754 ms
Testing c727f511bd with hash_inserted_sorted.v4.patch
latency average = 2310.023 ms
latency average = 2286.710 ms
latency average = 2315.241 ms
Testing afbfc02983
HEAD is now at afbfc02983 Refactor ownercheck functions
latency average = 2607.636 ms
latency average = 2509.407 ms
latency average = 2551.236 ms
Testing afbfc02983 with hash_inserted_sorted.v4.patch
latency average = 2354.992 ms
latency average = 2349.007 ms
latency average = 2371.417 ms
Testing b4b7ce8061
HEAD is now at b4b7ce8061 Add repalloc0 and repalloc0_array
latency average = 2698.148 ms
latency average = 2604.179 ms
latency average = 2608.903 ms
Testing b4b7ce8061 with hash_inserted_sorted.v4.patch
latency average = 2366.051 ms
latency average = 2329.557 ms
latency average = 2383.185 ms
Testing 97c61f70d1
HEAD is now at 97c61f70d1 Document WAL rules related to PD_ALL_VISIBLE in README.
latency average = 2569.559 ms
latency average = 2493.218 ms
latency average = 2595.524 ms
Testing 97c61f70d1 with hash_inserted_sorted.v4.patch
latency average = 2333.463 ms
latency average = 2305.564 ms
latency average = 2323.668 ms
Testing d6a3dbe14f
HEAD is now at d6a3dbe14f Fix theoretical torn page hazard.
latency average = 2576.712 ms
latency average = 2537.394 ms
latency average = 2617.201 ms
Testing d6a3dbe14f with hash_inserted_sorted.v4.patch
latency average = 2341.578 ms
latency average = 2333.859 ms
latency average = 2314.373 ms
Testing 3eb8eeccbe
HEAD is now at 3eb8eeccbe Remove obsolete comments and code from prior to f8f4227976.
latency average = 2637.642 ms
latency average = 2512.848 ms
latency average = 2584.967 ms
Testing 3eb8eeccbe with hash_inserted_sorted.v4.patch
latency average = 2372.732 ms
latency average = 2340.511 ms
latency average = 2365.156 ms
Testing b9424d014e
HEAD is now at b9424d014e Support writing "CREATE/ALTER TABLE ... SET STORAGE DEFAULT".
latency average = 2556.584 ms
latency average = 2525.312 ms
latency average = 2525.383 ms
Testing b9424d014e with hash_inserted_sorted.v4.patch
latency average = 2319.155 ms
latency average = 2309.208 ms
latency average = 2323.705 ms
Testing 36e545cd05
HEAD is now at 36e545cd05 Fix comments atop ReorderBufferAddInvalidations.
latency average = 2567.350 ms
latency average = 2668.871 ms
latency average = 2593.162 ms
Testing 36e545cd05 with hash_inserted_sorted.v4.patch
latency average = 2375.806 ms
latency average = 2331.210 ms
latency average = 2348.319 ms
Testing 5ca3645cb3
HEAD is now at 5ca3645cb3 Fix comment of SimpleLruInit() in slru.c
latency average = 2615.466 ms
latency average = 2489.282 ms
latency average = 2610.227 ms
Testing 5ca3645cb3 with hash_inserted_sorted.v4.patch
latency average = 2328.906 ms
latency average = 2309.380 ms
latency average = 2330.482 ms
Testing 85d8b30724
HEAD is now at 85d8b30724 Apply a better fix to mdunlinkfork().
latency average = 2558.060 ms
latency average = 2547.623 ms
latency average = 2655.184 ms
Testing 85d8b30724 with hash_inserted_sorted.v4.patch
latency average = 2338.695 ms
latency average = 2298.726 ms
latency average = 2310.082 ms
Testing 4f981df8e0
HEAD is now at 4f981df8e0 Report a more useful error for reloptions on a partitioned table.
latency average = 2542.523 ms
latency average = 2575.723 ms
latency average = 2511.547 ms
Testing 4f981df8e0 with hash_inserted_sorted.v4.patch
latency average = 2326.880 ms
latency average = 2315.611 ms
latency average = 2330.598 ms
Testing e613ace1f0
HEAD is now at e613ace1f0 Doc: add comments about PreventInTransactionBlock/IsInTransactionBlock.
latency average = 2548.314 ms
latency average = 2520.307 ms
latency average = 2602.306 ms
Testing e613ace1f0 with hash_inserted_sorted.v4.patch
latency average = 2319.946 ms
latency average = 2306.619 ms
latency average = 2326.536 ms
Testing b28ac1d24d
HEAD is now at b28ac1d24d Provide sigaction() for Windows.
latency average = 2603.864 ms
latency average = 2638.927 ms
latency average = 2623.397 ms
Testing b28ac1d24d with hash_inserted_sorted.v4.patch
latency average = 2333.284 ms
latency average = 2334.505 ms
latency average = 2335.565 ms
Testing 6bbd8b7385
HEAD is now at 6bbd8b7385 Use AbsoluteConfigLocation() when building an included path in hba.c
latency average = 2566.154 ms
latency average = 2582.984 ms
latency average = 2508.615 ms
Testing 6bbd8b7385 with hash_inserted_sorted.v4.patch
latency average = 2315.752 ms
latency average = 2304.409 ms
latency average = 2312.609 ms
Testing b5621b66e7
HEAD is now at b5621b66e7 Unify some internal error message wordings
latency average = 2575.142 ms
latency average = 2531.990 ms
latency average = 2642.011 ms
Testing b5621b66e7 with hash_inserted_sorted.v4.patch
latency average = 2344.448 ms
latency average = 2314.287 ms
latency average = 2317.520 ms
Testing 042c9091f0
HEAD is now at 042c9091f0 Produce more-optimal plans for bitmap scans on boolean columns.
latency average = 2564.736 ms
latency average = 2584.385 ms
latency average = 2586.905 ms
Testing 042c9091f0 with hash_inserted_sorted.v4.patch
latency average = 2314.212 ms
latency average = 2306.888 ms
latency average = 2304.622 ms
Testing 05a7be9355
HEAD is now at 05a7be9355 Suppress useless wakeups in walreceiver.
latency average = 2557.643 ms
latency average = 2558.682 ms
latency average = 2625.695 ms
Testing 05a7be9355 with hash_inserted_sorted.v4.patch
latency average = 2323.089 ms
latency average = 2310.719 ms
latency average = 2312.283 ms
Testing 3bdbdf5d06
HEAD is now at 3bdbdf5d06 Introduce pg_pwrite_zeros() in fileutils.c
latency average = 2581.453 ms
latency average = 2557.655 ms
latency average = 2497.719 ms
Testing 3bdbdf5d06 with hash_inserted_sorted.v4.patch
latency average = 2325.895 ms
latency average = 2303.553 ms
latency average = 2333.526 ms
Testing d7744d50a5
HEAD is now at d7744d50a5 Fix initialization of pg_stat_get_lastscan()
latency average = 2587.556 ms
latency average = 2499.254 ms
latency average = 2590.929 ms
Testing d7744d50a5 with hash_inserted_sorted.v4.patch
latency average = 2349.141 ms
latency average = 2314.493 ms
latency average = 2358.917 ms
Testing 1613de8bc3
HEAD is now at 1613de8bc3 Fix compiler warning on MSVC
latency average = 2565.962 ms
latency average = 2490.490 ms
latency average = 2623.311 ms
Testing 1613de8bc3 with hash_inserted_sorted.v4.patch
latency average = 2346.765 ms
latency average = 2346.219 ms
latency average = 2469.499 ms
Testing 0e758ae89a
HEAD is now at 0e758ae89a Fix failure to remove non-first segments of temporary tables.
latency average = 2554.269 ms
latency average = 2478.089 ms
latency average = 2501.562 ms
Testing 0e758ae89a with hash_inserted_sorted.v4.patch
latency average = 2351.535 ms
latency average = 2358.191 ms
latency average = 2342.335 ms
Testing a1a7bb8f16
HEAD is now at a1a7bb8f16 Move code related to configuration files in directories to new file
latency average = 2572.732 ms
latency average = 2549.068 ms
latency average = 2629.398 ms
Testing a1a7bb8f16 with hash_inserted_sorted.v4.patch
latency average = 2303.673 ms
latency average = 2244.184 ms
latency average = 2224.508 ms
Testing b0b72c64a0
HEAD is now at b0b72c64a0 Don't pass down nonnullable_vars while reducing outer joins.
latency average = 2600.178 ms
latency average = 2649.661 ms
latency average = 2592.777 ms
Testing b0b72c64a0 with hash_inserted_sorted.v4.patch
latency average = 2361.784 ms
latency average = 2314.966 ms
latency average = 2351.954 ms
Testing ff8fa0bf7e
HEAD is now at ff8fa0bf7e Handle SubPlan cases in find_nonnullable_rels/vars.
latency average = 2619.688 ms
latency average = 2479.717 ms
latency average = 2563.754 ms
Testing ff8fa0bf7e with hash_inserted_sorted.v4.patch
latency average = 2372.355 ms
latency average = 2334.374 ms
latency average = 2319.480 ms
Testing c3652cd84a
HEAD is now at c3652cd84a Remove redundant breaks in HeapTupleSatisfiesVisibility
latency average = 2595.598 ms
latency average = 2630.692 ms
latency average = 2601.062 ms
Testing c3652cd84a with hash_inserted_sorted.v4.patch
latency average = 2391.140 ms
latency average = 2337.106 ms
latency average = 2342.686 ms
Testing 2a71de8915
HEAD is now at 2a71de8915 Remove unneeded includes of <sys/stat.h>
latency average = 2655.597 ms
latency average = 2478.325 ms
latency average = 2552.646 ms
Testing 2a71de8915 with hash_inserted_sorted.v4.patch
latency average = 2380.744 ms
latency average = 2365.029 ms
latency average = 2363.443 ms
Testing 34fa0ddae5
HEAD is now at 34fa0ddae5 Fix CREATE DATABASE so we can pg_upgrade DBs with OIDs above 2^31.
latency average = 2586.739 ms
latency average = 2442.470 ms
latency average = 2508.969 ms
Testing 34fa0ddae5 with hash_inserted_sorted.v4.patch
latency average = 2372.138 ms
latency average = 2278.061 ms
latency average = 2288.480 ms
Testing 8c71467908
HEAD is now at 8c71467908 Correct error message for row-level triggers with transition tables on partitioned tables.
latency average = 2575.921 ms
latency average = 2565.585 ms
latency average = 2649.470 ms
Testing 8c71467908 with hash_inserted_sorted.v4.patch
latency average = 2353.758 ms
latency average = 2352.173 ms
latency average = 2360.371 ms
Testing 233cf6e8ad
HEAD is now at 233cf6e8ad Remove outdated include
latency average = 2602.480 ms
latency average = 2651.066 ms
latency average = 2608.862 ms
Testing 233cf6e8ad with hash_inserted_sorted.v4.patch
latency average = 2354.203 ms
latency average = 2335.024 ms
latency average = 2335.930 ms
Testing b0284bfb1d
HEAD is now at b0284bfb1d Create FKs properly when attaching table as partition
latency average = 2610.374 ms
latency average = 2532.709 ms
latency average = 2616.912 ms
Testing b0284bfb1d with hash_inserted_sorted.v4.patch
latency average = 2357.815 ms
latency average = 2299.897 ms
latency average = 2288.117 ms
Testing dea8349380
HEAD is now at dea8349380 Avoid crash after function syntax error in a replication worker.
latency average = 2571.665 ms
latency average = 2520.032 ms
latency average = 2490.022 ms
Testing dea8349380 with hash_inserted_sorted.v4.patch
latency average = 2341.478 ms
latency average = 2339.011 ms
latency average = 2306.446 ms
Testing 5fca91025e
HEAD is now at 5fca91025e Resolve partition strategy during early parsing
latency average = 2594.012 ms
latency average = 2537.600 ms
latency average = 2641.264 ms
Testing 5fca91025e with hash_inserted_sorted.v4.patch
latency average = 2306.030 ms
latency average = 2284.057 ms
latency average = 2269.448 ms
Testing 062eef3a9b
HEAD is now at 062eef3a9b Straighten include order in guc-file.l
latency average = 2617.288 ms
latency average = 2483.089 ms
latency average = 2608.614 ms
Testing 062eef3a9b with hash_inserted_sorted.v4.patch
latency average = 2323.138 ms
latency average = 2303.455 ms
latency average = 2328.547 ms
Testing be541efbfd
HEAD is now at be541efbfd Defend against unsupported partition relkind in logical replication worker.
latency average = 2613.276 ms
latency average = 2534.782 ms
latency average = 2679.991 ms
Testing be541efbfd with hash_inserted_sorted.v4.patch
latency average = 2342.325 ms
latency average = 2315.661 ms
latency average = 2375.281 ms
Testing d54e79ba28
HEAD is now at d54e79ba28 Fix copy-and-pasteo in comment.
latency average = 2652.618 ms
latency average = 2507.253 ms
latency average = 2569.875 ms
Testing d54e79ba28 with hash_inserted_sorted.v4.patch
latency average = 2328.429 ms
latency average = 2212.987 ms
latency average = 2213.743 ms
Testing 568546f7e4
HEAD is now at 568546f7e4 Improve the description of XLOG_RUNNING_XACTS.
latency average = 2614.805 ms
latency average = 2503.605 ms
latency average = 2552.067 ms
Testing 568546f7e4 with hash_inserted_sorted.v4.patch
latency average = 2335.791 ms
latency average = 2333.319 ms
latency average = 2358.751 ms
Testing 8e621c10c7
HEAD is now at 8e621c10c7 Remove code handling FORCE_NULL and FORCE_NOT_NULL for COPY TO
latency average = 2663.667 ms
latency average = 2489.555 ms
latency average = 2555.109 ms
Testing 8e621c10c7 with hash_inserted_sorted.v4.patch
latency average = 2348.930 ms
latency average = 2367.419 ms
latency average = 2334.123 ms
Testing 7c335b7a20
HEAD is now at 7c335b7a20 Add doubly linked count list implementation
latency average = 2615.585 ms
latency average = 2457.761 ms
latency average = 2498.906 ms
Testing 7c335b7a20 with hash_inserted_sorted.v4.patch
latency average = 2362.836 ms
latency average = 2351.036 ms
latency average = 2305.201 ms
Testing f4857082bc
HEAD is now at f4857082bc Fix planner failure with extended statistics on partitioned tables.
latency average = 2600.016 ms
latency average = 2675.312 ms
latency average = 2633.423 ms
Testing f4857082bc with hash_inserted_sorted.v4.patch
latency average = 2399.183 ms
latency average = 2367.420 ms
latency average = 2367.234 ms
Attachments:
[text/plain] bench_commits.sh.txt (1.4K, ../../CAApHDvpyoGwu3-TuzK-T4SVMxsZSD4t+mun63jnj5vQksNq06g@mail.gmail.com/2-bench_commits.sh.txt)
download | inline:
#!/bin/bash
pgsrc=~/pg_src
patch=~/pg_src/hash_inserted_sorted.v4.patch
test_setup=~/setup.sql
test_run_prep=~/prewarm.sql
test_run=~/bench.sql
test_run_clean=~/bench_clean.sql
pg_install=~/pg
pg_data=~/pgdata
dbname=postgres
pgconf=~/postgresql.conf
cd $pgsrc
for sha in $(git log --format="%h" --since=2022-11-01 -- src/backend/*)
do
echo Testing $sha
git checkout $sha 2>&1 | grep -E "^HEAD"
./configure --prefix=$pg_install > /dev/null
make clean -s > /dev/null
make -j -s > /dev/null
make install -s > /dev/null
pg_ctl stop -D $pg_data > /dev/null
rm -rf $pg_data
initdb -D $pg_data > /dev/null 2>&1
cp $pgconf $pg_data
pg_ctl start -D $pg_data -l pg.log > /dev/null
psql -f $test_setup $dbname > /dev/null
psql -f $test_run_prep $dbname > /dev/null
for i in {1..3}
do
pgbench -n -t 1 -f $test_run $dbname | grep -E "^latency"
psql -f $test_run_clean $dbname > /dev/null
done
echo Testing $sha with $patch
patch -p1 < $patch > /dev/null
./configure --prefix=$pg_install > /dev/null
make clean -s > /dev/null
make -j -s > /dev/null
make install -s > /dev/null
pg_ctl stop -D $pg_data > /dev/null
pg_ctl start -D $pg_data -l pg.log > /dev/null
psql -f $test_run_prep $dbname > /dev/null
for i in {1..3}
do
pgbench -n -t 1 -f $test_run $dbname | grep -E "^latency"
psql -f $test_run_clean $dbname > /dev/null
done
patch -p1 -R < $patch > /dev/null
done
[application/octet-stream] bench.sql (49B, ../../CAApHDvpyoGwu3-TuzK-T4SVMxsZSD4t+mun63jnj5vQksNq06g@mail.gmail.com/3-bench.sql)
download
[application/octet-stream] bench_clean.sql (26B, ../../CAApHDvpyoGwu3-TuzK-T4SVMxsZSD4t+mun63jnj5vQksNq06g@mail.gmail.com/4-bench_clean.sql)
download
[application/octet-stream] prewarm.sql (25B, ../../CAApHDvpyoGwu3-TuzK-T4SVMxsZSD4t+mun63jnj5vQksNq06g@mail.gmail.com/5-prewarm.sql)
download
[text/plain] output.txt (24.5K, ../../CAApHDvpyoGwu3-TuzK-T4SVMxsZSD4t+mun63jnj5vQksNq06g@mail.gmail.com/6-output.txt)
download | inline:
Testing 51b5834cd5
HEAD is now at 51b5834cd5 Provide options for postmaster to kill child processes with SIGABRT.
latency average = 2402.343 ms
latency average = 2420.909 ms
latency average = 2420.255 ms
Testing 51b5834cd5 with hash_inserted_sorted.v4.patch
latency average = 2287.420 ms
latency average = 2281.734 ms
latency average = 2270.310 ms
Testing f193883fc9
HEAD is now at f193883fc9 Replace SQLValueFunction by COERCE_SQL_SYNTAX
latency average = 2362.361 ms
latency average = 2393.386 ms
latency average = 2408.171 ms
Testing f193883fc9 with hash_inserted_sorted.v4.patch
latency average = 2265.948 ms
latency average = 2249.244 ms
latency average = 2264.912 ms
Testing 240e0dbacd
HEAD is now at 240e0dbacd Add additional checks while creating the initial decoding snapshot.
latency average = 2406.153 ms
latency average = 2415.519 ms
latency average = 2429.456 ms
Testing 240e0dbacd with hash_inserted_sorted.v4.patch
latency average = 2292.135 ms
latency average = 2273.964 ms
latency average = 2267.536 ms
Testing a4adc31f69
HEAD is now at a4adc31f69 lwlock: Fix quadratic behavior with very long wait lists
latency average = 2418.404 ms
latency average = 2408.542 ms
latency average = 2411.702 ms
Testing a4adc31f69 with hash_inserted_sorted.v4.patch
latency average = 2239.904 ms
latency average = 2224.753 ms
latency average = 2215.073 ms
Testing 061bf98fb8
HEAD is now at 061bf98fb8 pgstat: replace double lookup with IsSharedRelation()
latency average = 2395.514 ms
latency average = 2389.669 ms
latency average = 2415.148 ms
Testing 061bf98fb8 with hash_inserted_sorted.v4.patch
latency average = 2306.043 ms
latency average = 2284.291 ms
latency average = 2293.745 ms
Testing 75b8d3de98
HEAD is now at 75b8d3de98 Fix long-obsolete comment.
latency average = 2406.206 ms
latency average = 2421.647 ms
latency average = 2432.993 ms
Testing 75b8d3de98 with hash_inserted_sorted.v4.patch
latency average = 2282.809 ms
latency average = 2260.056 ms
latency average = 2259.387 ms
Testing fb32748e32
HEAD is now at fb32748e32 Switch SQLValueFunction on "name" to use COERCE_SQL_SYNTAX
latency average = 2398.222 ms
latency average = 2398.158 ms
latency average = 2412.667 ms
Testing fb32748e32 with hash_inserted_sorted.v4.patch
latency average = 2287.401 ms
latency average = 2277.280 ms
latency average = 2273.925 ms
Testing 8c954168cf
HEAD is now at 8c954168cf Fix mislabeling of PROC_QUEUE->links as PGPROC, fixing UBSan on 32bit
latency average = 2402.645 ms
latency average = 2408.780 ms
latency average = 2435.559 ms
Testing 8c954168cf with hash_inserted_sorted.v4.patch
latency average = 2274.398 ms
latency average = 2246.481 ms
latency average = 2258.064 ms
Testing 3d14e171e9
HEAD is now at 3d14e171e9 Add a SET option to the GRANT command.
latency average = 2370.585 ms
latency average = 2385.232 ms
latency average = 2352.468 ms
Testing 3d14e171e9 with hash_inserted_sorted.v4.patch
latency average = 2283.514 ms
latency average = 2255.163 ms
latency average = 2255.692 ms
Testing f84ff0c6d4
HEAD is now at f84ff0c6d4 Don't read MCV stats needlessly in eqjoinsel().
latency average = 2451.621 ms
latency average = 2445.336 ms
latency average = 2441.625 ms
Testing f84ff0c6d4 with hash_inserted_sorted.v4.patch
latency average = 2273.587 ms
latency average = 2281.773 ms
latency average = 2255.076 ms
Testing 1489b1ce72
HEAD is now at 1489b1ce72 Standardize rmgrdesc recovery conflict XID output.
latency average = 2454.695 ms
latency average = 2482.178 ms
latency average = 2455.765 ms
Testing 1489b1ce72 with hash_inserted_sorted.v4.patch
latency average = 2278.288 ms
latency average = 2264.149 ms
latency average = 2286.112 ms
Testing 6ff5aa1299
HEAD is now at 6ff5aa1299 Fix MERGE tuple count with DO NOTHING
latency average = 2438.202 ms
latency average = 2466.679 ms
latency average = 2441.644 ms
Testing 6ff5aa1299 with hash_inserted_sorted.v4.patch
latency average = 2250.029 ms
latency average = 2174.908 ms
latency average = 2176.969 ms
Testing 813492dacc
HEAD is now at 813492dacc Use correct type name in comments about freezing.
latency average = 2422.253 ms
latency average = 2425.635 ms
latency average = 2433.091 ms
Testing 813492dacc with hash_inserted_sorted.v4.patch
latency average = 2259.202 ms
latency average = 2255.037 ms
latency average = 2253.613 ms
Testing 01755490cf
HEAD is now at 01755490cf Fix outdated comment in ExecDelete
latency average = 2441.454 ms
latency average = 2495.011 ms
latency average = 2476.166 ms
Testing 01755490cf with hash_inserted_sorted.v4.patch
latency average = 2276.148 ms
latency average = 2237.806 ms
latency average = 2244.081 ms
Testing af3abca029
HEAD is now at af3abca029 Allow initdb to complete on systems without "locale" command
latency average = 2421.149 ms
latency average = 2409.146 ms
latency average = 2426.692 ms
Testing af3abca029 with hash_inserted_sorted.v4.patch
latency average = 2275.710 ms
latency average = 2261.024 ms
latency average = 2278.414 ms
Testing 3b304547a9
HEAD is now at 3b304547a9 doc: Fix wording of MERGE actions in README
latency average = 2432.049 ms
latency average = 2426.667 ms
latency average = 2425.029 ms
Testing 3b304547a9 with hash_inserted_sorted.v4.patch
latency average = 2228.444 ms
latency average = 2216.235 ms
latency average = 2222.636 ms
Testing aba2dbb3cf
HEAD is now at aba2dbb3cf Fix typos in comments
latency average = 2303.056 ms
latency average = 2407.131 ms
latency average = 2402.108 ms
Testing aba2dbb3cf with hash_inserted_sorted.v4.patch
latency average = 2263.234 ms
latency average = 2235.021 ms
latency average = 2248.850 ms
Testing aca9920409
HEAD is now at aca9920409 Update some more ObjectType switch statements to not have default
latency average = 2424.080 ms
latency average = 2463.660 ms
latency average = 2467.940 ms
Testing aca9920409 with hash_inserted_sorted.v4.patch
latency average = 2268.505 ms
latency average = 2260.860 ms
latency average = 2263.187 ms
Testing adaf34241a
HEAD is now at adaf34241a Improve ruleutils' printout of LATERAL references within subplans.
latency average = 2364.065 ms
latency average = 2427.333 ms
latency average = 2416.276 ms
Testing adaf34241a with hash_inserted_sorted.v4.patch
latency average = 2235.660 ms
latency average = 2170.204 ms
latency average = 2144.862 ms
Testing 5db195f76f
HEAD is now at 5db195f76f Fix slowdown in TAP tests due to recent walreceiver change.
latency average = 2410.043 ms
latency average = 2431.649 ms
latency average = 2437.555 ms
Testing 5db195f76f with hash_inserted_sorted.v4.patch
latency average = 2270.928 ms
latency average = 2250.962 ms
latency average = 2264.647 ms
Testing e9e26b5e71
HEAD is now at e9e26b5e71 Invent "multibitmapsets", and use them to speed up antijoin detection.
latency average = 2408.820 ms
latency average = 2428.216 ms
latency average = 2430.524 ms
Testing e9e26b5e71 with hash_inserted_sorted.v4.patch
latency average = 2287.628 ms
latency average = 2271.201 ms
latency average = 2294.732 ms
Testing 8e1db29cdb
HEAD is now at 8e1db29cdb Variable renaming in preparation for refactoring
latency average = 2517.555 ms
latency average = 2572.147 ms
latency average = 2584.518 ms
Testing 8e1db29cdb with hash_inserted_sorted.v4.patch
latency average = 2296.561 ms
latency average = 2306.250 ms
latency average = 2307.192 ms
Testing d1cb4e9f92
HEAD is now at d1cb4e9f92 Remove useless casts
latency average = 2498.345 ms
latency average = 2551.064 ms
latency average = 2557.233 ms
Testing d1cb4e9f92 with hash_inserted_sorted.v4.patch
latency average = 2318.721 ms
latency average = 2348.590 ms
latency average = 2346.206 ms
Testing 4eb3b11200
HEAD is now at 4eb3b11200 Turn HeapKeyTest macro into inline function
latency average = 2509.932 ms
latency average = 2600.525 ms
latency average = 2602.572 ms
Testing 4eb3b11200 with hash_inserted_sorted.v4.patch
latency average = 2347.831 ms
latency average = 2334.950 ms
latency average = 2325.223 ms
Testing c0f1e51ac7
HEAD is now at c0f1e51ac7 Remove unused include
latency average = 2526.558 ms
latency average = 2563.099 ms
latency average = 2603.873 ms
Testing c0f1e51ac7 with hash_inserted_sorted.v4.patch
latency average = 2329.869 ms
latency average = 2321.878 ms
latency average = 2323.433 ms
Testing 63c833f4bd
HEAD is now at 63c833f4bd Use multi-inserts for pg_ts_config_map
latency average = 2541.776 ms
latency average = 2543.263 ms
latency average = 2606.162 ms
Testing 63c833f4bd with hash_inserted_sorted.v4.patch
latency average = 2299.880 ms
latency average = 2318.037 ms
latency average = 2361.812 ms
Testing 1ff4161218
HEAD is now at 1ff4161218 Use multi-inserts for pg_enum
latency average = 2534.148 ms
latency average = 2583.887 ms
latency average = 2580.256 ms
Testing 1ff4161218 with hash_inserted_sorted.v4.patch
latency average = 2305.907 ms
latency average = 2308.932 ms
latency average = 2335.532 ms
Testing 09a72188cd
HEAD is now at 09a72188cd Avoid some overhead with open and close of catalog indexes
latency average = 2590.324 ms
latency average = 2610.252 ms
latency average = 2647.632 ms
Testing 09a72188cd with hash_inserted_sorted.v4.patch
latency average = 2288.947 ms
latency average = 2278.704 ms
latency average = 2288.870 ms
Testing 1eda3ce802
HEAD is now at 1eda3ce802 Mark argument of RegisterCustomRmgr() as const.
latency average = 2525.532 ms
latency average = 2557.133 ms
latency average = 2575.577 ms
Testing 1eda3ce802 with hash_inserted_sorted.v4.patch
latency average = 2317.446 ms
latency average = 2307.123 ms
latency average = 2314.614 ms
Testing 9e5405993c
HEAD is now at 9e5405993c Deduplicate freeze plans in freeze WAL records.
latency average = 2513.333 ms
latency average = 2524.062 ms
latency average = 2559.457 ms
Testing 9e5405993c with hash_inserted_sorted.v4.patch
latency average = 2284.289 ms
latency average = 2308.022 ms
latency average = 2312.370 ms
Testing 2fe3bdbd69
HEAD is now at 2fe3bdbd69 Check return value of pclose() correctly
latency average = 2630.249 ms
latency average = 2543.159 ms
latency average = 2606.355 ms
Testing 2fe3bdbd69 with hash_inserted_sorted.v4.patch
latency average = 2297.949 ms
latency average = 2264.590 ms
latency average = 2222.208 ms
Testing d627ce3b70
HEAD is now at d627ce3b70 Disallow setting archive_library and archive_command at the same time
latency average = 2605.760 ms
latency average = 2529.993 ms
latency average = 2584.864 ms
Testing d627ce3b70 with hash_inserted_sorted.v4.patch
latency average = 2311.481 ms
latency average = 2351.165 ms
latency average = 2345.112 ms
Testing 8b5262fa0e
HEAD is now at 8b5262fa0e Improve comments referring snapshot's subxip array.
latency average = 2531.827 ms
latency average = 2503.293 ms
latency average = 2614.431 ms
Testing 8b5262fa0e with hash_inserted_sorted.v4.patch
latency average = 2305.385 ms
latency average = 2330.122 ms
latency average = 2275.587 ms
Testing e848be60b5
HEAD is now at e848be60b5 Fix cleanup lock acquisition in SPLIT_ALLOCATE_PAGE replay.
latency average = 2563.815 ms
latency average = 2464.968 ms
latency average = 2569.916 ms
Testing e848be60b5 with hash_inserted_sorted.v4.patch
latency average = 2319.475 ms
latency average = 2323.132 ms
latency average = 2305.990 ms
Testing ad6c52846f
HEAD is now at ad6c52846f Add error context callback when tokenizing authentication files
latency average = 2568.077 ms
latency average = 2467.833 ms
latency average = 2537.239 ms
Testing ad6c52846f with hash_inserted_sorted.v4.patch
latency average = 2317.818 ms
latency average = 2330.041 ms
latency average = 2322.055 ms
Testing 783e8c69cb
HEAD is now at 783e8c69cb Invent open_auth_file() in hba.c to refactor authentication file opening
latency average = 2574.041 ms
latency average = 2650.330 ms
latency average = 2629.292 ms
Testing 783e8c69cb with hash_inserted_sorted.v4.patch
latency average = 2333.128 ms
latency average = 2364.182 ms
latency average = 2317.255 ms
Testing 5e1f3b9ebf
HEAD is now at 5e1f3b9ebf Make Bitmapsets be valid Nodes.
latency average = 2566.785 ms
latency average = 2460.722 ms
latency average = 2500.946 ms
Testing 5e1f3b9ebf with hash_inserted_sorted.v4.patch
latency average = 2304.605 ms
latency average = 2239.050 ms
latency average = 2209.607 ms
Testing c727f511bd
HEAD is now at c727f511bd Refactor aclcheck functions
latency average = 2612.968 ms
latency average = 2502.632 ms
latency average = 2480.754 ms
Testing c727f511bd with hash_inserted_sorted.v4.patch
latency average = 2310.023 ms
latency average = 2286.710 ms
latency average = 2315.241 ms
Testing afbfc02983
HEAD is now at afbfc02983 Refactor ownercheck functions
latency average = 2607.636 ms
latency average = 2509.407 ms
latency average = 2551.236 ms
Testing afbfc02983 with hash_inserted_sorted.v4.patch
latency average = 2354.992 ms
latency average = 2349.007 ms
latency average = 2371.417 ms
Testing b4b7ce8061
HEAD is now at b4b7ce8061 Add repalloc0 and repalloc0_array
latency average = 2698.148 ms
latency average = 2604.179 ms
latency average = 2608.903 ms
Testing b4b7ce8061 with hash_inserted_sorted.v4.patch
latency average = 2366.051 ms
latency average = 2329.557 ms
latency average = 2383.185 ms
Testing 97c61f70d1
HEAD is now at 97c61f70d1 Document WAL rules related to PD_ALL_VISIBLE in README.
latency average = 2569.559 ms
latency average = 2493.218 ms
latency average = 2595.524 ms
Testing 97c61f70d1 with hash_inserted_sorted.v4.patch
latency average = 2333.463 ms
latency average = 2305.564 ms
latency average = 2323.668 ms
Testing d6a3dbe14f
HEAD is now at d6a3dbe14f Fix theoretical torn page hazard.
latency average = 2576.712 ms
latency average = 2537.394 ms
latency average = 2617.201 ms
Testing d6a3dbe14f with hash_inserted_sorted.v4.patch
latency average = 2341.578 ms
latency average = 2333.859 ms
latency average = 2314.373 ms
Testing 3eb8eeccbe
HEAD is now at 3eb8eeccbe Remove obsolete comments and code from prior to f8f4227976.
latency average = 2637.642 ms
latency average = 2512.848 ms
latency average = 2584.967 ms
Testing 3eb8eeccbe with hash_inserted_sorted.v4.patch
latency average = 2372.732 ms
latency average = 2340.511 ms
latency average = 2365.156 ms
Testing b9424d014e
HEAD is now at b9424d014e Support writing "CREATE/ALTER TABLE ... SET STORAGE DEFAULT".
latency average = 2556.584 ms
latency average = 2525.312 ms
latency average = 2525.383 ms
Testing b9424d014e with hash_inserted_sorted.v4.patch
latency average = 2319.155 ms
latency average = 2309.208 ms
latency average = 2323.705 ms
Testing 36e545cd05
HEAD is now at 36e545cd05 Fix comments atop ReorderBufferAddInvalidations.
latency average = 2567.350 ms
latency average = 2668.871 ms
latency average = 2593.162 ms
Testing 36e545cd05 with hash_inserted_sorted.v4.patch
latency average = 2375.806 ms
latency average = 2331.210 ms
latency average = 2348.319 ms
Testing 5ca3645cb3
HEAD is now at 5ca3645cb3 Fix comment of SimpleLruInit() in slru.c
latency average = 2615.466 ms
latency average = 2489.282 ms
latency average = 2610.227 ms
Testing 5ca3645cb3 with hash_inserted_sorted.v4.patch
latency average = 2328.906 ms
latency average = 2309.380 ms
latency average = 2330.482 ms
Testing 85d8b30724
HEAD is now at 85d8b30724 Apply a better fix to mdunlinkfork().
latency average = 2558.060 ms
latency average = 2547.623 ms
latency average = 2655.184 ms
Testing 85d8b30724 with hash_inserted_sorted.v4.patch
latency average = 2338.695 ms
latency average = 2298.726 ms
latency average = 2310.082 ms
Testing 4f981df8e0
HEAD is now at 4f981df8e0 Report a more useful error for reloptions on a partitioned table.
latency average = 2542.523 ms
latency average = 2575.723 ms
latency average = 2511.547 ms
Testing 4f981df8e0 with hash_inserted_sorted.v4.patch
latency average = 2326.880 ms
latency average = 2315.611 ms
latency average = 2330.598 ms
Testing e613ace1f0
HEAD is now at e613ace1f0 Doc: add comments about PreventInTransactionBlock/IsInTransactionBlock.
latency average = 2548.314 ms
latency average = 2520.307 ms
latency average = 2602.306 ms
Testing e613ace1f0 with hash_inserted_sorted.v4.patch
latency average = 2319.946 ms
latency average = 2306.619 ms
latency average = 2326.536 ms
Testing b28ac1d24d
HEAD is now at b28ac1d24d Provide sigaction() for Windows.
latency average = 2603.864 ms
latency average = 2638.927 ms
latency average = 2623.397 ms
Testing b28ac1d24d with hash_inserted_sorted.v4.patch
latency average = 2333.284 ms
latency average = 2334.505 ms
latency average = 2335.565 ms
Testing 6bbd8b7385
HEAD is now at 6bbd8b7385 Use AbsoluteConfigLocation() when building an included path in hba.c
latency average = 2566.154 ms
latency average = 2582.984 ms
latency average = 2508.615 ms
Testing 6bbd8b7385 with hash_inserted_sorted.v4.patch
latency average = 2315.752 ms
latency average = 2304.409 ms
latency average = 2312.609 ms
Testing b5621b66e7
HEAD is now at b5621b66e7 Unify some internal error message wordings
latency average = 2575.142 ms
latency average = 2531.990 ms
latency average = 2642.011 ms
Testing b5621b66e7 with hash_inserted_sorted.v4.patch
latency average = 2344.448 ms
latency average = 2314.287 ms
latency average = 2317.520 ms
Testing 042c9091f0
HEAD is now at 042c9091f0 Produce more-optimal plans for bitmap scans on boolean columns.
latency average = 2564.736 ms
latency average = 2584.385 ms
latency average = 2586.905 ms
Testing 042c9091f0 with hash_inserted_sorted.v4.patch
latency average = 2314.212 ms
latency average = 2306.888 ms
latency average = 2304.622 ms
Testing 05a7be9355
HEAD is now at 05a7be9355 Suppress useless wakeups in walreceiver.
latency average = 2557.643 ms
latency average = 2558.682 ms
latency average = 2625.695 ms
Testing 05a7be9355 with hash_inserted_sorted.v4.patch
latency average = 2323.089 ms
latency average = 2310.719 ms
latency average = 2312.283 ms
Testing 3bdbdf5d06
HEAD is now at 3bdbdf5d06 Introduce pg_pwrite_zeros() in fileutils.c
latency average = 2581.453 ms
latency average = 2557.655 ms
latency average = 2497.719 ms
Testing 3bdbdf5d06 with hash_inserted_sorted.v4.patch
latency average = 2325.895 ms
latency average = 2303.553 ms
latency average = 2333.526 ms
Testing d7744d50a5
HEAD is now at d7744d50a5 Fix initialization of pg_stat_get_lastscan()
latency average = 2587.556 ms
latency average = 2499.254 ms
latency average = 2590.929 ms
Testing d7744d50a5 with hash_inserted_sorted.v4.patch
latency average = 2349.141 ms
latency average = 2314.493 ms
latency average = 2358.917 ms
Testing 1613de8bc3
HEAD is now at 1613de8bc3 Fix compiler warning on MSVC
latency average = 2565.962 ms
latency average = 2490.490 ms
latency average = 2623.311 ms
Testing 1613de8bc3 with hash_inserted_sorted.v4.patch
latency average = 2346.765 ms
latency average = 2346.219 ms
latency average = 2469.499 ms
Testing 0e758ae89a
HEAD is now at 0e758ae89a Fix failure to remove non-first segments of temporary tables.
latency average = 2554.269 ms
latency average = 2478.089 ms
latency average = 2501.562 ms
Testing 0e758ae89a with hash_inserted_sorted.v4.patch
latency average = 2351.535 ms
latency average = 2358.191 ms
latency average = 2342.335 ms
Testing a1a7bb8f16
HEAD is now at a1a7bb8f16 Move code related to configuration files in directories to new file
latency average = 2572.732 ms
latency average = 2549.068 ms
latency average = 2629.398 ms
Testing a1a7bb8f16 with hash_inserted_sorted.v4.patch
latency average = 2303.673 ms
latency average = 2244.184 ms
latency average = 2224.508 ms
Testing b0b72c64a0
HEAD is now at b0b72c64a0 Don't pass down nonnullable_vars while reducing outer joins.
latency average = 2600.178 ms
latency average = 2649.661 ms
latency average = 2592.777 ms
Testing b0b72c64a0 with hash_inserted_sorted.v4.patch
latency average = 2361.784 ms
latency average = 2314.966 ms
latency average = 2351.954 ms
Testing ff8fa0bf7e
HEAD is now at ff8fa0bf7e Handle SubPlan cases in find_nonnullable_rels/vars.
latency average = 2619.688 ms
latency average = 2479.717 ms
latency average = 2563.754 ms
Testing ff8fa0bf7e with hash_inserted_sorted.v4.patch
latency average = 2372.355 ms
latency average = 2334.374 ms
latency average = 2319.480 ms
Testing c3652cd84a
HEAD is now at c3652cd84a Remove redundant breaks in HeapTupleSatisfiesVisibility
latency average = 2595.598 ms
latency average = 2630.692 ms
latency average = 2601.062 ms
Testing c3652cd84a with hash_inserted_sorted.v4.patch
latency average = 2391.140 ms
latency average = 2337.106 ms
latency average = 2342.686 ms
Testing 2a71de8915
HEAD is now at 2a71de8915 Remove unneeded includes of <sys/stat.h>
latency average = 2655.597 ms
latency average = 2478.325 ms
latency average = 2552.646 ms
Testing 2a71de8915 with hash_inserted_sorted.v4.patch
latency average = 2380.744 ms
latency average = 2365.029 ms
latency average = 2363.443 ms
Testing 34fa0ddae5
HEAD is now at 34fa0ddae5 Fix CREATE DATABASE so we can pg_upgrade DBs with OIDs above 2^31.
latency average = 2586.739 ms
latency average = 2442.470 ms
latency average = 2508.969 ms
Testing 34fa0ddae5 with hash_inserted_sorted.v4.patch
latency average = 2372.138 ms
latency average = 2278.061 ms
latency average = 2288.480 ms
Testing 8c71467908
HEAD is now at 8c71467908 Correct error message for row-level triggers with transition tables on partitioned tables.
latency average = 2575.921 ms
latency average = 2565.585 ms
latency average = 2649.470 ms
Testing 8c71467908 with hash_inserted_sorted.v4.patch
latency average = 2353.758 ms
latency average = 2352.173 ms
latency average = 2360.371 ms
Testing 233cf6e8ad
HEAD is now at 233cf6e8ad Remove outdated include
latency average = 2602.480 ms
latency average = 2651.066 ms
latency average = 2608.862 ms
Testing 233cf6e8ad with hash_inserted_sorted.v4.patch
latency average = 2354.203 ms
latency average = 2335.024 ms
latency average = 2335.930 ms
Testing b0284bfb1d
HEAD is now at b0284bfb1d Create FKs properly when attaching table as partition
latency average = 2610.374 ms
latency average = 2532.709 ms
latency average = 2616.912 ms
Testing b0284bfb1d with hash_inserted_sorted.v4.patch
latency average = 2357.815 ms
latency average = 2299.897 ms
latency average = 2288.117 ms
Testing dea8349380
HEAD is now at dea8349380 Avoid crash after function syntax error in a replication worker.
latency average = 2571.665 ms
latency average = 2520.032 ms
latency average = 2490.022 ms
Testing dea8349380 with hash_inserted_sorted.v4.patch
latency average = 2341.478 ms
latency average = 2339.011 ms
latency average = 2306.446 ms
Testing 5fca91025e
HEAD is now at 5fca91025e Resolve partition strategy during early parsing
latency average = 2594.012 ms
latency average = 2537.600 ms
latency average = 2641.264 ms
Testing 5fca91025e with hash_inserted_sorted.v4.patch
latency average = 2306.030 ms
latency average = 2284.057 ms
latency average = 2269.448 ms
Testing 062eef3a9b
HEAD is now at 062eef3a9b Straighten include order in guc-file.l
latency average = 2617.288 ms
latency average = 2483.089 ms
latency average = 2608.614 ms
Testing 062eef3a9b with hash_inserted_sorted.v4.patch
latency average = 2323.138 ms
latency average = 2303.455 ms
latency average = 2328.547 ms
Testing be541efbfd
HEAD is now at be541efbfd Defend against unsupported partition relkind in logical replication worker.
latency average = 2613.276 ms
latency average = 2534.782 ms
latency average = 2679.991 ms
Testing be541efbfd with hash_inserted_sorted.v4.patch
latency average = 2342.325 ms
latency average = 2315.661 ms
latency average = 2375.281 ms
Testing d54e79ba28
HEAD is now at d54e79ba28 Fix copy-and-pasteo in comment.
latency average = 2652.618 ms
latency average = 2507.253 ms
latency average = 2569.875 ms
Testing d54e79ba28 with hash_inserted_sorted.v4.patch
latency average = 2328.429 ms
latency average = 2212.987 ms
latency average = 2213.743 ms
Testing 568546f7e4
HEAD is now at 568546f7e4 Improve the description of XLOG_RUNNING_XACTS.
latency average = 2614.805 ms
latency average = 2503.605 ms
latency average = 2552.067 ms
Testing 568546f7e4 with hash_inserted_sorted.v4.patch
latency average = 2335.791 ms
latency average = 2333.319 ms
latency average = 2358.751 ms
Testing 8e621c10c7
HEAD is now at 8e621c10c7 Remove code handling FORCE_NULL and FORCE_NOT_NULL for COPY TO
latency average = 2663.667 ms
latency average = 2489.555 ms
latency average = 2555.109 ms
Testing 8e621c10c7 with hash_inserted_sorted.v4.patch
latency average = 2348.930 ms
latency average = 2367.419 ms
latency average = 2334.123 ms
Testing 7c335b7a20
HEAD is now at 7c335b7a20 Add doubly linked count list implementation
latency average = 2615.585 ms
latency average = 2457.761 ms
latency average = 2498.906 ms
Testing 7c335b7a20 with hash_inserted_sorted.v4.patch
latency average = 2362.836 ms
latency average = 2351.036 ms
latency average = 2305.201 ms
Testing f4857082bc
HEAD is now at f4857082bc Fix planner failure with extended statistics on partitioned tables.
latency average = 2600.016 ms
latency average = 2675.312 ms
latency average = 2633.423 ms
Testing f4857082bc with hash_inserted_sorted.v4.patch
latency average = 2399.183 ms
latency average = 2367.420 ms
latency average = 2367.234 ms
[application/octet-stream] setup.sql (132B, ../../CAApHDvpyoGwu3-TuzK-T4SVMxsZSD4t+mun63jnj5vQksNq06g@mail.gmail.com/7-setup.sql)
download
[image/png] unpatched_vs_v4_patch.png (418.7K, ../../CAApHDvpyoGwu3-TuzK-T4SVMxsZSD4t+mun63jnj5vQksNq06g@mail.gmail.com/8-unpatched_vs_v4_patch.png)
download | view image
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Hash index build performance tweak from sorting
@ 2022-11-24 04:24 David Rowley <[email protected]>
parent: Simon Riggs <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: David Rowley @ 2022-11-24 04:24 UTC (permalink / raw)
To: Simon Riggs <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Kapila <[email protected]>; pgsql-hackers
On Thu, 24 Nov 2022 at 02:27, Simon Riggs <[email protected]> wrote:
>
> On Wed, 23 Nov 2022 at 13:04, David Rowley <[email protected]> wrote:
> > I'd rather see this solved like v4 is doing it.
>
> Please do. No further comments. Thanks for your help
Thanks. I pushed the v4 patch with some minor comment adjustments and
also renamed _hash_pgaddtup()'s new parameter to "appendtup". I felt
that better reflected what the parameter does in that function.
David
^ permalink raw reply [nested|flat] 15+ messages in thread
* [PATCH v33 2/5] Refactor index_concurrently_create_copy() for use with REPACK (CONCURRENTLY).
@ 2026-01-27 10:48 Antonin Houska <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Antonin Houska @ 2026-01-27 10:48 UTC (permalink / raw)
This patch moves the code to index_create_copy() and adds a "concurrently"
parameter so it can be used by REPACK (CONCURRENTLY).
With the CONCURRENTLY option, REPACK cannot simply swap the heap file and
rebuild its indexes. Instead, it needs to build a separate set of indexes
(including system catalog entries) *before* the actual swap, to reduce the
time AccessExclusiveLock needs to be held for.
---
src/backend/catalog/index.c | 54 +++++++++++++++++++++++---------
src/backend/commands/indexcmds.c | 6 ++--
src/backend/nodes/makefuncs.c | 9 +++---
src/include/catalog/index.h | 3 ++
src/include/nodes/makefuncs.h | 4 ++-
5 files changed, 54 insertions(+), 22 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5ee6389d39c..f8e6c3d804e 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1288,15 +1288,32 @@ index_create(Relation heapRelation,
/*
* index_concurrently_create_copy
*
- * Create concurrently an index based on the definition of the one provided by
- * caller. The index is inserted into catalogs and needs to be built later
- * on. This is called during concurrent reindex processing.
- *
- * "tablespaceOid" is the tablespace to use for this index.
+ * Variant of index_create_copy(), called during concurrent reindex
+ * processing.
*/
Oid
index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
Oid tablespaceOid, const char *newName)
+{
+ return index_create_copy(heapRelation, oldIndexId, tablespaceOid, newName,
+ true);
+}
+
+/*
+ * index_create_copy
+ *
+ * Create an index based on the definition of the one provided by caller. The
+ * index is inserted into catalogs. If 'concurrently' is TRUE, it needs to be
+ * built later on, otherwise it's built immediately.
+ *
+ * "tablespaceOid" is the tablespace to use for this index.
+ *
+ * The actual implementation of index_concurrently_create_copy(), reusable for
+ * other purposes.
+ */
+Oid
+index_create_copy(Relation heapRelation, Oid oldIndexId, Oid tablespaceOid,
+ const char *newName, bool concurrently)
{
Relation indexRelation;
IndexInfo *oldInfo,
@@ -1315,6 +1332,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
List *indexColNames = NIL;
List *indexExprs = NIL;
List *indexPreds = NIL;
+ int flags = 0;
indexRelation = index_open(oldIndexId, RowExclusiveLock);
@@ -1325,7 +1343,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
* Concurrent build of an index with exclusion constraints is not
* supported.
*/
- if (oldInfo->ii_ExclusionOps != NULL)
+ if (oldInfo->ii_ExclusionOps != NULL && concurrently)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("concurrent index creation for exclusion constraints is not supported")));
@@ -1381,9 +1399,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
}
/*
- * Build the index information for the new index. Note that rebuild of
- * indexes with exclusion constraints is not supported, hence there is no
- * need to fill all the ii_Exclusion* fields.
+ * Build the index information for the new index.
*/
newInfo = makeIndexInfo(oldInfo->ii_NumIndexAttrs,
oldInfo->ii_NumIndexKeyAttrs,
@@ -1392,10 +1408,13 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indexPreds,
oldInfo->ii_Unique,
oldInfo->ii_NullsNotDistinct,
- false, /* not ready for inserts */
- true,
+ !concurrently, /* isready */
+ concurrently, /* concurrent */
indexRelation->rd_indam->amsummarizing,
- oldInfo->ii_WithoutOverlaps);
+ oldInfo->ii_WithoutOverlaps,
+ oldInfo->ii_ExclusionOps,
+ oldInfo->ii_ExclusionProcs,
+ oldInfo->ii_ExclusionStrats);
/*
* Extract the list of column names and the column numbers for the new
@@ -1433,6 +1452,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
stattargets[i].isnull = isnull;
}
+ if (concurrently)
+ flags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT;
+
/*
* Now create the new index.
*
@@ -1456,7 +1478,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indcoloptions->values,
stattargets,
reloptionsDatum,
- INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT,
+ flags,
0,
true, /* allow table to be a system catalog? */
false, /* is_internal? */
@@ -2450,7 +2472,8 @@ BuildIndexInfo(Relation index)
indexStruct->indisready,
false,
index->rd_indam->amsummarizing,
- indexStruct->indisexclusion && indexStruct->indisunique);
+ indexStruct->indisexclusion && indexStruct->indisunique,
+ NULL, NULL, NULL);
/* fill in attribute numbers */
for (i = 0; i < numAtts; i++)
@@ -2510,7 +2533,8 @@ BuildDummyIndexInfo(Relation index)
indexStruct->indisready,
false,
index->rd_indam->amsummarizing,
- indexStruct->indisexclusion && indexStruct->indisunique);
+ indexStruct->indisexclusion && indexStruct->indisunique,
+ NULL, NULL, NULL);
/* fill in attribute numbers */
for (i = 0; i < numAtts; i++)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 635679cc1f2..34209bd1393 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -243,7 +243,8 @@ CheckIndexCompatible(Oid oldId,
*/
indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes,
accessMethodId, NIL, NIL, false, false,
- false, false, amsummarizing, isWithoutOverlaps);
+ false, false, amsummarizing, isWithoutOverlaps,
+ NULL, NULL, NULL);
typeIds = palloc_array(Oid, numberOfAttributes);
collationIds = palloc_array(Oid, numberOfAttributes);
opclassIds = palloc_array(Oid, numberOfAttributes);
@@ -930,7 +931,8 @@ DefineIndex(ParseState *pstate,
!concurrent,
concurrent,
amissummarizing,
- stmt->iswithoutoverlaps);
+ stmt->iswithoutoverlaps,
+ NULL, NULL, NULL);
typeIds = palloc_array(Oid, numberOfAttributes);
collationIds = palloc_array(Oid, numberOfAttributes);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 2caec621d73..ca7e21e8349 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -834,7 +834,8 @@ IndexInfo *
makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
List *predicates, bool unique, bool nulls_not_distinct,
bool isready, bool concurrent, bool summarizing,
- bool withoutoverlaps)
+ bool withoutoverlaps, Oid *exclusion_ops, Oid *exclusion_procs,
+ uint16 *exclusion_strats)
{
IndexInfo *n = makeNode(IndexInfo);
@@ -863,9 +864,9 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
n->ii_PredicateState = NULL;
/* exclusion constraints */
- n->ii_ExclusionOps = NULL;
- n->ii_ExclusionProcs = NULL;
- n->ii_ExclusionStrats = NULL;
+ n->ii_ExclusionOps = exclusion_ops;
+ n->ii_ExclusionProcs = exclusion_procs;
+ n->ii_ExclusionStrats = exclusion_strats;
/* speculative inserts */
n->ii_UniqueOps = NULL;
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index b259c4141ed..3426087b445 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -99,6 +99,9 @@ extern Oid index_concurrently_create_copy(Relation heapRelation,
Oid oldIndexId,
Oid tablespaceOid,
const char *newName);
+extern Oid index_create_copy(Relation heapRelation, Oid oldIndexId,
+ Oid tablespaceOid, const char *newName,
+ bool concurrently);
extern void index_concurrently_build(Oid heapRelationId,
Oid indexRelationId);
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 982ec25ae14..dcea148ae1a 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -99,7 +99,9 @@ extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid,
List *expressions, List *predicates,
bool unique, bool nulls_not_distinct,
bool isready, bool concurrent,
- bool summarizing, bool withoutoverlaps);
+ bool summarizing, bool withoutoverlaps,
+ Oid *exclusion_ops, Oid *exclusion_procs,
+ uint16 *exclusion_strats);
extern Node *makeStringConst(char *str, int location);
extern DefElem *makeDefElem(char *name, Node *arg, int location);
--
2.47.3
--r7amdyq3rmktb267
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
filename="v33-0003-Move-conversion-of-a-historic-to-MVCC-snapshot-t.patch"
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-01-27 10:48 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-10 09:04 [PATCH 7/8] Alter table set compression Dilip Kumar <[email protected]>
2022-08-01 15:37 Re: Hash index build performance tweak from sorting Simon Riggs <[email protected]>
2022-08-05 19:46 ` Re: Hash index build performance tweak from sorting David Zhang <[email protected]>
2022-08-30 16:27 ` Re: Hash index build performance tweak from sorting Simon Riggs <[email protected]>
2022-09-21 01:31 ` Re: Hash index build performance tweak from sorting David Rowley <[email protected]>
2022-09-21 11:43 ` Re: Hash index build performance tweak from sorting Simon Riggs <[email protected]>
2022-11-16 04:33 ` Re: Hash index build performance tweak from sorting Simon Riggs <[email protected]>
2022-11-17 14:34 ` Re: Hash index build performance tweak from sorting Tomas Vondra <[email protected]>
2022-11-23 13:07 ` Re: Hash index build performance tweak from sorting David Rowley <[email protected]>
2022-11-23 19:08 ` Re: Hash index build performance tweak from sorting Tomas Vondra <[email protected]>
2022-11-24 02:47 ` Re: Hash index build performance tweak from sorting David Rowley <[email protected]>
2022-11-23 13:04 ` Re: Hash index build performance tweak from sorting David Rowley <[email protected]>
2022-11-23 13:27 ` Re: Hash index build performance tweak from sorting Simon Riggs <[email protected]>
2022-11-24 04:24 ` Re: Hash index build performance tweak from sorting David Rowley <[email protected]>
2026-01-27 10:48 [PATCH v33 2/5] Refactor index_concurrently_create_copy() for use with REPACK (CONCURRENTLY). Antonin Houska <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox