public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v12 10/11] pg_ls_logdir to ignore error if initial/top dir is missing
31+ messages / 8 participants
[nested] [flat]
* [PATCH v12 10/11] pg_ls_logdir to ignore error if initial/top dir is missing
@ 2020-03-06 23:23 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 31+ messages in thread
From: Justin Pryzby @ 2020-03-06 23:23 UTC (permalink / raw)
---
src/backend/utils/adt/genfile.c | 2 +-
src/test/regress/input/tablespace.source | 4 ++++
src/test/regress/output/tablespace.source | 7 +++++++
3 files changed, 12 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index de63e77836..810c6b0f23 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -651,7 +651,7 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
Datum
pg_ls_logdir(PG_FUNCTION_ARGS)
{
- return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_MODERN);
+ return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_MODERN|LS_DIR_MISSING_OK);
}
/* Function to return the list of files in the WAL directory */
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 0b9cfe615e..2a1268e17c 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -16,6 +16,10 @@ CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
-- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
+-- This tests the missing_ok parameter. If that's not functioning, this would ERROR if the logdir doesn't exist yet.
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
+
-- try setting and resetting some properties for the new tablespace
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1c88e914e3..ba9a3fe29a 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -21,6 +21,13 @@ SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspa
------+------+--------------+-------
(0 rows)
+-- This tests the missing_ok parameter. If that's not functioning, this would ERROR if the logdir doesn't exist yet.
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
+ name | size | modification | isdir
+------+------+--------------+-------
+(0 rows)
+
-- try setting and resetting some properties for the new tablespace
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
--
2.17.0
--wIc/V6YLA2QdyfT4
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v12-0011-pg_ls_-dir-to-return-all-the-metadata-from-pg_st.patch"
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-02 21:03 Andres Freund <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Andres Freund @ 2022-04-02 21:03 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: John Naylor <[email protected]>; Thomas Munro <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
Hi,
On 2022-04-02 15:20:27 -0500, Justin Pryzby wrote:
> On Sat, Apr 02, 2022 at 06:41:30PM +0700, John Naylor wrote:
> > On Sat, Apr 2, 2022 at 5:27 PM Thomas Munro <[email protected]> wrote:
> > > Reproduced locally, using the same few lines from the cluster.sql
> > > test. I'll try to dig more tomorrow...
> >
> > Thanks! Unfortunately I can't reproduce locally with clang 13/gcc 11,
> > with -Og or -O2 with CFLAGS="-fsanitize=undefined,alignment" ...
>
> Like Thomas just said, I had to use:
> CFLAGS="-Og -fsanitize=undefined,alignment -fno-sanitize-recover=all
>
> I'm a couple few steps out of my league here, but it may be an issue with:
>
> commit 4ea51cdfe85ceef8afabceb03c446574daa0ac23
> Author: Robert Haas <[email protected]>
> Date: Mon Jan 19 15:20:31 2015 -0500
>
> Use abbreviated keys for faster sorting of text datums.
>
> This is enough to avoid the crash, which might be a useful hint..
>
> @@ -4126,22 +4126,23 @@ copytup_cluster(Tuplesortstate *state, SortTuple *stup, void *tup)
> /*
> * set up first-column key value, and potentially abbreviate, if it's a
> * simple column
> */
> + stup->isnull1 = false;
> if (state->indexInfo->ii_IndexAttrNumbers[0] == 0)
> return;
>
> original = heap_getattr(tuple,
> state->indexInfo->ii_IndexAttrNumbers[0],
> state->tupDesc,
> &stup->isnull1);
I don't think that can be correct - the column can be NULL afaics. And I don't
think in that patch it's needed, because it always goes through ->comparetup()
when state->onlyKey isn't explicitly set. Which tuplesort_begin_cluster() as
well as several others don't. And you'd just sort an uninitialized datum
immediately after.
It's certainly not pretty that copytup_cluster() can use SortTuples without
actually using SortTuples. Afaics it basically only computes isnull1/datum1 if
state->indexInfo->ii_IndexAttrNumbers[0] == 0.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-02 21:45 Thomas Munro <[email protected]>
parent: Andres Freund <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Thomas Munro @ 2022-04-02 21:45 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Sun, Apr 3, 2022 at 9:03 AM Andres Freund <[email protected]> wrote:
> It's certainly not pretty that copytup_cluster() can use SortTuples without
> actually using SortTuples. Afaics it basically only computes isnull1/datum1 if
> state->indexInfo->ii_IndexAttrNumbers[0] == 0.
I think we just need to decide up front if we're in a situation that
can't provide datum1/isnull1 (in this case because it's an expression
index), and skip the optimised paths. Here's an experimental patch...
still looking into whether there are more cases like this...
(There's also room to recognise when you don't even need to look at
isnull1 for a less branchy optimised sort, but that was already
discussed and put off for later.)
Attachments:
[text/x-patch] 0001-WIP-Fix-tuplesort-optimizations-for-expression-based.patch (2.7K, ../../CA+hUKGLNpDXytCwUnOQYvGkvjR4h+0ev=ivqLGuUO-w0WAJ_7A@mail.gmail.com/2-0001-WIP-Fix-tuplesort-optimizations-for-expression-based.patch)
download | inline diff:
From a8a7c9ec4f0b96ed9d889d008731864f3d4e87d1 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email protected]>
Date: Sun, 3 Apr 2022 09:41:04 +1200
Subject: [PATCH] WIP: Fix tuplesort optimizations for expression-based
CLUSTER.
---
src/backend/utils/sort/tuplesort.c | 20 ++++++++++++++++++--
1 file changed, 18 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 361527098f..34714d9baf 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -268,6 +268,8 @@ struct Tuplesortstate
MemoryContext tuplecontext; /* sub-context of sortcontext for tuple data */
LogicalTapeSet *tapeset; /* logtape.c object for tapes in a temp file */
+ bool disable_datum1; /* disable leading value-based optimizations */
+
/*
* These function pointers decouple the routines that must know what kind
* of tuple we are sorting from the routines that don't need to know it.
@@ -1095,6 +1097,13 @@ tuplesort_begin_cluster(TupleDesc tupDesc,
state->indexInfo = BuildIndexInfo(indexRel);
+ /*
+ * If we don't have a simple attribute, disable the use of datum1/isnull1.
+ * copytup_cluster() doesn't know how to compute expressions.
+ */
+ if (state->indexInfo->ii_IndexAttrNumbers[0] == 0)
+ state->disable_datum1 = true;
+
state->tupDesc = tupDesc; /* assume we need not copy tupDesc */
indexScanKey = _bt_mkscankey(indexRel, NULL);
@@ -3593,20 +3602,27 @@ tuplesort_sort_memtuples(Tuplesortstate *state)
if (state->memtupcount > 1)
{
- /* Do we have a specialization for the leading column's comparator? */
+ /*
+ * Do we have a specialization for the leading column's comparator,
+ * and has the leading column's value or abbreviation been stored in
+ * datum1/isnull1?
+ */
if (state->sortKeys &&
+ !state->disable_datum1 &&
state->sortKeys[0].comparator == ssup_datum_unsigned_cmp)
{
elog(DEBUG1, "qsort_tuple_unsigned");
qsort_tuple_unsigned(state->memtuples, state->memtupcount, state);
}
else if (state->sortKeys &&
+ !state->disable_datum1 &&
state->sortKeys[0].comparator == ssup_datum_signed_cmp)
{
elog(DEBUG1, "qsort_tuple_signed");
qsort_tuple_signed(state->memtuples, state->memtupcount, state);
}
else if (state->sortKeys &&
+ !state->disable_datum1 &&
state->sortKeys[0].comparator == ssup_datum_int32_cmp)
{
elog(DEBUG1, "qsort_tuple_int32");
@@ -4134,7 +4150,7 @@ copytup_cluster(Tuplesortstate *state, SortTuple *stup, void *tup)
* set up first-column key value, and potentially abbreviate, if it's a
* simple column
*/
- if (state->indexInfo->ii_IndexAttrNumbers[0] == 0)
+ if (state->disable_datum1)
return;
original = heap_getattr(tuple,
--
2.35.1
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-02 23:11 Andres Freund <[email protected]>
parent: Thomas Munro <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Andres Freund @ 2022-04-02 23:11 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
Hi,
On 2022-04-03 09:45:13 +1200, Thomas Munro wrote:
> On Sun, Apr 3, 2022 at 9:03 AM Andres Freund <[email protected]> wrote:
> > It's certainly not pretty that copytup_cluster() can use SortTuples without
> > actually using SortTuples. Afaics it basically only computes isnull1/datum1 if
> > state->indexInfo->ii_IndexAttrNumbers[0] == 0.
>
> I think we just need to decide up front if we're in a situation that
> can't provide datum1/isnull1 (in this case because it's an expression
> index), and skip the optimised paths. Here's an experimental patch...
> still looking into whether there are more cases like this...
That's a lot of redundant checks. How about putting all the checks for
optimized paths into one if (state->sortKeys && !state->disable_datum1)?
I'm a bit worried that none of the !ubsan tests failed on this...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-03 05:46 Thomas Munro <[email protected]>
parent: Andres Freund <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Thomas Munro @ 2022-04-03 05:46 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Sun, Apr 3, 2022 at 11:11 AM Andres Freund <[email protected]> wrote:
> On 2022-04-03 09:45:13 +1200, Thomas Munro wrote:
> > I think we just need to decide up front if we're in a situation that
> > can't provide datum1/isnull1 (in this case because it's an expression
> > index), and skip the optimised paths. Here's an experimental patch...
> > still looking into whether there are more cases like this...
I didn't find anything else.
Maybe it'd be better if we explicitly declared whether datum1 is used
in each tuplesort mode's 'begin' function, right next to the code that
installs the set of routines that are in control of that? Trying that
in this version. Is it clearer what's going on like this?
> That's a lot of redundant checks. How about putting all the checks for
> optimized paths into one if (state->sortKeys && !state->disabl1e_datum1)?
OK, sure.
> I'm a bit worried that none of the !ubsan tests failed on this...
In accordance with whoever-it-was-that-said-that's law about things
that aren't tested, this are turned out to be broken already[1]. Once
we fix that we should have a new test in the three that might also
eventually have failed under this UB, given enough chaos.
[1] https://www.postgresql.org/message-id/CA%2BhUKG%2BbA%2BbmwD36_oDxAoLrCwZjVtST2fqe%3Db4%3DqZcmU7u89A%...
Attachments:
[text/x-patch] v2-0001-Fix-tuplesort-optimizations-for-expression-based-.patch (5.8K, ../../CA+hUKG+n94QKCYZTO7Esu2L5y8mt8GsDm=3Ke__jkkntVpjnQA@mail.gmail.com/2-v2-0001-Fix-tuplesort-optimizations-for-expression-based-.patch)
download | inline diff:
From b0a79f91edc6ce305f77373b92f31100fcad07d5 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email protected]>
Date: Sun, 3 Apr 2022 09:41:04 +1200
Subject: [PATCH v2] Fix tuplesort optimizations for expression-based CLUSTER.
When redirecting sorts to specialized variants, commit 69749243 failed
to handle the case where CLUSTER-sort decides not to initialize datum1
and isnull1. Fix by hoisting that decision up a level and advertising
whether datum1 can be used, in the Tuplesortstate object.
Per reports from UBsan and Valgrind while running the cluster.sql test.
Reviewed-by: Andres Freund <[email protected]>
Discussion: https://postgr.es/m/CAFBsxsF1TeK5Fic0M%2BTSJXzbKsY6aBqJGNj6ptURuB09ZF6k_w%40mail.gmail.com
---
src/backend/utils/sort/tuplesort.c | 73 ++++++++++++++++++++++--------
1 file changed, 53 insertions(+), 20 deletions(-)
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 361527098f..58441ed638 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -306,6 +306,12 @@ struct Tuplesortstate
void (*readtup) (Tuplesortstate *state, SortTuple *stup,
LogicalTape *tape, unsigned int len);
+ /*
+ * Whether SortTuple's datum1 and isnull1 members are maintained by the
+ * above routines. If not, some sort specializations are disabled.
+ */
+ bool haveDatum1;
+
/*
* This array holds the tuples now in sort memory. If we are in state
* INITIAL, the tuples are in no particular order; if we are in state
@@ -1016,6 +1022,7 @@ tuplesort_begin_heap(TupleDesc tupDesc,
state->copytup = copytup_heap;
state->writetup = writetup_heap;
state->readtup = readtup_heap;
+ state->haveDatum1 = true;
state->tupDesc = tupDesc; /* assume we need not copy tupDesc */
state->abbrevNext = 10;
@@ -1095,6 +1102,15 @@ tuplesort_begin_cluster(TupleDesc tupDesc,
state->indexInfo = BuildIndexInfo(indexRel);
+ /*
+ * If we don't have a simple leading attribute, we can't easily initialize
+ * datum1, so disable optimizations based on datum1.
+ */
+ if (state->indexInfo->ii_IndexAttrNumbers[0] == 0)
+ state->haveDatum1 = false;
+ else
+ state->haveDatum1 = true;
+
state->tupDesc = tupDesc; /* assume we need not copy tupDesc */
indexScanKey = _bt_mkscankey(indexRel, NULL);
@@ -1188,6 +1204,7 @@ tuplesort_begin_index_btree(Relation heapRel,
state->writetup = writetup_index;
state->readtup = readtup_index;
state->abbrevNext = 10;
+ state->haveDatum1 = true;
state->heapRel = heapRel;
state->indexRel = indexRel;
@@ -1262,6 +1279,7 @@ tuplesort_begin_index_hash(Relation heapRel,
state->copytup = copytup_index;
state->writetup = writetup_index;
state->readtup = readtup_index;
+ state->haveDatum1 = true;
state->heapRel = heapRel;
state->indexRel = indexRel;
@@ -1302,6 +1320,7 @@ tuplesort_begin_index_gist(Relation heapRel,
state->copytup = copytup_index;
state->writetup = writetup_index;
state->readtup = readtup_index;
+ state->haveDatum1 = true;
state->heapRel = heapRel;
state->indexRel = indexRel;
@@ -1366,6 +1385,7 @@ tuplesort_begin_datum(Oid datumType, Oid sortOperator, Oid sortCollation,
state->writetup = writetup_datum;
state->readtup = readtup_datum;
state->abbrevNext = 10;
+ state->haveDatum1 = true;
state->datumType = datumType;
@@ -3593,27 +3613,40 @@ tuplesort_sort_memtuples(Tuplesortstate *state)
if (state->memtupcount > 1)
{
- /* Do we have a specialization for the leading column's comparator? */
- if (state->sortKeys &&
- state->sortKeys[0].comparator == ssup_datum_unsigned_cmp)
- {
- elog(DEBUG1, "qsort_tuple_unsigned");
- qsort_tuple_unsigned(state->memtuples, state->memtupcount, state);
- }
- else if (state->sortKeys &&
- state->sortKeys[0].comparator == ssup_datum_signed_cmp)
- {
- elog(DEBUG1, "qsort_tuple_signed");
- qsort_tuple_signed(state->memtuples, state->memtupcount, state);
- }
- else if (state->sortKeys &&
- state->sortKeys[0].comparator == ssup_datum_int32_cmp)
+ /*
+ * Do we have the leading column's value or abbreviation in datum1,
+ * and is there a specialization for its comparator?
+ */
+ if (state->haveDatum1 && state->sortKeys)
{
- elog(DEBUG1, "qsort_tuple_int32");
- qsort_tuple_int32(state->memtuples, state->memtupcount, state);
+ if (state->sortKeys[0].comparator == ssup_datum_unsigned_cmp)
+ {
+ elog(DEBUG1, "qsort_tuple_unsigned");
+ qsort_tuple_unsigned(state->memtuples,
+ state->memtupcount,
+ state);
+ return;
+ }
+ else if (state->sortKeys[0].comparator == ssup_datum_signed_cmp)
+ {
+ elog(DEBUG1, "qsort_tuple_signed");
+ qsort_tuple_signed(state->memtuples,
+ state->memtupcount,
+ state);
+ return;
+ }
+ else if (state->sortKeys[0].comparator == ssup_datum_int32_cmp)
+ {
+ elog(DEBUG1, "qsort_tuple_int32");
+ qsort_tuple_int32(state->memtuples,
+ state->memtupcount,
+ state);
+ return;
+ }
}
+
/* Can we use the single-key sort function? */
- else if (state->onlyKey != NULL)
+ if (state->onlyKey != NULL)
{
elog(DEBUG1, "qsort_ssup");
qsort_ssup(state->memtuples, state->memtupcount,
@@ -4134,7 +4167,7 @@ copytup_cluster(Tuplesortstate *state, SortTuple *stup, void *tup)
* set up first-column key value, and potentially abbreviate, if it's a
* simple column
*/
- if (state->indexInfo->ii_IndexAttrNumbers[0] == 0)
+ if (!state->haveDatum1)
return;
original = heap_getattr(tuple,
@@ -4229,7 +4262,7 @@ readtup_cluster(Tuplesortstate *state, SortTuple *stup,
LogicalTapeReadExact(tape, &tuplen, sizeof(tuplen));
stup->tuple = (void *) tuple;
/* set up first-column key value, if it's a simple column */
- if (state->indexInfo->ii_IndexAttrNumbers[0] != 0)
+ if (state->haveDatum1)
stup->datum1 = heap_getattr(tuple,
state->indexInfo->ii_IndexAttrNumbers[0],
state->tupDesc,
--
2.35.1
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-03 16:32 Andres Freund <[email protected]>
parent: Thomas Munro <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Andres Freund @ 2022-04-03 16:32 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
Hi,
On 2022-04-03 17:46:28 +1200, Thomas Munro wrote:
> On Sun, Apr 3, 2022 at 11:11 AM Andres Freund <[email protected]> wrote:
> > On 2022-04-03 09:45:13 +1200, Thomas Munro wrote:
> > > I think we just need to decide up front if we're in a situation that
> > > can't provide datum1/isnull1 (in this case because it's an expression
> > > index), and skip the optimised paths. Here's an experimental patch...
> > > still looking into whether there are more cases like this...
>
> I didn't find anything else.
>
> Maybe it'd be better if we explicitly declared whether datum1 is used
> in each tuplesort mode's 'begin' function, right next to the code that
> installs the set of routines that are in control of that? Trying that
> in this version. Is it clearer what's going on like this?
Seems an improvement.
> > I'm a bit worried that none of the !ubsan tests failed on this...
>
> In accordance with whoever-it-was-that-said-that's law about things
> that aren't tested, this are turned out to be broken already[1].
Yea :/.
Would be good to get this committed soon, so we can see further ubsan
violations introduced in the next few days (and so I can unblock my local dev
tests :P).
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-03 23:01 Thomas Munro <[email protected]>
parent: Andres Freund <[email protected]>
0 siblings, 2 replies; 31+ messages in thread
From: Thomas Munro @ 2022-04-03 23:01 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Mon, Apr 4, 2022 at 4:32 AM Andres Freund <[email protected]> wrote:
> Would be good to get this committed soon, so we can see further ubsan
> violations introduced in the next few days (and so I can unblock my local dev
> tests :P).
Pushed (with a minor tweak).
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-06 10:31 John Naylor <[email protected]>
parent: Thomas Munro <[email protected]>
1 sibling, 0 replies; 31+ messages in thread
From: John Naylor @ 2022-04-06 10:31 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
Here is the updated insertion sort threshold patch based on Thomas'
experimental v4 0010, with adjusted regression test output. I only
found a couple places where it could make sense to add sort keys to
test queries, but 1) not enough to make a big difference and 2) the
adjustments looked out of place, so I decided to just update all the
regression tests in one go. Since the patch here is a bit more (and
less) involved than Thomas' 0010, I'm going to refrain from committing
until it gets review. If not in the next couple days, I will bring it
up at the beginning of the v16 cycle.
--
John Naylor
EDB: http://www.enterprisedb.com
Attachments:
[text/x-patch] v5-0010-Raise-qsort-insertion-sort-threshold.patch (53.2K, ../../CAFBsxsHtKW_3oQrNt_XVs3d1n_WV3foCOFF0V92Nbp1a8K4UZg@mail.gmail.com/2-v5-0010-Raise-qsort-insertion-sort-threshold.patch)
download | inline diff:
From 74b934bc5ed8f6733c064c0ef832e1aa9949f216 Mon Sep 17 00:00:00 2001
From: John Naylor <[email protected]>
Date: Wed, 6 Apr 2022 16:38:28 +0700
Subject: [PATCH v5] Raise qsort insertion sort threshold
Our qsort algorithm is from NetBSD and is described in the 1993 paper
"Engineering a Sort Function". It includes three hard coded thresholds that
control the point at which we drop to insertion sort, and whether we use 1,
3 or 9 samples for choosing a pivot. The insertion sort threshold of 7 was
chosen by testing on hardware available in 1993. Our testing has shown
that hardware from 2010 to 2020 prefer a larger threshold. The ideal value
varies with input distribution, but 20 seems to be a good compromise that
works well with all of them. This is more in line with thresholds found
in other software as well.
Since the new insertion sort threshold is larger then the singleton range
where a single sample is chosen for the pivot, get rid of the middle
range. There are now two thresholds.
Also use macros intead of hard-coded values. This improves readability
and enables specializing the thresholds if desired.
The changes in the regression tests are needed for the change in sort
stability when the sort key contains duplicates.
Thomas Munro and John Naylor
Discussion:
https://www.postgresql.org/message-id/CA%2BhUKGJhOtjQH%2BwjCodtJzHAFCAPYyt6Qm9E1mUoJph42qo1hg%40mail.gmail.com
Discussion:
https://www.postgresql.org/message-id/CAFBsxsHr-C1xqjUMjeUN3-FvNzKiAt3gcfBKt8PFN2mov7z2gQ%40mail.gmail.com
---
.../expected/pg_stat_statements.out | 6 +-
src/include/lib/sort_template.h | 38 +-
src/test/regress/expected/create_index.out | 2 +-
src/test/regress/expected/geometry.out | 2 +-
src/test/regress/expected/groupingsets.out | 4 +-
src/test/regress/expected/inet.out | 4 +-
src/test/regress/expected/join.out | 2 +-
src/test/regress/expected/sqljson.out | 10 +-
src/test/regress/expected/tsrf.out | 28 +-
src/test/regress/expected/tuplesort.out | 10 +-
src/test/regress/expected/window.out | 542 +++++++++---------
11 files changed, 331 insertions(+), 317 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0abe34bb6..aeb8f04aea 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -169,12 +169,12 @@ SELECT *
SELECT * FROM test ORDER BY a;
a | b
---+----------------------
- 1 | a
1 | 111
- 2 | b
+ 1 | a
2 | 222
- 3 | c
+ 2 | b
3 | 333
+ 3 | c
4 | 444
5 | 555
6 | 666
diff --git a/src/include/lib/sort_template.h b/src/include/lib/sort_template.h
index 3122a93009..7b92b2c084 100644
--- a/src/include/lib/sort_template.h
+++ b/src/include/lib/sort_template.h
@@ -40,6 +40,11 @@
*
* - ST_COMPARE_ARG_TYPE - type of extra argument
*
+ * Optional macros for tuning algorithm choices:
+ *
+ * - ST_THRESHOLD_INSERTION_SORT - below this size we do insertion sort
+ * - ST_THRESHOLD_MED9 - above this size we partition with med9, otherwise with med3
+ *
* The prototype of the generated sort function is:
*
* void ST_SORT(ST_ELEMENT_TYPE *data, size_t n,
@@ -172,6 +177,14 @@
#define ST_SORT_INVOKE_ARG
#endif
+/* Default input size thresholds to control algorithm behavior. */
+#ifndef ST_THRESHOLD_INSERTION_SORT
+#define ST_THRESHOLD_INSERTION_SORT 20
+#endif
+#ifndef ST_THRESHOLD_MED9
+#define ST_THRESHOLD_MED9 40
+#endif
+
#ifdef ST_DECLARE
#ifdef ST_COMPARE_RUNTIME_POINTER
@@ -296,7 +309,7 @@ ST_SORT(ST_ELEMENT_TYPE * data, size_t n
loop:
DO_CHECK_FOR_INTERRUPTS();
- if (n < 7)
+ if (n < ST_THRESHOLD_INSERTION_SORT)
{
for (pm = a + ST_POINTER_STEP; pm < a + n * ST_POINTER_STEP;
pm += ST_POINTER_STEP)
@@ -318,21 +331,20 @@ loop:
}
if (presorted)
return;
+ /* Partition with median of three for medium input. */
+ pl = a;
pm = a + (n / 2) * ST_POINTER_STEP;
- if (n > 7)
+ pn = a + (n - 1) * ST_POINTER_STEP;
+ if (n > ST_THRESHOLD_MED9)
{
- pl = a;
- pn = a + (n - 1) * ST_POINTER_STEP;
- if (n > 40)
- {
- size_t d = (n / 8) * ST_POINTER_STEP;
+ /* Partition with median of nine for large input. */
+ size_t d = (n / 8) * ST_POINTER_STEP;
- pl = DO_MED3(pl, pl + d, pl + 2 * d);
- pm = DO_MED3(pm - d, pm, pm + d);
- pn = DO_MED3(pn - 2 * d, pn - d, pn);
- }
- pm = DO_MED3(pl, pm, pn);
+ pl = DO_MED3(pl, pl + d, pl + 2 * d);
+ pm = DO_MED3(pm - d, pm, pm + d);
+ pn = DO_MED3(pn - 2 * d, pn - d, pn);
}
+ pm = DO_MED3(pl, pm, pn);
DO_SWAP(a, pm);
pa = pb = a + ST_POINTER_STEP;
pc = pd = a + (n - 1) * ST_POINTER_STEP;
@@ -430,3 +442,5 @@ loop:
#undef ST_SORT_PROTO_ELEMENT_SIZE
#undef ST_SWAP
#undef ST_SWAPN
+#undef ST_THRESHOLD_INSERTION_SORT
+#undef ST_THRESHOLD_MED9
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d55aec3a1d..9b548fe3e8 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -172,8 +172,8 @@ SELECT * FROM point_tbl ORDER BY f1 <-> '0,1';
(10,10)
(-5,-12)
(5.1,34.5)
- (Infinity,1e+300)
(1e+300,Infinity)
+ (Infinity,1e+300)
(NaN,NaN)
(11 rows)
diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out
index 3b364d1e3b..275d915f92 100644
--- a/src/test/regress/expected/geometry.out
+++ b/src/test/regress/expected/geometry.out
@@ -4321,8 +4321,8 @@ SELECT c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance
<(100,1),115> | (1e+300,Infinity) | Infinity
<(100,1),115> | (Infinity,1e+300) | Infinity
<(3,5),0> | (NaN,NaN) | NaN
- <(1,2),3> | (NaN,NaN) | NaN
<(5,1),3> | (NaN,NaN) | NaN
+ <(1,2),3> | (NaN,NaN) | NaN
<(1,3),5> | (NaN,NaN) | NaN
<(100,200),10> | (NaN,NaN) | NaN
<(1,2),100> | (NaN,NaN) | NaN
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 6a56f0b09c..f0be23af7f 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -94,12 +94,12 @@ select a, b, grouping(a,b), sum(v), count(*), max(v)
1 | | 1 | 60 | 5 | 14
1 | 1 | 0 | 21 | 2 | 11
2 | | 1 | 15 | 1 | 15
- 3 | | 1 | 33 | 2 | 17
1 | 2 | 0 | 25 | 2 | 13
+ 3 | | 1 | 33 | 2 | 17
1 | 3 | 0 | 14 | 1 | 14
4 | | 1 | 37 | 2 | 19
- 4 | 1 | 0 | 37 | 2 | 19
2 | 3 | 0 | 15 | 1 | 15
+ 4 | 1 | 0 | 37 | 2 | 19
3 | 3 | 0 | 16 | 1 | 16
3 | 4 | 0 | 17 | 1 | 17
(12 rows)
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index d5bf9e2aaa..c3afcc1574 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -390,8 +390,8 @@ SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
- 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
+ 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
@@ -538,8 +538,8 @@ SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
- 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
+ 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index bf1a2db2cf..98655f94ff 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2386,8 +2386,8 @@ select * from
---+---+-------+---+----
| | | | 0
| | | |
- | 0 | zero | |
| | null | |
+ | 0 | zero | |
8 | 8 | eight | |
7 | 7 | seven | |
6 | 6 | six | |
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 6cadd87868..2574b72218 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -873,13 +873,13 @@ FROM
(VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL), (5), (4)) foo(bar);
bar | json_arrayagg
-----+---------------
+ 2 | [4, 4]
4 | [4, 4]
4 | [4, 4]
- 2 | [4, 4]
- 5 | [5, 3, 5]
- 3 | [5, 3, 5]
- 1 | [5, 3, 5]
- 5 | [5, 3, 5]
+ 3 | [3, 5, 5]
+ 1 | [3, 5, 5]
+ 5 | [3, 5, 5]
+ 5 | [3, 5, 5]
|
|
|
diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out
index d47b5f6ec5..8be60cfd98 100644
--- a/src/test/regress/expected/tsrf.out
+++ b/src/test/regress/expected/tsrf.out
@@ -356,14 +356,14 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | 1 | 3
| bar | 1 | 2
| foo | 1 | 1
- | foo | 2 | 1
a | bar | 2 | 1
- b | | 2 | 1
a | foo | 2 | 1
- | bar | 2 | 2
a | | 2 | 2
- | | 2 | 3
b | bar | 2 | 1
+ b | | 2 | 1
+ | | 2 | 3
+ | bar | 2 | 2
+ | foo | 2 | 1
(16 rows)
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g);
@@ -400,19 +400,19 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
-------+-----+---+-------
a | foo | | 2
a | | | 4
+ a | | 1 | 2
a | | 2 | 2
a | bar | 1 | 1
a | bar | 2 | 1
a | bar | | 2
a | foo | 1 | 1
a | foo | 2 | 1
- a | | 1 | 2
b | bar | 1 | 1
- b | | | 2
b | | 1 | 1
b | bar | 2 | 1
- b | bar | | 2
b | | 2 | 1
+ b | bar | | 2
+ b | | | 2
| | 2 | 3
| | | 6
| bar | 1 | 2
@@ -435,22 +435,22 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
a | | 1 | 2
b | | 1 | 1
| | 1 | 3
- a | | 2 | 2
+ b | bar | 2 | 1
b | | 2 | 1
| bar | 2 | 2
| | 2 | 3
| foo | 2 | 1
a | bar | 2 | 1
a | foo | 2 | 1
- b | bar | 2 | 1
- a | | | 4
+ a | | 2 | 2
b | bar | | 2
b | | | 2
| | | 6
- a | foo | | 2
- a | bar | | 2
| bar | | 4
+ a | bar | | 2
| foo | | 2
+ a | foo | | 2
+ a | | | 4
(24 rows)
reset enable_hashagg;
@@ -598,8 +598,8 @@ FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b);
a | b | g
---+---+---
3 | 2 | 1
- 5 | 1 | 2
- 3 | 1 | 3
+ 3 | 2 | 2
+ 3 | 2 | 3
(3 rows)
-- LIMIT / OFFSET is evaluated after SRF evaluation
diff --git a/src/test/regress/expected/tuplesort.out b/src/test/regress/expected/tuplesort.out
index 418f296a3f..28953f8dd7 100644
--- a/src/test/regress/expected/tuplesort.out
+++ b/src/test/regress/expected/tuplesort.out
@@ -231,8 +231,8 @@ ORDER BY ctid LIMIT 5;
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
- 3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
+ 3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997
(5 rows)
@@ -242,11 +242,11 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | | |
- 20002 | | | |
20003 | | | |
- 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20002 | | | |
+ 0 | | | |
20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
(5 rows)
ROLLBACK;
@@ -335,9 +335,9 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | | |
20003 | | | |
20002 | | | |
+ 0 | | | |
9993 | 00000000-0000-0000-0000-000000009992 | 00000000-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008
9994 | 00000000-0000-0000-0000-000000009993 | 00000000-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007
(5 rows)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index bb9ff7f07b..2a3353b5d8 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -23,13 +23,13 @@ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM emps
-----------+-------+--------+-------
develop | 7 | 4200 | 25100
develop | 9 | 4500 | 25100
- develop | 11 | 5200 | 25100
develop | 10 | 5200 | 25100
+ develop | 11 | 5200 | 25100
develop | 8 | 6000 | 25100
personnel | 5 | 3500 | 7400
personnel | 2 | 3900 | 7400
- sales | 3 | 4800 | 14600
sales | 4 | 4800 | 14600
+ sales | 3 | 4800 | 14600
sales | 1 | 5000 | 14600
(10 rows)
@@ -38,13 +38,13 @@ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary
-----------+-------+--------+------
develop | 7 | 4200 | 1
develop | 9 | 4500 | 2
- develop | 11 | 5200 | 3
develop | 10 | 5200 | 3
+ develop | 11 | 5200 | 3
develop | 8 | 6000 | 5
personnel | 5 | 3500 | 1
personnel | 2 | 3900 | 2
- sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
+ sales | 3 | 4800 | 1
sales | 1 | 5000 | 3
(10 rows)
@@ -78,16 +78,16 @@ GROUP BY four, ten ORDER BY four, ten;
SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
depname | empno | salary | sum
-----------+-------+--------+-------
- develop | 11 | 5200 | 25100
+ develop | 10 | 5200 | 25100
develop | 7 | 4200 | 25100
develop | 9 | 4500 | 25100
develop | 8 | 6000 | 25100
- develop | 10 | 5200 | 25100
+ develop | 11 | 5200 | 25100
personnel | 5 | 3500 | 7400
personnel | 2 | 3900 | 7400
- sales | 3 | 4800 | 14600
sales | 1 | 5000 | 14600
sales | 4 | 4800 | 14600
+ sales | 3 | 4800 | 14600
(10 rows)
SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
@@ -95,13 +95,13 @@ SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITI
-----------+-------+--------+------
develop | 7 | 4200 | 1
personnel | 5 | 3500 | 1
- sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
- personnel | 2 | 3900 | 2
+ sales | 3 | 4800 | 1
develop | 9 | 4500 | 2
- sales | 1 | 5000 | 3
- develop | 11 | 5200 | 3
+ personnel | 2 | 3900 | 2
develop | 10 | 5200 | 3
+ develop | 11 | 5200 | 3
+ sales | 1 | 5000 | 3
develop | 8 | 6000 | 5
(10 rows)
@@ -394,12 +394,12 @@ SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM te
SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
last_value | ten | four
------------+-----+------
- 0 | 0 | 0
- 0 | 0 | 2
- 0 | 0 | 0
- 1 | 1 | 1
+ 2 | 0 | 0
+ 2 | 0 | 0
+ 2 | 0 | 2
1 | 1 | 3
1 | 1 | 1
+ 1 | 1 | 1
3 | 3 | 3
0 | 4 | 0
1 | 7 | 1
@@ -803,14 +803,14 @@ SELECT sum(unique1) over (order by four range between current row and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -922,14 +922,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- 8 | 0 | 0
- 4 | 8 | 0
- 5 | 4 | 0
- 9 | 5 | 1
- 1 | 9 | 1
- 6 | 1 | 1
- 2 | 6 | 2
- 3 | 2 | 2
+ 8 | 4 | 0
+ 0 | 8 | 0
+ 1 | 0 | 0
+ 9 | 1 | 1
+ 5 | 9 | 1
+ 2 | 5 | 1
+ 6 | 2 | 2
+ 3 | 6 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
@@ -939,14 +939,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- | 0 | 0
- 5 | 8 | 0
- 5 | 4 | 0
- | 5 | 1
- 6 | 9 | 1
- 6 | 1 | 1
- 3 | 6 | 2
+ | 4 | 0
+ 1 | 8 | 0
+ 1 | 0 | 0
+ | 1 | 1
+ 2 | 9 | 1
+ 2 | 5 | 1
3 | 2 | 2
+ 3 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -956,14 +956,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- 0 | 0 | 0
- 8 | 8 | 0
4 | 4 | 0
- 5 | 5 | 1
- 9 | 9 | 1
+ 8 | 8 | 0
+ 0 | 0 | 0
1 | 1 | 1
- 6 | 6 | 2
+ 9 | 9 | 1
+ 5 | 5 | 1
2 | 2 | 2
+ 6 | 6 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
@@ -973,14 +973,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- 4 | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- 1 | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ 0 | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ 5 | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
@@ -990,14 +990,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -1007,14 +1007,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- 0 | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- 5 | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ 4 | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ 1 | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
@@ -1075,14 +1075,14 @@ SELECT sum(unique1) over (w range between current row and unbounded following),
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -1092,14 +1092,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 4 | 8 | 0
8 | 4 | 0
- 22 | 5 | 1
- 18 | 9 | 1
+ 4 | 8 | 0
+ 12 | 0 | 0
26 | 1 | 1
- 29 | 6 | 2
+ 18 | 9 | 1
+ 22 | 5 | 1
33 | 2 | 2
+ 29 | 6 | 2
42 | 3 | 3
38 | 7 | 3
(10 rows)
@@ -1109,14 +1109,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
35 | 3 | 3
35 | 7 | 3
(10 rows)
@@ -1126,14 +1126,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 0 | 0 | 0
- 8 | 8 | 0
4 | 4 | 0
- 17 | 5 | 1
- 21 | 9 | 1
+ 8 | 8 | 0
+ 0 | 0 | 0
13 | 1 | 1
- 33 | 6 | 2
+ 21 | 9 | 1
+ 17 | 5 | 1
29 | 2 | 2
+ 33 | 6 | 2
38 | 3 | 3
42 | 7 | 3
(10 rows)
@@ -1145,14 +1145,14 @@ FROM tenk1 WHERE unique1 < 10
WINDOW w AS (order by four range between current row and unbounded following);
first_value | nth_2 | last_value | unique1 | four
-------------+-------+------------+---------+------
- 0 | 8 | 7 | 0 | 0
- 0 | 8 | 7 | 8 | 0
- 0 | 8 | 7 | 4 | 0
- 5 | 9 | 7 | 5 | 1
- 5 | 9 | 7 | 9 | 1
- 5 | 9 | 7 | 1 | 1
- 6 | 2 | 7 | 6 | 2
- 6 | 2 | 7 | 2 | 2
+ 4 | 8 | 7 | 4 | 0
+ 4 | 8 | 7 | 8 | 0
+ 4 | 8 | 7 | 0 | 0
+ 1 | 9 | 7 | 1 | 1
+ 1 | 9 | 7 | 9 | 1
+ 1 | 9 | 7 | 5 | 1
+ 2 | 6 | 7 | 2 | 2
+ 2 | 6 | 7 | 6 | 2
3 | 7 | 7 | 3 | 3
3 | 7 | 7 | 7 | 3
(10 rows)
@@ -1349,14 +1349,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1368,14 +1368,14 @@ FROM tenk1 WHERE unique1 < 10;
-----+---------+------
| 3 | 3
| 7 | 3
- 10 | 6 | 2
10 | 2 | 2
+ 10 | 6 | 2
+ 18 | 1 | 1
18 | 9 | 1
18 | 5 | 1
- 18 | 1 | 1
- 23 | 0 | 0
- 23 | 8 | 0
23 | 4 | 0
+ 23 | 8 | 0
+ 23 | 0 | 0
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others),
@@ -1383,14 +1383,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1400,14 +1400,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1417,14 +1417,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1434,14 +1434,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1451,14 +1451,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 41 | 8 | 0
37 | 4 | 0
- 35 | 5 | 1
- 39 | 9 | 1
+ 41 | 8 | 0
+ 33 | 0 | 0
31 | 1 | 1
- 43 | 6 | 2
+ 39 | 9 | 1
+ 35 | 5 | 1
39 | 2 | 2
+ 43 | 6 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
@@ -1468,14 +1468,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 33 | 8 | 0
33 | 4 | 0
- 30 | 5 | 1
- 30 | 9 | 1
+ 33 | 8 | 0
+ 33 | 0 | 0
30 | 1 | 1
- 37 | 6 | 2
+ 30 | 9 | 1
+ 30 | 5 | 1
37 | 2 | 2
+ 37 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1521,13 +1521,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
- 47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
+ 47100 | 4800 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
- 32200 | 4500 | 01-01-2008
32200 | 4200 | 01-01-2008
+ 32200 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following),
@@ -1539,8 +1539,8 @@ select sum(salary) over (order by enroll_date desc range between '1 year'::inter
36100 | 3500 | 12-10-2007
47100 | 5200 | 08-15-2007
47100 | 4800 | 08-08-2007
- 47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
+ 47100 | 4800 | 08-01-2007
38400 | 3900 | 12-23-2006
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
@@ -1555,8 +1555,8 @@ select sum(salary) over (order by enroll_date desc range between '1 year'::inter
| 3500 | 12-10-2007
| 5200 | 08-15-2007
| 4800 | 08-08-2007
- | 4800 | 08-01-2007
| 5200 | 08-01-2007
+ | 4800 | 08-01-2007
| 3900 | 12-23-2006
| 5000 | 10-01-2006
| 6000 | 10-01-2006
@@ -1569,13 +1569,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
29900 | 5000 | 10-01-2006
28900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
- 42300 | 4800 | 08-01-2007
41900 | 5200 | 08-01-2007
+ 42300 | 4800 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
- 27700 | 4500 | 01-01-2008
28000 | 4200 | 01-01-2008
+ 27700 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
@@ -1585,13 +1585,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
23900 | 5000 | 10-01-2006
23900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
- 37100 | 4800 | 08-01-2007
37100 | 5200 | 08-01-2007
+ 37100 | 4800 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
- 23500 | 4500 | 01-01-2008
23500 | 4200 | 01-01-2008
+ 23500 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
@@ -1601,13 +1601,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
28900 | 5000 | 10-01-2006
29900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
- 41900 | 4800 | 08-01-2007
42300 | 5200 | 08-01-2007
+ 41900 | 4800 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
- 28000 | 4500 | 01-01-2008
27700 | 4200 | 01-01-2008
+ 28000 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by salary range between 1000 preceding and 1000 following),
@@ -1692,13 +1692,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4200 | 4500 | 01-01-2008
- 5000 | 4200 | 4200 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
+ 5000 | 4500 | 4200 | 01-01-2008
+ 5000 | 4500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1711,13 +1711,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4500 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 4200 | 4200 | 01-01-2008
+ 5000 | 4500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1730,13 +1730,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
3900 | 5200 | 5000 | 10-01-2006
3900 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 3500 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 3500 | 4200 | 01-01-2008
+ 5000 | 3500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1749,13 +1749,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
6000 | 5200 | 5000 | 10-01-2006
5000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4200 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 4500 | 4200 | 01-01-2008
+ 5000 | 4200 | 4500 | 01-01-2008
(10 rows)
-- RANGE offset PRECEDING/FOLLOWING with null values
@@ -1810,8 +1810,8 @@ window w as
(order by x desc nulls first range between 2 preceding and 2 following);
x | y | first_value | last_value
---+----+-------------+------------
- | 43 | 43 | 42
- | 42 | 43 | 42
+ | 42 | 42 | 43
+ | 43 | 42 | 43
5 | 5 | 5 | 3
4 | 4 | 5 | 2
3 | 3 | 5 | 1
@@ -2392,10 +2392,10 @@ window w as (order by f_time desc range between
10 | 20:00:00 | 10 | 8
9 | 19:00:00 | 10 | 7
8 | 18:00:00 | 9 | 7
- 7 | 17:00:00 | 8 | 5
- 6 | 15:00:00 | 6 | 3
- 5 | 15:00:00 | 6 | 3
- 4 | 14:00:00 | 6 | 2
+ 7 | 17:00:00 | 8 | 6
+ 5 | 15:00:00 | 5 | 3
+ 6 | 15:00:00 | 5 | 3
+ 4 | 14:00:00 | 5 | 2
3 | 13:00:00 | 4 | 1
2 | 12:00:00 | 3 | 1
1 | 11:00:00 | 2 | 1
@@ -2428,10 +2428,10 @@ window w as (order by f_timetz desc range between
10 | 20:00:00+01 | 10 | 8
9 | 19:00:00+01 | 10 | 7
8 | 18:00:00+01 | 9 | 7
- 7 | 17:00:00+01 | 8 | 5
- 6 | 15:00:00+01 | 6 | 3
- 5 | 15:00:00+01 | 6 | 3
- 4 | 14:00:00+01 | 6 | 2
+ 7 | 17:00:00+01 | 8 | 6
+ 5 | 15:00:00+01 | 5 | 3
+ 6 | 15:00:00+01 | 5 | 3
+ 4 | 14:00:00+01 | 5 | 2
3 | 13:00:00+01 | 4 | 1
2 | 12:00:00+01 | 3 | 1
1 | 11:00:00+01 | 2 | 1
@@ -2465,9 +2465,9 @@ window w as (order by f_interval desc range between
9 | @ 9 years | 10 | 8
8 | @ 8 years | 9 | 7
7 | @ 7 years | 8 | 7
- 6 | @ 5 years | 6 | 4
- 5 | @ 5 years | 6 | 4
- 4 | @ 4 years | 6 | 3
+ 5 | @ 5 years | 5 | 4
+ 6 | @ 5 years | 5 | 4
+ 4 | @ 4 years | 5 | 3
3 | @ 3 years | 4 | 2
2 | @ 2 years | 3 | 1
1 | @ 1 year | 2 | 1
@@ -2503,10 +2503,10 @@ window w as (order by f_timestamptz desc range between
7 | Wed Oct 19 02:23:54 2005 PDT | 8 | 6
6 | Tue Oct 19 02:23:54 2004 PDT | 7 | 5
5 | Sun Oct 19 02:23:54 2003 PDT | 6 | 4
- 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 2
- 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
+ 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 3
2 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
- 1 | Thu Oct 19 02:23:54 2000 PDT | 3 | 1
+ 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
+ 1 | Thu Oct 19 02:23:54 2000 PDT | 2 | 1
(10 rows)
select id, f_timestamp, first_value(id) over w, last_value(id) over w
@@ -2539,10 +2539,10 @@ window w as (order by f_timestamp desc range between
7 | Wed Oct 19 10:23:54 2005 | 8 | 6
6 | Tue Oct 19 10:23:54 2004 | 7 | 5
5 | Sun Oct 19 10:23:54 2003 | 6 | 4
- 4 | Sat Oct 19 10:23:54 2002 | 5 | 2
- 3 | Fri Oct 19 10:23:54 2001 | 4 | 1
+ 4 | Sat Oct 19 10:23:54 2002 | 5 | 3
2 | Fri Oct 19 10:23:54 2001 | 4 | 1
- 1 | Thu Oct 19 10:23:54 2000 | 3 | 1
+ 3 | Fri Oct 19 10:23:54 2001 | 4 | 1
+ 1 | Thu Oct 19 10:23:54 2000 | 2 | 1
(10 rows)
-- RANGE offset PRECEDING/FOLLOWING error cases
@@ -2588,14 +2588,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and c
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 12 | 8 | 0
12 | 4 | 0
- 27 | 5 | 1
- 27 | 9 | 1
+ 12 | 8 | 0
+ 12 | 0 | 0
27 | 1 | 1
- 35 | 6 | 2
+ 27 | 9 | 1
+ 27 | 5 | 1
35 | 2 | 2
+ 35 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -2605,14 +2605,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and u
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
45 | 1 | 1
- 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -2622,14 +2622,14 @@ SELECT sum(unique1) over (order by four groups between current row and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -2639,14 +2639,14 @@ SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
45 | 1 | 1
- 33 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
33 | 2 | 2
+ 33 | 6 | 2
18 | 3 | 3
18 | 7 | 3
(10 rows)
@@ -2656,14 +2656,14 @@ SELECT sum(unique1) over (order by four groups between 1 following and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 33 | 8 | 0
33 | 4 | 0
- 18 | 5 | 1
- 18 | 9 | 1
+ 33 | 8 | 0
+ 33 | 0 | 0
18 | 1 | 1
- 10 | 6 | 2
+ 18 | 9 | 1
+ 18 | 5 | 1
10 | 2 | 2
+ 10 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -2673,14 +2673,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and 2
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 35 | 0 | 0
- 35 | 8 | 0
35 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 35 | 8 | 0
+ 35 | 0 | 0
45 | 1 | 1
- 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -2690,14 +2690,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 precedi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -2707,14 +2707,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 27 | 0 | 0
- 27 | 8 | 0
27 | 4 | 0
- 35 | 5 | 1
- 35 | 9 | 1
+ 27 | 8 | 0
+ 27 | 0 | 0
35 | 1 | 1
- 45 | 6 | 2
+ 35 | 9 | 1
+ 35 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
33 | 3 | 3
33 | 7 | 3
(10 rows)
@@ -2724,14 +2724,14 @@ SELECT sum(unique1) over (order by four groups between 0 preceding and 0 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 12 | 8 | 0
12 | 4 | 0
- 15 | 5 | 1
- 15 | 9 | 1
+ 12 | 8 | 0
+ 12 | 0 | 0
15 | 1 | 1
- 8 | 6 | 2
+ 15 | 9 | 1
+ 15 | 5 | 1
8 | 2 | 2
+ 8 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -2741,14 +2741,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 27 | 0 | 0
- 19 | 8 | 0
23 | 4 | 0
- 30 | 5 | 1
- 26 | 9 | 1
+ 19 | 8 | 0
+ 27 | 0 | 0
34 | 1 | 1
- 39 | 6 | 2
+ 26 | 9 | 1
+ 30 | 5 | 1
43 | 2 | 2
+ 39 | 6 | 2
30 | 3 | 3
26 | 7 | 3
(10 rows)
@@ -2758,14 +2758,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 15 | 0 | 0
- 15 | 8 | 0
15 | 4 | 0
- 20 | 5 | 1
- 20 | 9 | 1
+ 15 | 8 | 0
+ 15 | 0 | 0
20 | 1 | 1
- 37 | 6 | 2
+ 20 | 9 | 1
+ 20 | 5 | 1
37 | 2 | 2
+ 37 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -2775,14 +2775,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 15 | 0 | 0
- 23 | 8 | 0
19 | 4 | 0
- 25 | 5 | 1
- 29 | 9 | 1
+ 23 | 8 | 0
+ 15 | 0 | 0
21 | 1 | 1
- 43 | 6 | 2
+ 29 | 9 | 1
+ 25 | 5 | 1
39 | 2 | 2
+ 43 | 6 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
@@ -2863,14 +2863,14 @@ select first_value(salary) over(order by enroll_date groups between 1 preceding
-------------+------+-----------+--------+-------------
5000 | 6000 | 5000 | 5000 | 10-01-2006
5000 | 3900 | 5000 | 6000 | 10-01-2006
- 5000 | 4800 | 5000 | 3900 | 12-23-2006
- 3900 | 5200 | 3900 | 4800 | 08-01-2007
+ 5000 | 5200 | 5000 | 3900 | 12-23-2006
3900 | 4800 | 3900 | 5200 | 08-01-2007
- 4800 | 5200 | 4800 | 4800 | 08-08-2007
+ 3900 | 4800 | 3900 | 4800 | 08-01-2007
+ 5200 | 5200 | 5200 | 4800 | 08-08-2007
4800 | 3500 | 4800 | 5200 | 08-15-2007
- 5200 | 4500 | 5200 | 3500 | 12-10-2007
- 3500 | 4200 | 3500 | 4500 | 01-01-2008
- 3500 | | 3500 | 4200 | 01-01-2008
+ 5200 | 4200 | 5200 | 3500 | 12-10-2007
+ 3500 | 4500 | 3500 | 4200 | 01-01-2008
+ 3500 | | 3500 | 4500 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following),
@@ -2880,14 +2880,14 @@ select last_value(salary) over(order by enroll_date groups between 1 preceding a
------------+------+--------+-------------
3900 | | 5000 | 10-01-2006
3900 | 5000 | 6000 | 10-01-2006
- 5200 | 6000 | 3900 | 12-23-2006
- 4800 | 3900 | 4800 | 08-01-2007
- 4800 | 4800 | 5200 | 08-01-2007
- 5200 | 5200 | 4800 | 08-08-2007
+ 4800 | 6000 | 3900 | 12-23-2006
+ 4800 | 3900 | 5200 | 08-01-2007
+ 4800 | 5200 | 4800 | 08-01-2007
+ 5200 | 4800 | 4800 | 08-08-2007
3500 | 4800 | 5200 | 08-15-2007
- 4200 | 5200 | 3500 | 12-10-2007
- 4200 | 3500 | 4500 | 01-01-2008
- 4200 | 4500 | 4200 | 01-01-2008
+ 4500 | 5200 | 3500 | 12-10-2007
+ 4500 | 3500 | 4200 | 01-01-2008
+ 4500 | 4200 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date groups between 1 following and 3 following
@@ -2900,14 +2900,14 @@ select first_value(salary) over(order by enroll_date groups between 1 following
-------------+------+-----------+--------+-------------
3900 | 6000 | 3900 | 5000 | 10-01-2006
3900 | 3900 | 3900 | 6000 | 10-01-2006
- 4800 | 4800 | 4800 | 3900 | 12-23-2006
- 4800 | 5200 | 4800 | 4800 | 08-01-2007
+ 5200 | 5200 | 5200 | 3900 | 12-23-2006
4800 | 4800 | 4800 | 5200 | 08-01-2007
+ 4800 | 4800 | 4800 | 4800 | 08-01-2007
5200 | 5200 | 5200 | 4800 | 08-08-2007
3500 | 3500 | 3500 | 5200 | 08-15-2007
- 4500 | 4500 | 4500 | 3500 | 12-10-2007
- | 4200 | | 4500 | 01-01-2008
- | | | 4200 | 01-01-2008
+ 4200 | 4200 | 4200 | 3500 | 12-10-2007
+ | 4500 | | 4200 | 01-01-2008
+ | | | 4500 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 following and 3 following
@@ -2919,13 +2919,13 @@ select last_value(salary) over(order by enroll_date groups between 1 following a
4800 | | 5000 | 10-01-2006
4800 | 5000 | 6000 | 10-01-2006
5200 | 6000 | 3900 | 12-23-2006
- 3500 | 3900 | 4800 | 08-01-2007
- 3500 | 4800 | 5200 | 08-01-2007
- 4200 | 5200 | 4800 | 08-08-2007
- 4200 | 4800 | 5200 | 08-15-2007
- 4200 | 5200 | 3500 | 12-10-2007
- | 3500 | 4500 | 01-01-2008
- | 4500 | 4200 | 01-01-2008
+ 3500 | 3900 | 5200 | 08-01-2007
+ 3500 | 5200 | 4800 | 08-01-2007
+ 4500 | 4800 | 4800 | 08-08-2007
+ 4500 | 4800 | 5200 | 08-15-2007
+ 4500 | 5200 | 3500 | 12-10-2007
+ | 3500 | 4200 | 01-01-2008
+ | 4200 | 4500 | 01-01-2008
(10 rows)
-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS
@@ -3407,7 +3407,7 @@ WHERE first_emp = 1 OR last_emp = 1;
depname | empno | salary | enroll_date | first_emp | last_emp
-----------+-------+--------+-------------+-----------+----------
develop | 8 | 6000 | 10-01-2006 | 1 | 5
- develop | 7 | 4200 | 01-01-2008 | 5 | 1
+ develop | 7 | 4200 | 01-01-2008 | 4 | 1
personnel | 2 | 3900 | 12-23-2006 | 1 | 2
personnel | 5 | 3500 | 12-10-2007 | 2 | 1
sales | 1 | 5000 | 10-01-2006 | 1 | 3
--
2.35.1
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-10 21:44 Thomas Munro <[email protected]>
parent: Thomas Munro <[email protected]>
1 sibling, 4 replies; 31+ messages in thread
From: Thomas Munro @ 2022-04-10 21:44 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; David Rowley <[email protected]>
Hi,
David Rowley privately reported a performance regression when sorting
single ints with a lot of duplicates, in a case that previously hit
qsort_ssup() but now hits qsort_tuple_int32() and then has to call the
tiebreaker comparator. Note that this comes up only for sorts in a
query, not for eg index builds which always have to tiebreak on item
ptr. I don't have data right now but that'd likely be due to:
+ * XXX: For now, there is no specialization for cases where datum1 is
+ * authoritative and we don't even need to fall back to a callback at all (that
+ * would be true for types like int4/int8/timestamp/date, but not true for
+ * abbreviations of text or multi-key sorts. There could be! Is it worth it?
Upthread we were discussing which variations it'd be worth investing
extra text segment space on to gain speedup and we put those hard
decisions off for future work, but on reflection, we probably should
tackle this particular point to avoid a regression. I think something
like the attached achieves that (draft, not tested much yet, could
perhaps find a tidier way to code the decision tree). In short:
variants qsort_tuple_{int32,signed,unsigned}() no longer fall back,
but new variants qsort_tuple_{int32,signed,unsigned}_tiebreak() do.
We should perhaps also reconsider the other XXX comment about finding
a way to skip the retest of column 1 in the tiebreak comparator.
Perhaps you'd just install a different comparetup function, eg
comparetup_index_btree_tail (which would sharing code), so no need to
multiply specialisations for that.
Planning to look at this more closely after I've sorted out some other
problems, but thought I'd post this draft/problem report early in case
John or others have thoughts or would like to run some experiments.
Attachments:
[text/x-patch] 0001-Specialize-tuplesort-for-keys-without-tiebreaker.patch (7.5K, ../../CA+hUKGJRbzaAOUtBUcjF5hLtaSHnJUqXmtiaLEoi53zeWSizeA@mail.gmail.com/2-0001-Specialize-tuplesort-for-keys-without-tiebreaker.patch)
download | inline diff:
From 3ff95aedb1065393a0ae1496cedb463bc998a329 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email protected]>
Date: Wed, 6 Apr 2022 11:38:31 +1200
Subject: [PATCH] Specialize tuplesort for keys without tiebreaker.
Commit 69749243 noted a future opportunity to avoid calling the
comparator function in the case of single column non-abbreviated sort,
where datum1 alone is enough to decide the order.
David Rowley reported a performance regression in single-column integer
sorts with a lot of duplicates, where previously the qsort_ssup()
specialization was reached, due to extra calls to the comparator. It
seems like a good idea to fix that, so let's complete that TODO item
now.
XXX WIP, is the if-then-else getting too ugly, maybe switch to a table of
functions to search?
XXX needs testing, experiments
---
src/backend/utils/sort/tuplesort.c | 110 +++++++++++++++++++++++------
1 file changed, 89 insertions(+), 21 deletions(-)
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 571fb95532..dc16e6c242 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -312,6 +312,14 @@ struct Tuplesortstate
*/
bool haveDatum1;
+ /*
+ * Whether the comparator function must always be called for tuples with
+ * equal datum1. This should be set to true if the sorting has extra
+ * 'invisible' sort order beyond the regular keys, to disable optimizations
+ * that would otherwise skip the function call.
+ */
+ bool alwaysTiebreak;
+
/*
* This array holds the tuples now in sort memory. If we are in state
* INITIAL, the tuples are in no particular order; if we are in state
@@ -682,11 +690,6 @@ static void tuplesort_updatemax(Tuplesortstate *state);
*
* XXX: For now, these fall back to comparator functions that will compare the
* leading datum a second time.
- *
- * XXX: For now, there is no specialization for cases where datum1 is
- * authoritative and we don't even need to fall back to a callback at all (that
- * would be true for types like int4/int8/timestamp/date, but not true for
- * abbreviations of text or multi-key sorts. There could be! Is it worth it?
*/
/* Used if first key's comparator is ssup_datum_unsigned_compare */
@@ -740,10 +743,12 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
* qsort_ssup() is specialized for the case where the comparetup function
* reduces to ApplySortComparator(), that is single-key MinimalTuple sorts
* and Datum sorts. qsort_tuple_{unsigned,signed,int32} are specialized for
- * common comparison functions on pass-by-value leading datums.
+ * common comparison functions on pass-by-value leading datums. The _tiebreak
+ * variants are for when it is necessary to call the comparator function even
+ * if datum1 is equal.
*/
-#define ST_SORT qsort_tuple_unsigned
+#define ST_SORT qsort_tuple_unsigned_tiebreak
#define ST_ELEMENT_TYPE SortTuple
#define ST_COMPARE(a, b, state) qsort_tuple_unsigned_compare(a, b, state)
#define ST_COMPARE_ARG_TYPE Tuplesortstate
@@ -752,7 +757,7 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
#define ST_DEFINE
#include "lib/sort_template.h"
-#define ST_SORT qsort_tuple_signed
+#define ST_SORT qsort_tuple_signed_tiebreak
#define ST_ELEMENT_TYPE SortTuple
#define ST_COMPARE(a, b, state) qsort_tuple_signed_compare(a, b, state)
#define ST_COMPARE_ARG_TYPE Tuplesortstate
@@ -761,7 +766,7 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
#define ST_DEFINE
#include "lib/sort_template.h"
-#define ST_SORT qsort_tuple_int32
+#define ST_SORT qsort_tuple_int32_tiebreak
#define ST_ELEMENT_TYPE SortTuple
#define ST_COMPARE(a, b, state) qsort_tuple_int32_compare(a, b, state)
#define ST_COMPARE_ARG_TYPE Tuplesortstate
@@ -770,6 +775,33 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
#define ST_DEFINE
#include "lib/sort_template.h"
+#define ST_SORT qsort_tuple_unsigned
+#define ST_ELEMENT_TYPE SortTuple
+#define ST_COMPARE(a, b, state) ApplyUnsignedSortComparator(a->datum1, a->isnull1, b->datum1, b->isnull1, &state->sortKeys[0])
+#define ST_COMPARE_ARG_TYPE Tuplesortstate
+#define ST_CHECK_FOR_INTERRUPTS
+#define ST_SCOPE static
+#define ST_DEFINE
+#include "lib/sort_template.h"
+
+#define ST_SORT qsort_tuple_signed
+#define ST_ELEMENT_TYPE SortTuple
+#define ST_COMPARE(a, b, state) ApplySignedSortComparator(a->datum1, a->isnull1, b->datum1, b->isnull1, &state->sortKeys[0])
+#define ST_COMPARE_ARG_TYPE Tuplesortstate
+#define ST_CHECK_FOR_INTERRUPTS
+#define ST_SCOPE static
+#define ST_DEFINE
+#include "lib/sort_template.h"
+
+#define ST_SORT qsort_tuple_int32
+#define ST_ELEMENT_TYPE SortTuple
+#define ST_COMPARE(a, b, state) ApplyInt32SortComparator(a->datum1, a->isnull1, b->datum1, b->isnull1, &state->sortKeys[0])
+#define ST_COMPARE_ARG_TYPE Tuplesortstate
+#define ST_CHECK_FOR_INTERRUPTS
+#define ST_SCOPE static
+#define ST_DEFINE
+#include "lib/sort_template.h"
+
#define ST_SORT qsort_tuple
#define ST_ELEMENT_TYPE SortTuple
#define ST_COMPARE_RUNTIME_POINTER
@@ -1215,6 +1247,12 @@ tuplesort_begin_index_btree(Relation heapRel,
state->abbrevNext = 10;
state->haveDatum1 = true;
+ /*
+ * Since we sort equal keys by ItemPointer, we can't skip the comparator
+ * call.
+ */
+ state->alwaysTiebreak = true;
+
state->heapRel = heapRel;
state->indexRel = indexRel;
state->enforceUnique = enforceUnique;
@@ -3632,28 +3670,58 @@ tuplesort_sort_memtuples(Tuplesortstate *state)
*/
if (state->haveDatum1 && state->sortKeys)
{
+ bool tiebreak;
+
+ /*
+ * If there's only a single key, and the value in datum1 is not a
+ * lossy abbreviation, then we don't need to call a tie-breaking
+ * comparator function for equal datum1 values unless explicitly
+ * asked to do so (eg btrees).
+ */
+ tiebreak = state->nKeys > 1 ||
+ state->sortKeys[0].abbrev_converter ||
+ state->alwaysTiebreak;
+
if (state->sortKeys[0].comparator == ssup_datum_unsigned_cmp)
{
- elog(DEBUG1, "qsort_tuple_unsigned");
- qsort_tuple_unsigned(state->memtuples,
- state->memtupcount,
- state);
+ elog(DEBUG1, "qsort_tuple_unsigned%s",
+ tiebreak ? "_tiebreak" : "");
+ if (tiebreak)
+ qsort_tuple_unsigned_tiebreak(state->memtuples,
+ state->memtupcount,
+ state);
+ else
+ qsort_tuple_unsigned(state->memtuples,
+ state->memtupcount,
+ state);
return;
}
else if (state->sortKeys[0].comparator == ssup_datum_signed_cmp)
{
- elog(DEBUG1, "qsort_tuple_signed");
- qsort_tuple_signed(state->memtuples,
- state->memtupcount,
- state);
+ elog(DEBUG1, "qsort_tuple_signed%s",
+ tiebreak ? "_tiebreak" : "");
+ if (tiebreak)
+ qsort_tuple_signed_tiebreak(state->memtuples,
+ state->memtupcount,
+ state);
+ else
+ qsort_tuple_signed(state->memtuples,
+ state->memtupcount,
+ state);
return;
}
else if (state->sortKeys[0].comparator == ssup_datum_int32_cmp)
{
- elog(DEBUG1, "qsort_tuple_int32");
- qsort_tuple_int32(state->memtuples,
- state->memtupcount,
- state);
+ elog(DEBUG1, "qsort_tuple_int32%s",
+ tiebreak ? "_tiebreak" : "");
+ if (tiebreak)
+ qsort_tuple_int32_tiebreak(state->memtuples,
+ state->memtupcount,
+ state);
+ else
+ qsort_tuple_int32(state->memtuples,
+ state->memtupcount,
+ state);
return;
}
}
--
2.30.2
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-10 21:54 Peter Geoghegan <[email protected]>
parent: Thomas Munro <[email protected]>
3 siblings, 1 reply; 31+ messages in thread
From: Peter Geoghegan @ 2022-04-10 21:54 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; John Naylor <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; David Rowley <[email protected]>
On Sun, Apr 10, 2022 at 2:44 PM Thomas Munro <[email protected]> wrote:
> David Rowley privately reported a performance regression when sorting
> single ints with a lot of duplicates, in a case that previously hit
> qsort_ssup() but now hits qsort_tuple_int32() and then has to call the
> tiebreaker comparator.
That's not good.
The B&M quicksort implementation that we adopted is generally
extremely fast for that case, since it uses 3 way partitioning (based
on the Dutch National Flag algorithm). This essentially makes sorting
large groups of duplicates take only linear time (not linearithmic
time).
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-10 22:34 David Rowley <[email protected]>
parent: Thomas Munro <[email protected]>
3 siblings, 0 replies; 31+ messages in thread
From: David Rowley @ 2022-04-10 22:34 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Mon, 11 Apr 2022 at 09:44, Thomas Munro <[email protected]> wrote:
> David Rowley privately reported a performance regression when sorting
> single ints with a lot of duplicates, in a case that previously hit
> qsort_ssup() but now hits qsort_tuple_int32() and then has to call the
> tiebreaker comparator. Note that this comes up only for sorts in a
> query, not for eg index builds which always have to tiebreak on item
> ptr. I don't have data right now but that'd likely be due to:
Yeah, I noticed this when running some sort benchmarks to compare v14
with master (as of Thursday last week).
The biggest slowdown I saw was the test that sorted 1 million tuples
on a BIGINT column with 100 distinct values. The test in question
does sorts on the same column each time, but continually adds columns,
which I was doing to check how wider tuples changed the performance
(this was for the exercise of 40af10b57 rather than this work).
With this particular test, v15 is about 15% *slower* than v14. I
didn't know what to blame at first, so I tried commenting out the sort
specialisations and got the results in the red bars in the graph. This
made it about 7.5% *faster* than v14. So looks like this patch is to
blame. I then hacked the comparator function that's used in the
specialisations for BIGINT to comment out the tiebreak to remove the
indirect function call, which happens to do nothing in this 1 column
sort case. The aim here was to get an idea what the performance would
be if there was a specialisation for single column sorts. That's the
yellow bars, which show about 10% *faster* than master.
Attachments:
[application/vnd.oasis.opendocument.spreadsheet] sorting_v14_vs_v15.ods (79.1K, ../../CAApHDvokMee00Ca71znFW4=9B1Z7ZG2kBTUZsuewZ24cp74zOw@mail.gmail.com/2-sorting_v14_vs_v15.ods)
download
[image/png] 1m_tup_mod_100.png (93.4K, ../../CAApHDvokMee00Ca71znFW4=9B1Z7ZG2kBTUZsuewZ24cp74zOw@mail.gmail.com/3-1m_tup_mod_100.png)
download | view image
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-11 00:25 David Rowley <[email protected]>
parent: Thomas Munro <[email protected]>
3 siblings, 0 replies; 31+ messages in thread
From: David Rowley @ 2022-04-11 00:25 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Mon, 11 Apr 2022 at 09:44, Thomas Munro <[email protected]> wrote:
> David Rowley privately reported a performance regression when sorting
> single ints with a lot of duplicates, in a case that previously hit
> qsort_ssup() but now hits qsort_tuple_int32() and then has to call the
> tiebreaker comparator. Note that this comes up only for sorts in a
> query, not for eg index builds which always have to tiebreak on item
> ptr. I don't have data right now but that'd likely be due to:
I've now added this as an open item for v15.
David
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-11 10:11 John Naylor <[email protected]>
parent: Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: John Naylor @ 2022-04-11 10:11 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; David Rowley <[email protected]>
On Mon, Apr 11, 2022 at 5:34 AM David Rowley <[email protected]> wrote:
> With this particular test, v15 is about 15% *slower* than v14. I
> didn't know what to blame at first, so I tried commenting out the sort
> specialisations and got the results in the red bars in the graph. This
> made it about 7.5% *faster* than v14. So looks like this patch is to
> blame. I then hacked the comparator function that's used in the
> specialisations for BIGINT to comment out the tiebreak to remove the
> indirect function call, which happens to do nothing in this 1 column
> sort case. The aim here was to get an idea what the performance would
> be if there was a specialisation for single column sorts. That's the
> yellow bars, which show about 10% *faster* than master.
Thanks for investigating! (I assume you meant 10% faster than v14?)
On Mon, Apr 11, 2022 at 4:55 AM Peter Geoghegan <[email protected]> wrote:
> The B&M quicksort implementation that we adopted is generally
> extremely fast for that case, since it uses 3 way partitioning (based
> on the Dutch National Flag algorithm). This essentially makes sorting
> large groups of duplicates take only linear time (not linearithmic
> time).
In the below thread, I wondered if it still counts as extremely fast
nowadays. I hope to give an answer to that during next cycle. Relevant
to the open item, the paper linked there has a variety of
low-cardinality cases. I'll incorporate them in a round of tests soon.
https://www.postgresql.org/message-id/[email protected]...
On Mon, Apr 11, 2022 at 4:44 AM Thomas Munro <[email protected]> wrote:
> Upthread we were discussing which variations it'd be worth investing
> extra text segment space on to gain speedup and we put those hard
> decisions off for future work, but on reflection, we probably should
> tackle this particular point to avoid a regression. I think something
> like the attached achieves that (draft, not tested much yet, could
> perhaps find a tidier way to code the decision tree). In short:
> variants qsort_tuple_{int32,signed,unsigned}() no longer fall back,
> but new variants qsort_tuple_{int32,signed,unsigned}_tiebreak() do.
Looks good at a glance, I will get some numbers after modifying my test scripts.
> We should perhaps also reconsider the other XXX comment about finding
> a way to skip the retest of column 1 in the tiebreak comparator.
> Perhaps you'd just install a different comparetup function, eg
> comparetup_index_btree_tail (which would sharing code), so no need to
> multiply specialisations for that.
If we need to add these cases to avoid regression, it makes sense to
make them work as well as we reasonably can.
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-12 00:40 David Rowley <[email protected]>
parent: John Naylor <[email protected]>
0 siblings, 0 replies; 31+ messages in thread
From: David Rowley @ 2022-04-12 00:40 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Mon, 11 Apr 2022 at 22:11, John Naylor <[email protected]> wrote:
>
> On Mon, Apr 11, 2022 at 5:34 AM David Rowley <[email protected]> wrote:
>
> > With this particular test, v15 is about 15% *slower* than v14. I
> > didn't know what to blame at first, so I tried commenting out the sort
> > specialisations and got the results in the red bars in the graph. This
> > made it about 7.5% *faster* than v14. So looks like this patch is to
> > blame. I then hacked the comparator function that's used in the
> > specialisations for BIGINT to comment out the tiebreak to remove the
> > indirect function call, which happens to do nothing in this 1 column
> > sort case. The aim here was to get an idea what the performance would
> > be if there was a specialisation for single column sorts. That's the
> > yellow bars, which show about 10% *faster* than master.
>
> Thanks for investigating! (I assume you meant 10% faster than v14?)
Yes, I did mean to say v14. (I'm too used to comparing everything to master)
David
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-12 00:58 David Rowley <[email protected]>
parent: Thomas Munro <[email protected]>
3 siblings, 2 replies; 31+ messages in thread
From: David Rowley @ 2022-04-12 00:58 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; John Naylor <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Mon, 11 Apr 2022 at 09:44, Thomas Munro <[email protected]> wrote:
> Planning to look at this more closely after I've sorted out some other
> problems, but thought I'd post this draft/problem report early in case
> John or others have thoughts or would like to run some experiments.
Thanks for putting the patch together.
I had a look at the patch and I wondered if we really need to add an
entire dimension of sort functions for just this case. My thought
process here is that when I look at a function such as
ApplySignedSortComparator(), I think that it might be better to save
adding another dimension for a sort case such as a column that does
not contain any NULLs. There's quite a bit more branching saved from
getting rid of NULL tests there than what we could save by checking if
we need to call the tiebreaker function in a function like
qsort_tuple_signed_compare().
I didn't really know what the performance implications would be of
checking an extra flag would be, so I very quickly put a patch
together and ran the benchmarks.
The 4GB work_mem 1 million tuple test with values MOD 100 comes out as:
Thomas' patch: 10.13% faster than v14
My patch: 9.48% faster than v14
master: 15.62% *slower* than v14
So it does seem like we can fix the regression in a more simple way.
We could then maybe do some more meaningful performance tests during
the v16 cycle to explore the most useful dimension to add that gains
the most performance. Perhaps that's NULLs, or maybe it's something
else.
I've attached the patch I tested. It was thrown together very quickly
just to try out the performance. If it's interesting I can polish it
up a bit. If not, I didn't waste too much time.
David
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 571fb95532..8c4ca8c17e 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -312,6 +312,12 @@ struct Tuplesortstate
*/
bool haveDatum1;
+ /*
+ * When true, specialized sort comparators are able to skip calling the
+ * tiebreak function when sort keys compare equally.
+ */
+ bool oneKeySort;
+
/*
* This array holds the tuples now in sort memory. If we are in state
* INITIAL, the tuples are in no particular order; if we are in state
@@ -698,7 +704,7 @@ qsort_tuple_unsigned_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyUnsignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+ if (compare != 0 || state->oneKeySort)
return compare;
return state->comparetup(a, b, state);
@@ -713,7 +719,7 @@ qsort_tuple_signed_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplySignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+ if (compare != 0 || state->oneKeySort)
return compare;
return state->comparetup(a, b, state);
@@ -728,7 +734,7 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyInt32SortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+ if (compare != 0 || state->oneKeySort)
return compare;
return state->comparetup(a, b, state);
@@ -959,6 +965,10 @@ tuplesort_begin_batch(Tuplesortstate *state)
state->tapeset = NULL;
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
state->memtupcount = 0;
/*
@@ -1056,6 +1066,10 @@ tuplesort_begin_heap(TupleDesc tupDesc,
PrepareSortSupportFromOrderingOp(sortOperators[i], sortKey);
}
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
/*
* The "onlyKey" optimization cannot be used with abbreviated keys, since
* tie-breaker comparisons may be required. Typically, the optimization
@@ -1167,6 +1181,10 @@ tuplesort_begin_cluster(TupleDesc tupDesc,
PrepareSortSupportFromIndexRel(indexRel, strategy, sortKey);
}
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
pfree(indexScanKey);
MemoryContextSwitchTo(oldcontext);
@@ -1248,6 +1266,12 @@ tuplesort_begin_index_btree(Relation heapRel,
PrepareSortSupportFromIndexRel(indexRel, strategy, sortKey);
}
+ /*
+ * Since we sort equal keys by ItemPointer, we can't skip the comparator
+ * call.
+ */
+ state->oneKeySort = false;
+
pfree(indexScanKey);
MemoryContextSwitchTo(oldcontext);
@@ -1297,6 +1321,7 @@ tuplesort_begin_index_hash(Relation heapRel,
state->high_mask = high_mask;
state->low_mask = low_mask;
state->max_buckets = max_buckets;
+ state->oneKeySort = false;
MemoryContextSwitchTo(oldcontext);
@@ -1356,6 +1381,10 @@ tuplesort_begin_index_gist(Relation heapRel,
PrepareSortSupportFromGistIndexRel(indexRel, sortKey);
}
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
MemoryContextSwitchTo(oldcontext);
return state;
@@ -1432,6 +1461,9 @@ tuplesort_begin_datum(Oid datumType, Oid sortOperator, Oid sortCollation,
if (!state->sortKeys->abbrev_converter)
state->onlyKey = state->sortKeys;
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = !state->sortKeys->abbrev_converter;
+
MemoryContextSwitchTo(oldcontext);
return state;
Attachments:
[text/plain] skip_calling_sort_tiebreak_when_not_needed.patch (3.9K, ../../CAApHDvoO0sJiGgqK3vx=XX4TF6qik3YbLT-XAfvQLW6uq=t-NQ@mail.gmail.com/2-skip_calling_sort_tiebreak_when_not_needed.patch)
download | inline diff:
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 571fb95532..8c4ca8c17e 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -312,6 +312,12 @@ struct Tuplesortstate
*/
bool haveDatum1;
+ /*
+ * When true, specialized sort comparators are able to skip calling the
+ * tiebreak function when sort keys compare equally.
+ */
+ bool oneKeySort;
+
/*
* This array holds the tuples now in sort memory. If we are in state
* INITIAL, the tuples are in no particular order; if we are in state
@@ -698,7 +704,7 @@ qsort_tuple_unsigned_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyUnsignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+ if (compare != 0 || state->oneKeySort)
return compare;
return state->comparetup(a, b, state);
@@ -713,7 +719,7 @@ qsort_tuple_signed_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplySignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+ if (compare != 0 || state->oneKeySort)
return compare;
return state->comparetup(a, b, state);
@@ -728,7 +734,7 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyInt32SortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+ if (compare != 0 || state->oneKeySort)
return compare;
return state->comparetup(a, b, state);
@@ -959,6 +965,10 @@ tuplesort_begin_batch(Tuplesortstate *state)
state->tapeset = NULL;
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
state->memtupcount = 0;
/*
@@ -1056,6 +1066,10 @@ tuplesort_begin_heap(TupleDesc tupDesc,
PrepareSortSupportFromOrderingOp(sortOperators[i], sortKey);
}
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
/*
* The "onlyKey" optimization cannot be used with abbreviated keys, since
* tie-breaker comparisons may be required. Typically, the optimization
@@ -1167,6 +1181,10 @@ tuplesort_begin_cluster(TupleDesc tupDesc,
PrepareSortSupportFromIndexRel(indexRel, strategy, sortKey);
}
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
pfree(indexScanKey);
MemoryContextSwitchTo(oldcontext);
@@ -1248,6 +1266,12 @@ tuplesort_begin_index_btree(Relation heapRel,
PrepareSortSupportFromIndexRel(indexRel, strategy, sortKey);
}
+ /*
+ * Since we sort equal keys by ItemPointer, we can't skip the comparator
+ * call.
+ */
+ state->oneKeySort = false;
+
pfree(indexScanKey);
MemoryContextSwitchTo(oldcontext);
@@ -1297,6 +1321,7 @@ tuplesort_begin_index_hash(Relation heapRel,
state->high_mask = high_mask;
state->low_mask = low_mask;
state->max_buckets = max_buckets;
+ state->oneKeySort = false;
MemoryContextSwitchTo(oldcontext);
@@ -1356,6 +1381,10 @@ tuplesort_begin_index_gist(Relation heapRel,
PrepareSortSupportFromGistIndexRel(indexRel, sortKey);
}
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
MemoryContextSwitchTo(oldcontext);
return state;
@@ -1432,6 +1461,9 @@ tuplesort_begin_datum(Oid datumType, Oid sortOperator, Oid sortCollation,
if (!state->sortKeys->abbrev_converter)
state->onlyKey = state->sortKeys;
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = !state->sortKeys->abbrev_converter;
+
MemoryContextSwitchTo(oldcontext);
return state;
[image/png] 1m_tup_mod_100_v2.png (82.8K, ../../CAApHDvoO0sJiGgqK3vx=XX4TF6qik3YbLT-XAfvQLW6uq=t-NQ@mail.gmail.com/3-1m_tup_mod_100_v2.png)
download | view image
[application/vnd.oasis.opendocument.spreadsheet] v14_vs_v15_sorting_v2 .ods (89.6K, ../../CAApHDvoO0sJiGgqK3vx=XX4TF6qik3YbLT-XAfvQLW6uq=t-NQ@mail.gmail.com/4-v14_vs_v15_sorting_v2%20.ods)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-13 11:19 John Naylor <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 1 reply; 31+ messages in thread
From: John Naylor @ 2022-04-13 11:19 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
As promised, I've done another round of tests (script and spreadsheet
attached) with
- v15 with 6974924347 and cc58eecc5d reverted
- v15 with Thomas' patch
- v15 with David's patch
- v15 as is ("std")
...where v15 is at 7b735f8b52ad. This time I limited it to int,
bigint, and text types.
Since more cases now use random distributions, I also took some
measures to tighten up the measurements:
- Reuse the same random distribution for all tests where the input is
randomized, by invoking the script with/without a second parameter
- For the text case, use lpadded ints so that lexicographic order is
the same as numeric order.
I verified David's mod100 test case and added most test cases from the
Orson Peters paper I mentioned above. I won't explain all of them
here, but the low cardinality ones are randomized sets of:
- mod8
- dupsq: x mod sqrt(n) , for 10 million about 3 thousand distinct values
- dup8: (x**8 + n/2) mod n , for 10 million about 80 thousand distinct
values, about 80% with 64 duplicates and 20% with 256 duplicates
All the clear regressions I can see in v15 are in the above for one or
more query types / data types, and both Thomas and David's patches
restore performance for those.
More broadly than the regression, Thomas' is very often the fastest of
all, at the cost of more binary size. David's is occasionally slower
than v15 or v15 with revert, but much of that is a slight difference
and some is probably noise.
--
John Naylor
EDB: http://www.enterprisedb.com
Attachments:
[application/vnd.oasis.opendocument.spreadsheet] qsort-fix-regression-jcn1.ods (42.2K, ../../CAFBsxsHWVR4UqRknnkwxyHrY5sxhgvgkF=8sPm8+LatsQtYM9Q@mail.gmail.com/2-qsort-fix-regression-jcn1.ods)
download
[application/x-shellscript] sort-bench-int-text-plus-low-card.sh (10.5K, ../../CAFBsxsHWVR4UqRknnkwxyHrY5sxhgvgkF=8sPm8+LatsQtYM9Q@mail.gmail.com/3-sort-bench-int-text-plus-low-card.sh)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-14 06:46 David Rowley <[email protected]>
parent: John Naylor <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: David Rowley @ 2022-04-14 06:46 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Wed, 13 Apr 2022 at 23:19, John Naylor <[email protected]> wrote:
> More broadly than the regression, Thomas' is very often the fastest of
> all, at the cost of more binary size. David's is occasionally slower
> than v15 or v15 with revert, but much of that is a slight difference
> and some is probably noise.
Just to get an opinion from some other hardware, I've run your test
script on my AMD 3990x machine.
My opinion here is that the best thing we can learn from both of our
results is, do the patches fix the regression?
I don't believe it should be about if adding the additional
specializations performs better than skipping the tie break function
call. I think it's pretty obvious that the specializations will be
faster. I think if it was decided that v16 would be the version where
more work should be done to decide on what should be specialized and
what shouldn't be, then we shouldn't let this regression force our
hand to make that choice now. It'll be pretty hard to remove any
specializations once they've been in a released version of Postgres.
David
Attachments:
[application/vnd.oasis.opendocument.spreadsheet] qsort-fix-regression-dgr1.ods (41.7K, ../../CAApHDvoMQ5WMQNwQoXzOuu86gin-UbFHe88zMST6RdOGyD=vtw@mail.gmail.com/2-qsort-fix-regression-dgr1.ods)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-14 08:58 John Naylor <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 0 replies; 31+ messages in thread
From: John Naylor @ 2022-04-14 08:58 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Thu, Apr 14, 2022 at 1:46 PM David Rowley <[email protected]> wrote:
>
> On Wed, 13 Apr 2022 at 23:19, John Naylor <[email protected]> wrote:
> > More broadly than the regression, Thomas' is very often the fastest of
> > all, at the cost of more binary size. David's is occasionally slower
> > than v15 or v15 with revert, but much of that is a slight difference
> > and some is probably noise.
To add to my summary of results - the v15 code, with and without extra
patches, seems slightly worse on B-tree index creation for very low
cardinality keys, but that's not an index that's going to be useful
(and therefore common) so that's a good tradeoff in my view. The
regression David found is more concerning.
> Just to get an opinion from some other hardware, I've run your test
> script on my AMD 3990x machine.
Thanks for that. I only see 4 non-Btree measurements in your results
that are larger than v15-revert, versus 8 in mine (Comet Lake). And
overall, most of those seem within the noise level.
> My opinion here is that the best thing we can learn from both of our
> results is, do the patches fix the regression?
I'd say the answer is yes for both.
> I don't believe it should be about if adding the additional
> specializations performs better than skipping the tie break function
> call. I think it's pretty obvious that the specializations will be
> faster. I think if it was decided that v16 would be the version where
> more work should be done to decide on what should be specialized and
> what shouldn't be, then we shouldn't let this regression force our
> hand to make that choice now. It'll be pretty hard to remove any
> specializations once they've been in a released version of Postgres.
I agree that a narrow fix is preferable. I'll take a closer look at
your patch soon.
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-18 14:11 John Naylor <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 1 reply; 31+ messages in thread
From: John Naylor @ 2022-04-18 14:11 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Tue, Apr 12, 2022 at 7:58 AM David Rowley <[email protected]> wrote:
>
> I've attached the patch I tested. It was thrown together very quickly
> just to try out the performance. If it's interesting I can polish it
> up a bit. If not, I didn't waste too much time.
@@ -959,6 +965,10 @@ tuplesort_begin_batch(Tuplesortstate *state)
state->tapeset = NULL;
+ /* check if specialized sorts can skip calling the tiebreak function */
+ state->oneKeySort = state->nKeys == 1 &&
+ !state->sortKeys[0].abbrev_converter;
+
IIUC, this function is called by tuplesort_begin_common, which in turn
is called by tuplesort_begin_{heap, indexes, etc}. The latter callers
set the onlyKey and now oneKeySort variables as appropriate, and
sometimes hard-coded to false. Is it intentional to set them here
first?
Falling under the polish that you were likely thinking of above:
We might rename oneKeySort to skipTiebreaker to avoid confusion.
SInce the test for these variable is the same, we could consolidate
them into a block and reword this existing comment (which I find a
little confusing anyway):
/*
* The "onlyKey" optimization cannot be used with abbreviated keys, since
* tie-breaker comparisons may be required. Typically, the optimization
* is only of value to pass-by-value types anyway, whereas abbreviated
* keys are typically only of value to pass-by-reference types.
*/
I can take a stab at this, unless you had something else in mind.
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-19 05:29 David Rowley <[email protected]>
parent: John Naylor <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: David Rowley @ 2022-04-19 05:29 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
Thanks for looking at this.
On Tue, 19 Apr 2022 at 02:11, John Naylor <[email protected]> wrote:
> IIUC, this function is called by tuplesort_begin_common, which in turn
> is called by tuplesort_begin_{heap, indexes, etc}. The latter callers
> set the onlyKey and now oneKeySort variables as appropriate, and
> sometimes hard-coded to false. Is it intentional to set them here
> first?
>
> Falling under the polish that you were likely thinking of above:
I did put the patch together quickly just for the benchmark and at the
time I was subtly aware that the onlyKey field was being set using a
similar condition as I was using to set the boolean field I'd added.
On reflection today, it should be fine just to check if that field is
NULL or not in the 3 new comparison functions. Similarly to before,
this only needs to be done if the datums compare equally, so does not
add any code to the path where the datums are non-equal. It looks
like the other tuplesort_begin_* functions use a different comparison
function that will never make use of the specialization comparison
functions added by 697492434.
I separated out the "or" condition that I'd added tot he existing "if"
to make it easier to write a comment explaining why we can skip the
tiebreak function call.
Updated patch attached.
David
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 1174e1a31c..12d1bf551b 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -436,7 +436,10 @@ struct Tuplesortstate
/*
* This variable is shared by the single-key MinimalTuple case and the
- * Datum case (which both use qsort_ssup()). Otherwise it's NULL.
+ * Datum case (which both use qsort_ssup()). It is also used by various
+ * sort specialization functions when comparing the leading key in a
+ * tiebreak situation to determine if there are any subsequent keys to
+ * sort on. It's otherwise NULL.
*/
SortSupport onlyKey;
@@ -698,7 +701,12 @@ qsort_tuple_unsigned_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyUnsignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+
+ /*
+ * No need to call the tiebreak function when the datums differ or if this
+ * is the only key we're sorting on.
+ */
+ if (compare != 0 || state->onlyKey != NULL)
return compare;
return state->comparetup(a, b, state);
@@ -713,7 +721,12 @@ qsort_tuple_signed_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplySignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+
+ /*
+ * No need to call the tiebreak function when the datums differ or if this
+ * is the only key we're sorting on.
+ */
+ if (compare != 0 || state->onlyKey != NULL)
return compare;
return state->comparetup(a, b, state);
@@ -728,7 +741,12 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyInt32SortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+
+ /*
+ * No need to call the tiebreak function when the datums differ or if this
+ * is the only key we're sorting on.
+ */
+ if (compare != 0 || state->onlyKey != NULL)
return compare;
return state->comparetup(a, b, state);
Attachments:
[text/plain] skip_calling_sort_tiebreak_when_not_needed2.patch (2.1K, ../../CAApHDvqWQJrAwLBveQLTfcB46NSer7qRLTe_NJF2cPuXziu4oA@mail.gmail.com/2-skip_calling_sort_tiebreak_when_not_needed2.patch)
download | inline diff:
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 1174e1a31c..12d1bf551b 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -436,7 +436,10 @@ struct Tuplesortstate
/*
* This variable is shared by the single-key MinimalTuple case and the
- * Datum case (which both use qsort_ssup()). Otherwise it's NULL.
+ * Datum case (which both use qsort_ssup()). It is also used by various
+ * sort specialization functions when comparing the leading key in a
+ * tiebreak situation to determine if there are any subsequent keys to
+ * sort on. It's otherwise NULL.
*/
SortSupport onlyKey;
@@ -698,7 +701,12 @@ qsort_tuple_unsigned_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyUnsignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+
+ /*
+ * No need to call the tiebreak function when the datums differ or if this
+ * is the only key we're sorting on.
+ */
+ if (compare != 0 || state->onlyKey != NULL)
return compare;
return state->comparetup(a, b, state);
@@ -713,7 +721,12 @@ qsort_tuple_signed_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplySignedSortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+
+ /*
+ * No need to call the tiebreak function when the datums differ or if this
+ * is the only key we're sorting on.
+ */
+ if (compare != 0 || state->onlyKey != NULL)
return compare;
return state->comparetup(a, b, state);
@@ -728,7 +741,12 @@ qsort_tuple_int32_compare(SortTuple *a, SortTuple *b, Tuplesortstate *state)
compare = ApplyInt32SortComparator(a->datum1, a->isnull1,
b->datum1, b->isnull1,
&state->sortKeys[0]);
- if (compare != 0)
+
+ /*
+ * No need to call the tiebreak function when the datums differ or if this
+ * is the only key we're sorting on.
+ */
+ if (compare != 0 || state->onlyKey != NULL)
return compare;
return state->comparetup(a, b, state);
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-19 13:55 John Naylor <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 2 replies; 31+ messages in thread
From: John Naylor @ 2022-04-19 13:55 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Tue, Apr 19, 2022 at 12:30 PM David Rowley <[email protected]> wrote:
>
> Thanks for looking at this.
>
> On Tue, 19 Apr 2022 at 02:11, John Naylor <[email protected]> wrote:
> > IIUC, this function is called by tuplesort_begin_common, which in turn
> > is called by tuplesort_begin_{heap, indexes, etc}. The latter callers
> > set the onlyKey and now oneKeySort variables as appropriate, and
> > sometimes hard-coded to false. Is it intentional to set them here
> > first?
> >
> > Falling under the polish that you were likely thinking of above:
>
> I did put the patch together quickly just for the benchmark and at the
> time I was subtly aware that the onlyKey field was being set using a
> similar condition as I was using to set the boolean field I'd added.
> On reflection today, it should be fine just to check if that field is
> NULL or not in the 3 new comparison functions. Similarly to before,
> this only needs to be done if the datums compare equally, so does not
> add any code to the path where the datums are non-equal. It looks
> like the other tuplesort_begin_* functions use a different comparison
> function that will never make use of the specialization comparison
> functions added by 697492434.
Okay, this makes logical sense and is a smaller patch to boot. I've
re-run my tests (attached) to make sure we have our bases covered. I'm
sharing the min-of-five, as before, but locally I tried . The
regression is fixed, and most other differences from v15 seem to be
noise. It's possible the naturally fastest cases (pre-sorted ints and
bigints) are slower than v15-revert than expected from noise, but it's
not clear.
I think this is good to go.
--
John Naylor
EDB: http://www.enterprisedb.com
Attachments:
[application/vnd.oasis.opendocument.spreadsheet] qsort-fix-regression-jcn2.ods (39.5K, ../../CAFBsxsH4RGpNB+O17FKnYApOmo0h+434iis+-9G6S3-p-krpOA@mail.gmail.com/2-qsort-fix-regression-jcn2.ods)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-19 13:56 John Naylor <[email protected]>
parent: John Naylor <[email protected]>
1 sibling, 0 replies; 31+ messages in thread
From: John Naylor @ 2022-04-19 13:56 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
> Okay, this makes logical sense and is a smaller patch to boot. I've
> re-run my tests (attached) to make sure we have our bases covered. I'm
> sharing the min-of-five, as before, but locally I tried . The
My sentence there was supposed to read "I tried using median and it
was a bit less noisy".
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-21 07:09 John Naylor <[email protected]>
parent: John Naylor <[email protected]>
1 sibling, 1 reply; 31+ messages in thread
From: John Naylor @ 2022-04-21 07:09 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
I intend to commit David's v2 fix next week, unless there are
objections, or unless he beats me to it.
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-22 04:13 David Rowley <[email protected]>
parent: John Naylor <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: David Rowley @ 2022-04-22 04:13 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Thu, 21 Apr 2022 at 19:09, John Naylor <[email protected]> wrote:
> I intend to commit David's v2 fix next week, unless there are
> objections, or unless he beats me to it.
I wasn't sure if you wanted to handle it or not, but I don't mind
doing it, so I just pushed it after a small adjustment to a comment.
Before going ahead with it I did test a 2-key sort where the leading
key values were all the same. I wondered if we'd still see any
regression from having to re-compare the leading key all over again.
I just did:
create table ab (a bigint, b bigint);
insert into ab select 0,x from generate_series(1,1000000)x;
vacuum freeze ab;
I then ran:
select * from ab order by a,b offset 1000000;
697492434 (Specialize tuplesort routines for different kinds of
abbreviated keys)
$ pgbench -n -f bench1.sql -T 60 -M prepared postgres
tps = 10.651740 (without initial connection time)
tps = 10.813647 (without initial connection time)
tps = 10.648960 (without initial connection time)
697492434~1 (Remove obsolete comment)
$ pgbench -n -f bench1.sql -T 60 -M prepared postgres
tps = 9.957163 (without initial connection time)
tps = 10.191168 (without initial connection time)
tps = 10.145281 (without initial connection time)
So it seems there was no regression for that case, at least, not on
the AMD machine that I tested on.
David
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-22 04:37 John Naylor <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 2 replies; 31+ messages in thread
From: John Naylor @ 2022-04-22 04:37 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Fri, Apr 22, 2022 at 11:13 AM David Rowley <[email protected]> wrote:
>
> On Thu, 21 Apr 2022 at 19:09, John Naylor <[email protected]> wrote:
> > I intend to commit David's v2 fix next week, unless there are
> > objections, or unless he beats me to it.
>
> I wasn't sure if you wanted to handle it or not, but I don't mind
> doing it, so I just pushed it after a small adjustment to a comment.
Thank you!
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-04-22 05:10 Thomas Munro <[email protected]>
parent: John Naylor <[email protected]>
1 sibling, 0 replies; 31+ messages in thread
From: Thomas Munro @ 2022-04-22 05:10 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: David Rowley <[email protected]>; Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Fri, Apr 22, 2022 at 4:37 PM John Naylor
<[email protected]> wrote:
> On Fri, Apr 22, 2022 at 11:13 AM David Rowley <[email protected]> wrote:
> > On Thu, 21 Apr 2022 at 19:09, John Naylor <[email protected]> wrote:
> > > I intend to commit David's v2 fix next week, unless there are
> > > objections, or unless he beats me to it.
> >
> > I wasn't sure if you wanted to handle it or not, but I don't mind
> > doing it, so I just pushed it after a small adjustment to a comment.
>
> Thank you!
Thanks both for working on this. Seems like a good call to defer the
choice of further specialisations.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-05-19 20:12 Justin Pryzby <[email protected]>
parent: John Naylor <[email protected]>
1 sibling, 1 reply; 31+ messages in thread
From: Justin Pryzby @ 2022-05-19 20:12 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: David Rowley <[email protected]>; Thomas Munro <[email protected]>; Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Fri, Apr 22, 2022 at 11:37:29AM +0700, John Naylor wrote:
> On Fri, Apr 22, 2022 at 11:13 AM David Rowley <[email protected]> wrote:
> >
> > On Thu, 21 Apr 2022 at 19:09, John Naylor <[email protected]> wrote:
> > > I intend to commit David's v2 fix next week, unless there are
> > > objections, or unless he beats me to it.
> >
> > I wasn't sure if you wanted to handle it or not, but I don't mind
> > doing it, so I just pushed it after a small adjustment to a comment.
>
> Thank you!
Should these debug lines be removed ?
elog(DEBUG1, "qsort_tuple");
Perhaps if I ask for debug output, I shouldn't be surprised if it changes
between major releases - but I still found this surprising.
I'm sure it's useful during development and maybe during beta. It could even
make sense if it were shown during regression tests (preferably at DEBUG2).
But right now it's not. is that
ts=# \dt
DEBUG: qsort_tuple
List of relations
--
Justin
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-05-19 22:24 Peter Geoghegan <[email protected]>
parent: Justin Pryzby <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Peter Geoghegan @ 2022-05-19 22:24 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: John Naylor <[email protected]>; David Rowley <[email protected]>; Thomas Munro <[email protected]>; Andres Freund <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Thu, May 19, 2022 at 1:12 PM Justin Pryzby <[email protected]> wrote:
> Should these debug lines be removed ?
>
> elog(DEBUG1, "qsort_tuple");
I agree -- DEBUG1 seems too chatty for something like this. DEBUG2
would be more appropriate IMV. Though I don't feel very strongly about
it.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-05-19 22:43 Tom Lane <[email protected]>
parent: Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: Tom Lane @ 2022-05-19 22:43 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Justin Pryzby <[email protected]>; John Naylor <[email protected]>; David Rowley <[email protected]>; Thomas Munro <[email protected]>; Andres Freund <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
Peter Geoghegan <[email protected]> writes:
> On Thu, May 19, 2022 at 1:12 PM Justin Pryzby <[email protected]> wrote:
>> Should these debug lines be removed ?
>>
>> elog(DEBUG1, "qsort_tuple");
> I agree -- DEBUG1 seems too chatty for something like this. DEBUG2
> would be more appropriate IMV. Though I don't feel very strongly about
> it.
Given the lack of context identification, I'd put the usefulness of
these in production at close to zero. +1 for removing them
altogether, or failing that, downgrade to DEBUG5 or so.
regards, tom lane
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-05-20 06:40 John Naylor <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 31+ messages in thread
From: John Naylor @ 2022-05-20 06:40 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Justin Pryzby <[email protected]>; David Rowley <[email protected]>; Thomas Munro <[email protected]>; Andres Freund <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
On Fri, May 20, 2022 at 5:43 AM Tom Lane <[email protected]> wrote:
>
> Peter Geoghegan <[email protected]> writes:
> > On Thu, May 19, 2022 at 1:12 PM Justin Pryzby <[email protected]> wrote:
> >> Should these debug lines be removed ?
> >>
> >> elog(DEBUG1, "qsort_tuple");
>
> > I agree -- DEBUG1 seems too chatty for something like this. DEBUG2
> > would be more appropriate IMV. Though I don't feel very strongly about
> > it.
>
> Given the lack of context identification, I'd put the usefulness of
> these in production at close to zero. +1 for removing them
> altogether, or failing that, downgrade to DEBUG5 or so.
I agree this is only useful in development. Removal sounds fine to me,
so I'll do that soon.
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: A qsort template
@ 2022-05-23 06:17 John Naylor <[email protected]>
parent: John Naylor <[email protected]>
0 siblings, 0 replies; 31+ messages in thread
From: John Naylor @ 2022-05-23 06:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Justin Pryzby <[email protected]>; David Rowley <[email protected]>; Thomas Munro <[email protected]>; Andres Freund <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>
I wrote:
> I agree this is only useful in development. Removal sounds fine to me,
> so I'll do that soon.
This is done.
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 31+ messages in thread
end of thread, other threads:[~2022-05-23 06:17 UTC | newest]
Thread overview: 31+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-06 23:23 [PATCH v12 10/11] pg_ls_logdir to ignore error if initial/top dir is missing Justin Pryzby <[email protected]>
2022-04-02 21:03 Re: A qsort template Andres Freund <[email protected]>
2022-04-02 21:45 ` Re: A qsort template Thomas Munro <[email protected]>
2022-04-02 23:11 ` Re: A qsort template Andres Freund <[email protected]>
2022-04-03 05:46 ` Re: A qsort template Thomas Munro <[email protected]>
2022-04-03 16:32 ` Re: A qsort template Andres Freund <[email protected]>
2022-04-03 23:01 ` Re: A qsort template Thomas Munro <[email protected]>
2022-04-06 10:31 ` Re: A qsort template John Naylor <[email protected]>
2022-04-10 21:44 ` Re: A qsort template Thomas Munro <[email protected]>
2022-04-10 21:54 ` Re: A qsort template Peter Geoghegan <[email protected]>
2022-04-11 10:11 ` Re: A qsort template John Naylor <[email protected]>
2022-04-12 00:40 ` Re: A qsort template David Rowley <[email protected]>
2022-04-10 22:34 ` Re: A qsort template David Rowley <[email protected]>
2022-04-11 00:25 ` Re: A qsort template David Rowley <[email protected]>
2022-04-12 00:58 ` Re: A qsort template David Rowley <[email protected]>
2022-04-13 11:19 ` Re: A qsort template John Naylor <[email protected]>
2022-04-14 06:46 ` Re: A qsort template David Rowley <[email protected]>
2022-04-14 08:58 ` Re: A qsort template John Naylor <[email protected]>
2022-04-18 14:11 ` Re: A qsort template John Naylor <[email protected]>
2022-04-19 05:29 ` Re: A qsort template David Rowley <[email protected]>
2022-04-19 13:55 ` Re: A qsort template John Naylor <[email protected]>
2022-04-19 13:56 ` Re: A qsort template John Naylor <[email protected]>
2022-04-21 07:09 ` Re: A qsort template John Naylor <[email protected]>
2022-04-22 04:13 ` Re: A qsort template David Rowley <[email protected]>
2022-04-22 04:37 ` Re: A qsort template John Naylor <[email protected]>
2022-04-22 05:10 ` Re: A qsort template Thomas Munro <[email protected]>
2022-05-19 20:12 ` Re: A qsort template Justin Pryzby <[email protected]>
2022-05-19 22:24 ` Re: A qsort template Peter Geoghegan <[email protected]>
2022-05-19 22:43 ` Re: A qsort template Tom Lane <[email protected]>
2022-05-20 06:40 ` Re: A qsort template John Naylor <[email protected]>
2022-05-23 06:17 ` Re: A qsort template John Naylor <[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