[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-hackers
Subject: Re: some aspects of our qsort might not be ideal
From: John Naylor <john.naylor () enterprisedb ! com>
Date: 2022-06-30 10:21:19
Message-ID: CAFBsxsGK4zn7UH3Yr5QTCA7Qf6u3c+0xgHeL6m+0Jmo8VA_--Q () mail ! gmail ! com
[Download RAW message or body]
I've run the microbenchmarks only so far, since they complete much
faster than queries, and I wanted to tell quickly if this is a good
avenue to pursue. Later I'll repeat for queries. Methodology is
similar to what I did earlier in the thread, so I won't belabor that.
Takeaways:
- Contrary to some testing on single pivot I did some month ago with
fewer input distributions, in this test neither single nor dual pivot
benefit much from a large insertion sort threshold (i.e. 20 or so). I
picked 12 for both to do the head-to-head comparison, since this seems
to give a slight boost to specialized sorts on the slowest inputs.
They don't have to be the same, of course, but it seems about right
with these results. (Of course, it's easy to have specialized sorts
define their own threshold, as Thomas Munro has shown.)
- Generic qsort (type length and comparator passed at runtime) sees no
benefit from dual-pivot in this test, but specialized qsorts do get a
decent speedup.
- Descending inputs get a huge boost when specialized. This is
surprising to me, enough that I'm skeptical and want to double check
the test. If it's legitimate, I'm happy about it.
I've attached three spreadsheets with graphs, two for threshold tests
for single- and dual-pivot, and one comparing single and dual for
threshold=12.
0001 is the test module and rough script (not for commit). Note: The
server won't build, since the threshold is passed via CPPFLAGS.
0002 is trivial and mostly for assert builds
0003 is a mostly cleaned up patch for dual pivot, with passing
regression tests and default insertion sort threshold of 12
I'll add a CF entry for this.
TODO: add results for queries.
--
John Naylor
EDB: http://www.enterprisedb.com
["v2-0001-Add-sort-test-module.patch" (text/x-patch)]
From 7a0286b3e1f403c622f8cf7dd65dc86103745ebb Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Mon, 30 May 2022 09:55:46 +0700
Subject: [PATCH v2 1/3] Add sort test module
XXX not for commit
---
src/include/lib/sort_template.h | 2 +-
src/test/modules/test_sort_perf/Makefile | 20 ++
.../run-microbenchmark-thresholds.sh | 37 +++
.../test_sort_cmp_weight_include.c | 237 ++++++++++++++++++
.../test_sort_perf/test_sort_perf--1.0.sql | 1 +
.../modules/test_sort_perf/test_sort_perf.c | 90 +++++++
.../test_sort_perf/test_sort_perf.control | 4 +
7 files changed, 390 insertions(+), 1 deletion(-)
create mode 100644 src/test/modules/test_sort_perf/Makefile
create mode 100755 src/test/modules/test_sort_perf/run-microbenchmark-thresholds.sh
create mode 100644 src/test/modules/test_sort_perf/test_sort_cmp_weight_include.c
create mode 100644 src/test/modules/test_sort_perf/test_sort_perf--1.0.sql
create mode 100644 src/test/modules/test_sort_perf/test_sort_perf.c
create mode 100644 src/test/modules/test_sort_perf/test_sort_perf.control
diff --git a/src/include/lib/sort_template.h b/src/include/lib/sort_template.h
index 3122a93009..5a2f1d3fbb 100644
--- a/src/include/lib/sort_template.h
+++ b/src/include/lib/sort_template.h
@@ -296,7 +296,7 @@ ST_SORT(ST_ELEMENT_TYPE * data, size_t n
loop:
DO_CHECK_FOR_INTERRUPTS();
- if (n < 7)
+ if (n < ST_INSERTION_SORT_THRESHOLD)
{
for (pm = a + ST_POINTER_STEP; pm < a + n * ST_POINTER_STEP;
pm += ST_POINTER_STEP)
diff --git a/src/test/modules/test_sort_perf/Makefile \
b/src/test/modules/test_sort_perf/Makefile new file mode 100644
index 0000000000..1e49fb43c0
--- /dev/null
+++ b/src/test/modules/test_sort_perf/Makefile
@@ -0,0 +1,20 @@
+MODULE_big = test_sort_perf
+OBJS = test_sort_perf.o
+PGFILEDESC = "test"
+EXTENSION = test_sort_perf
+DATA = test_sort_perf--1.0.sql
+
+first: all
+
+test_sort_perf.o: test_sort_cmp_weight_include.c
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_sort_perf
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_sort_perf/run-microbenchmark-thresholds.sh \
b/src/test/modules/test_sort_perf/run-microbenchmark-thresholds.sh new file mode \
100755 index 0000000000..90df741f1e
--- /dev/null
+++ b/src/test/modules/test_sort_perf/run-microbenchmark-thresholds.sh
@@ -0,0 +1,37 @@
+# ../papers-sorting/sort-bench-pdqsort-sql-20220529.sh 1000 setup
+
+set -e
+
+#ROWS=10000000
+#../papers-sorting/sort-bench-pdqsort-sql-20220529.sh $ROWS setup
+
+
+# test actual funcs
+for threshold in 7 10 12 14 16 18 20 22 24 32; do
+#for threshold in 7 10; do
+
+# version 10
+echo "Threshold: $threshold"
+
+# TODO: run queries
+#../rebuild.sh
+#../papers-sorting/sort-bench-pdqsort-sql-20220529.sh $ROWS
+#mv results.csv queryresult-$(date +'%Y%m%d')-$threshold.csv
+
+# run microbenchmark
+
+# build perf module
+pushd src/test/modules/test_sort_perf/ >/dev/null
+touch test_sort_perf.c
+make -s CPPFLAGS=-DST_INSERTION_SORT_THRESHOLD=$threshold && make install -s
+popd >/dev/null
+
+padthreshold=$(printf "%02d" $threshold)
+./inst/bin/psql -c 'drop extension if exists test_sort_perf; create extension \
test_sort_perf;' +./inst/bin/psql -c 'select test_sort_cmp_weight(1 * 1024*1024)' 2> \
microresult-$padthreshold.txt +#./inst/bin/psql -c 'select test_sort_cmp_weight(1 * \
1024)' 2> microresult-$padthreshold.txt +
+perl -n -e 'print "$1\t$2\t$3\t$4\t$5\n" if /NOTICE: \[(.+)\] num=(\d+), \
threshold=(\d+), order=(\w+), time=(\d+\.\d+)/;' microresult-$padthreshold.txt > \
microresult-$(date +'%Y%m%d')-$padthreshold.csv +
+done
+
diff --git a/src/test/modules/test_sort_perf/test_sort_cmp_weight_include.c \
b/src/test/modules/test_sort_perf/test_sort_cmp_weight_include.c new file mode 100644
index 0000000000..eaf1191d09
--- /dev/null
+++ b/src/test/modules/test_sort_perf/test_sort_cmp_weight_include.c
@@ -0,0 +1,237 @@
+#include <math.h>
+
+static void run_tests(char* type, Datum *unsorted, Datum *sorted, BTSortArrayContext \
*cxt, int nobjects) +{
+ const int numtests = 10;
+ double time,
+ min;
+
+ if (nobjects <= 100)
+ {
+ elog(NOTICE, "order: %s", type);
+ for (int j = 0; j < nobjects; ++j)
+ elog(NOTICE, "%d", DatumGetInt32(unsorted[j]));
+ return;
+ }
+
+ min = 1000000;
+ for (int i = 1; i <= numtests; ++i)
+ {
+ instr_time start_time, end_time;
+ memcpy(sorted, unsorted, sizeof(Datum) * nobjects);
+ INSTR_TIME_SET_CURRENT(start_time);
+ qsort_arg((void *) sorted, nobjects, sizeof(Datum), _bt_compare_array_elements, \
(void *) cxt); + INSTR_TIME_SET_CURRENT(end_time);
+ INSTR_TIME_SUBTRACT(end_time, start_time);
+ time = INSTR_TIME_GET_DOUBLE(end_time);
+ if (time < min)
+ min = time;
+ }
+ elog(NOTICE, "[fmgr runtime] num=%d, threshold=%d, order=%s, time=%f", nobjects, \
ST_INSERTION_SORT_THRESHOLD, type, min); +
+ min = 1000000;
+ for (int i = 1; i <= numtests; ++i)
+ {
+ instr_time start_time, end_time;
+ memcpy(sorted, unsorted, sizeof(Datum) * nobjects);
+ INSTR_TIME_SET_CURRENT(start_time);
+ qsort_bt_array_elements(sorted, nobjects, cxt);
+ INSTR_TIME_SET_CURRENT(end_time);
+ INSTR_TIME_SUBTRACT(end_time, start_time);
+ time = INSTR_TIME_GET_DOUBLE(end_time);
+ if (time < min)
+ min = time;
+ }
+ elog(NOTICE, "[fmgr specialized] num=%d, threshold=%d, order=%s, time=%f", \
nobjects, ST_INSERTION_SORT_THRESHOLD, type, min); +
+ min = 1000000;
+ for (int i = 1; i <= numtests; ++i)
+ {
+ instr_time start_time, end_time;
+ memcpy(sorted, unsorted, sizeof(Datum) * nobjects);
+ INSTR_TIME_SET_CURRENT(start_time);
+ qsort(sorted, nobjects, sizeof(Datum), btint4fastcmp);
+ INSTR_TIME_SET_CURRENT(end_time);
+ INSTR_TIME_SUBTRACT(end_time, start_time);
+ time = INSTR_TIME_GET_DOUBLE(end_time);
+ if (time < min)
+ min = time;
+ }
+ elog(NOTICE, "[C runtime] num=%d, threshold=%d, order=%s, time=%f", nobjects, \
ST_INSERTION_SORT_THRESHOLD, type, min); +
+ min = 1000000;
+ for (int i = 0; i < numtests; ++i)
+ {
+ instr_time start_time, end_time;
+ memcpy(sorted, unsorted, sizeof(Datum) * nobjects);
+ INSTR_TIME_SET_CURRENT(start_time);
+ qsort_int32(sorted, nobjects);
+ INSTR_TIME_SET_CURRENT(end_time);
+ INSTR_TIME_SUBTRACT(end_time, start_time);
+ time = INSTR_TIME_GET_DOUBLE(end_time);
+ if (time < min)
+ min = time;
+ }
+ elog(NOTICE, "[C specialized] num=%d, threshold=%d, order=%s, time=%f", nobjects, \
ST_INSERTION_SORT_THRESHOLD, type, min); +}
+
+typedef struct source_value
+{
+ int32 value;
+ float rand;
+} source_value;
+
+static int
+source_rand_cmp(const void * x, const void * y)
+{
+ source_value *a = (source_value *) x;
+ source_value *b = (source_value *) y;
+
+ if (a->rand > b->rand)
+ return 1;
+ else if (a->rand < b->rand)
+ return -1;
+ else
+ return 0;
+}
+
+static int
+source_dither_cmp(const void * x, const void * y, void * nobjects)
+{
+ source_value *a = (source_value *) x;
+ source_value *b = (source_value *) y;
+ float dither;
+
+ if (*(int *) nobjects <= 100)
+ dither = 5;
+ else
+ dither = 100;
+
+ if ((a->value + dither * a->rand) > (b->value + dither * b->rand))
+ return 1;
+ else if ((a->value + dither * a->rand) < (b->value + dither * b->rand))
+ return -1;
+ else
+ return 0;
+}
+
+static void
+do_sort_cmp_weight(int nobjects)
+{
+ source_value *source = malloc(sizeof(source_value) * nobjects);
+ source_value *tmp = malloc(sizeof(source_value) * nobjects);
+ Datum *unsorted = malloc(sizeof(Datum) * nobjects);
+ Datum *sorted = malloc(sizeof(Datum) * nobjects);
+
+ // for fmgr comparator tests
+
+ BTSortArrayContext cxt;
+ RegProcedure cmp_proc ;
+
+ // to keep from pulling in nbtree.h
+#define BTORDER_PROC 1
+
+ cmp_proc = get_opfamily_proc(INTEGER_BTREE_FAM_OID,
+ INT4OID,
+ INT4OID,
+ BTORDER_PROC);
+
+ fmgr_info(cmp_proc, &cxt.flinfo);
+ cxt.collation = DEFAULT_COLLATION_OID;
+ cxt.reverse = false;
+
+ // needed for some distributions: first populate source array with ascending value \
and random tag + for (int i = 0; i < nobjects; ++i)
+ {
+ source[i].value = i;
+ source[i].rand = (float) random() / (float) RAND_MAX; // between 0 and 1
+ }
+// if (nobjects <= 100)
+// for (int j = 0; j < nobjects; ++j)
+// elog(NOTICE, "%f", (source[j].rand));
+ qsort(source, nobjects, sizeof(source_value), source_rand_cmp);
+
+ // random
+ for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(source[i].value);
+ run_tests("random", unsorted, sorted, &cxt, nobjects);
+
+ // for these, sort the first X % of the array
+
+ // sort50
+ for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(source[i].value);
+ // sort first part of unsorted[]
+ qsort(unsorted, nobjects/2, sizeof(Datum), btint4fastcmp);
+ run_tests("sort50", unsorted, sorted, &cxt, nobjects);
+
+ // sort90
+ qsort(unsorted, nobjects/10 * 9, sizeof(Datum), btint4fastcmp);
+ run_tests("sort90", unsorted, sorted, &cxt, nobjects);
+
+ // sort99
+ qsort(unsorted, nobjects/100 * 99, sizeof(Datum), btint4fastcmp);
+ run_tests("sort99", unsorted, sorted, &cxt, nobjects);
+
+ // dither -- copy source to tmp array because it sorts differently
+ memcpy(tmp, source, sizeof(source_value) * nobjects);
+ qsort_arg(tmp, nobjects, sizeof(source_value), source_dither_cmp, (void *) \
&nobjects); + for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(tmp[i].value);
+ run_tests("dither", unsorted, sorted, &cxt, nobjects);
+
+ // descending
+ for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(nobjects - i);
+ run_tests("descending", unsorted, sorted, &cxt, nobjects);
+
+ // organ
+ for (int i = 0; i < nobjects/2; ++i)
+ unsorted[i] = Int32GetDatum(i);
+ for (int i = nobjects/2; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(nobjects - i);
+ run_tests("organ", unsorted, sorted, &cxt, nobjects);
+
+ // merge
+ for (int i = 0; i < nobjects/2; ++i)
+ unsorted[i] = Int32GetDatum(i);
+ for (int i = nobjects/2; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(i - nobjects/2);
+ run_tests("merge", unsorted, sorted, &cxt, nobjects);
+
+ // dup8
+ for (int i = 0; i < nobjects; ++i)
+ {
+ uint32 tmp = 1; // XXX this will only show duplicates for large nobjects
+ for (int j=0; j<8; ++j)
+ tmp *= (uint32) source[i].value;
+ tmp = (tmp + nobjects/2) % nobjects;
+ unsorted[i] = Int32GetDatum((int) tmp);
+ }
+ run_tests("dup8", unsorted, sorted, &cxt, nobjects);
+
+ // dupsq
+ for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum((int) sqrt(source[i].value));
+ run_tests("dupsq", unsorted, sorted, &cxt, nobjects);
+
+ // mod100
+ for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(source[i].value % 100);
+ run_tests("mod100", unsorted, sorted, &cxt, nobjects);
+
+ // mod8
+ for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(source[i].value % 8);
+ run_tests("mod8", unsorted, sorted, &cxt, nobjects);
+
+ // ascending
+ for (int i = 0; i < nobjects; ++i)
+ unsorted[i] = Int32GetDatum(i);
+ run_tests("ascending", unsorted, sorted, &cxt, nobjects);
+
+ free(tmp);
+ free(source);
+ free(sorted);
+ free(unsorted);
+}
diff --git a/src/test/modules/test_sort_perf/test_sort_perf--1.0.sql \
b/src/test/modules/test_sort_perf/test_sort_perf--1.0.sql new file mode 100644
index 0000000000..22e0726a57
--- /dev/null
+++ b/src/test/modules/test_sort_perf/test_sort_perf--1.0.sql
@@ -0,0 +1 @@
+CREATE FUNCTION test_sort_cmp_weight(int4) RETURNS void AS 'MODULE_PATHNAME' \
LANGUAGE C;
diff --git a/src/test/modules/test_sort_perf/test_sort_perf.c \
b/src/test/modules/test_sort_perf/test_sort_perf.c new file mode 100644
index 0000000000..d162e42049
--- /dev/null
+++ b/src/test/modules/test_sort_perf/test_sort_perf.c
@@ -0,0 +1,90 @@
+#include "postgres.h"
+
+#include "funcapi.h"
+#include "catalog/index.h"
+#include "catalog/pg_collation_d.h"
+#include "catalog/pg_type_d.h"
+#include "catalog/pg_opfamily_d.h"
+#include "miscadmin.h"
+#include "portability/instr_time.h"
+#include "storage/itemptr.h"
+#include "utils/lsyscache.h"
+
+#include <stdlib.h>
+
+// standard comparators
+
+// comparator for qsort_arg() copied from nbtutils.c
+static int
+btint4fastcmp(const void * x, const void * y)
+{
+ int32 *a = (int32 *) x;
+ int32 *b = (int32 *) y;
+
+ if (*a > *b)
+ return 1;
+ else if (*a == *b)
+ return 0;
+ else
+ return -1;
+}
+
+// specialized qsort with inlined comparator
+#define ST_SORT qsort_int32
+#define ST_ELEMENT_TYPE Datum
+#define ST_COMPARE(a, b) (btint4fastcmp(a, b))
+#define ST_SCOPE static
+#define ST_DEFINE
+#define ST_DECLARE
+#include "lib/sort_template.h"
+
+// SQL-callable comparators
+
+typedef struct BTSortArrayContext
+{
+ FmgrInfo flinfo;
+ Oid collation;
+ bool reverse;
+} BTSortArrayContext;
+
+// comparator for qsort arg
+static int
+_bt_compare_array_elements(const void *a, const void *b, void *arg)
+{
+ Datum da = *((const Datum *) a);
+ Datum db = *((const Datum *) b);
+ BTSortArrayContext *cxt = (BTSortArrayContext *) arg;
+ int32 compare;
+
+ compare = DatumGetInt32(FunctionCall2Coll(&cxt->flinfo,
+ cxt->collation,
+ da, db));
+ if (cxt->reverse)
+ INVERT_COMPARE_RESULT(compare);
+ return compare;
+}
+
+/* Define a specialized sort function for _bt_sort_array_elements. */
+#define ST_SORT qsort_bt_array_elements
+#define ST_ELEMENT_TYPE Datum
+#define ST_COMPARE(a, b, cxt) \
+ DatumGetInt32(FunctionCall2Coll(&cxt->flinfo, cxt->collation, *a, *b))
+#define ST_COMPARE_ARG_TYPE BTSortArrayContext
+#define ST_SCOPE static
+#define ST_DEFINE
+#include "lib/sort_template.h"
+
+PG_MODULE_MAGIC;
+
+/* include the test suites */
+#include "test_sort_cmp_weight_include.c"
+
+PG_FUNCTION_INFO_V1(test_sort_cmp_weight);
+Datum
+test_sort_cmp_weight(PG_FUNCTION_ARGS)
+{
+ const int32 nobjects = PG_GETARG_INT32(0);
+
+ do_sort_cmp_weight(nobjects);
+ PG_RETURN_NULL();
+}
diff --git a/src/test/modules/test_sort_perf/test_sort_perf.control \
b/src/test/modules/test_sort_perf/test_sort_perf.control new file mode 100644
index 0000000000..336cb0c5ba
--- /dev/null
+++ b/src/test/modules/test_sort_perf/test_sort_perf.control
@@ -0,0 +1,4 @@
+comment = 'test'
+default_version = '1.0'
+module_pathname = '$libdir/test_sort_perf'
+relocatable = true
--
2.36.1
["v2-0002-Create-internal-workhorse-for-ST_SORT.patch" (text/x-patch)]
From 669ae583a991b90e7d7847cc8468619bd2b535f5 Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Mon, 30 May 2022 10:09:17 +0700
Subject: [PATCH v2 2/3] Create internal workhorse for ST_SORT
Also use a macro intead of a hard-coded value for the insertion sort
threshold. This improves readability and enables specializing the
threshold if desired.
No functional changes.
---
src/include/lib/sort_template.h | 41 +++++++++++++++++++++++++++++----
1 file changed, 36 insertions(+), 5 deletions(-)
diff --git a/src/include/lib/sort_template.h b/src/include/lib/sort_template.h
index 5a2f1d3fbb..ea04011ce5 100644
--- a/src/include/lib/sort_template.h
+++ b/src/include/lib/sort_template.h
@@ -172,6 +172,11 @@
#define ST_SORT_INVOKE_ARG
#endif
+/* Default input size threshold to control algorithm choice. */
+#ifndef ST_INSERTION_SORT_THRESHOLD
+#define ST_INSERTION_SORT_THRESHOLD 7
+#endif
+
#ifdef ST_DECLARE
#ifdef ST_COMPARE_RUNTIME_POINTER
@@ -191,6 +196,7 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n
/* sort private helper functions */
#define ST_MED3 ST_MAKE_NAME(ST_SORT, med3)
+#define ST_SORT_INTERNAL ST_MAKE_NAME(ST_SORT, internal)
#define ST_SWAP ST_MAKE_NAME(ST_SORT, swap)
#define ST_SWAPN ST_MAKE_NAME(ST_SORT, swapn)
@@ -217,7 +223,7 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n
ST_SORT_INVOKE_COMPARE \
ST_SORT_INVOKE_ARG)
#define DO_SORT(a_, n_) \
- ST_SORT((a_), (n_) \
+ ST_SORT_INTERNAL((a_), (n_) \
ST_SORT_INVOKE_ELEMENT_SIZE \
ST_SORT_INVOKE_COMPARE \
ST_SORT_INVOKE_ARG)
@@ -273,15 +279,15 @@ ST_SWAPN(ST_POINTER_TYPE * a, ST_POINTER_TYPE * b, size_t n)
}
/*
- * Sort an array.
+ * Workhorse for ST_SORT
*/
-ST_SCOPE void
-ST_SORT(ST_ELEMENT_TYPE * data, size_t n
+static void
+ST_SORT_INTERNAL(ST_POINTER_TYPE * data, size_t n
ST_SORT_PROTO_ELEMENT_SIZE
ST_SORT_PROTO_COMPARE
ST_SORT_PROTO_ARG)
{
- ST_POINTER_TYPE *a = (ST_POINTER_TYPE *) data,
+ ST_POINTER_TYPE *a = data,
*pa,
*pb,
*pc,
@@ -399,6 +405,30 @@ loop:
}
}
}
+
+/*
+ * Sort an array.
+ */
+ST_SCOPE void
+ST_SORT(ST_ELEMENT_TYPE * data, size_t n
+ ST_SORT_PROTO_ELEMENT_SIZE
+ ST_SORT_PROTO_COMPARE
+ ST_SORT_PROTO_ARG)
+{
+ ST_POINTER_TYPE *begin = (ST_POINTER_TYPE *) data;
+
+ DO_SORT(begin, n);
+
+#ifdef USE_ASSERT_CHECKING
+ /* WIP: verify the sorting worked */
+ for (ST_POINTER_TYPE *pm = begin + ST_POINTER_STEP; pm < begin + n * ST_POINTER_STEP;
+ pm += ST_POINTER_STEP)
+ {
+ if (DO_COMPARE(pm, pm - ST_POINTER_STEP) < 0)
+ Assert(false);
+ }
+#endif /* USE_ASSERT_CHECKING */
+}
#endif
#undef DO_CHECK_FOR_INTERRUPTS
@@ -422,6 +452,7 @@ loop:
#undef ST_POINTER_TYPE
#undef ST_SCOPE
#undef ST_SORT
+#undef ST_SORT_INTERNAL
#undef ST_SORT_INVOKE_ARG
#undef ST_SORT_INVOKE_COMPARE
#undef ST_SORT_INVOKE_ELEMENT_SIZE
--
2.36.1
["v2-0003-Implement-dual-pivot-quicksort.patch" (text/x-patch)]
From bc2752902d1187ed8086641b4da1f36836940cb8 Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Thu, 30 Jun 2022 16:56:06 +0700
Subject: [PATCH v2 3/3] Implement dual-pivot quicksort
Choose pivots by running insertion sort on five candidates and choosing
the 2nd and 4th, ("tertile of five"). If any of the five are equal, we
assume the input has many duplicates and fall back to B&M since it's
optimized for that case.
Set default insertion sort threshold to 12, since it seems better
for dual pivot.
---
.../expected/pg_stat_statements.out | 4 +-
src/include/lib/sort_template.h | 219 +++++--
src/test/regress/expected/create_index.out | 2 +-
src/test/regress/expected/geometry.out | 4 +-
src/test/regress/expected/groupingsets.out | 4 +-
src/test/regress/expected/inet.out | 8 +-
src/test/regress/expected/join.out | 2 +-
src/test/regress/expected/merge.out | 22 +-
src/test/regress/expected/sqljson.out | 10 +-
src/test/regress/expected/tsrf.out | 60 +-
src/test/regress/expected/tuplesort.out | 16 +-
src/test/regress/expected/window.out | 544 +++++++++---------
12 files changed, 515 insertions(+), 380 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out \
b/contrib/pg_stat_statements/expected/pg_stat_statements.out index \
8f7f93172a..5a894a0f8b 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -196,10 +196,10 @@ SELECT * FROM test ORDER BY a;
---+----------------------
1 | a
1 | 111
- 2 | b
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 ea04011ce5..1b70331f05 100644
--- a/src/include/lib/sort_template.h
+++ b/src/include/lib/sort_template.h
@@ -135,7 +135,7 @@
/*
* If the user wants to be able to pass in compare functions at runtime,
- * we'll need to make that an argument of the sort and med3 functions.
+ * we'll need to make that an argument of the sort functions.
*/
#ifdef ST_COMPARE_RUNTIME_POINTER
/*
@@ -161,8 +161,8 @@
/*
* If the user wants to use a compare function or expression that takes an
- * extra argument, we'll need to make that an argument of the sort, compare and
- * med3 functions.
+ * extra argument, we'll need to make that an argument of the sort and compare
+ * functions.
*/
#ifdef ST_COMPARE_ARG_TYPE
#define ST_SORT_PROTO_ARG , ST_COMPARE_ARG_TYPE *arg
@@ -174,7 +174,7 @@
/* Default input size threshold to control algorithm choice. */
#ifndef ST_INSERTION_SORT_THRESHOLD
-#define ST_INSERTION_SORT_THRESHOLD 7
+#define ST_INSERTION_SORT_THRESHOLD 12
#endif
#ifdef ST_DECLARE
@@ -195,7 +195,6 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n
#ifdef ST_DEFINE
/* sort private helper functions */
-#define ST_MED3 ST_MAKE_NAME(ST_SORT, med3)
#define ST_SORT_INTERNAL ST_MAKE_NAME(ST_SORT, internal)
#define ST_SWAP ST_MAKE_NAME(ST_SORT, swap)
#define ST_SWAPN ST_MAKE_NAME(ST_SORT, swapn)
@@ -208,8 +207,8 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n
#endif
/*
- * Create wrapper macros that know how to invoke compare, med3 and sort with
- * the right arguments.
+ * Create wrapper macros that know how to invoke compare and sort with the
+ * right arguments.
*/
#ifdef ST_COMPARE_RUNTIME_POINTER
#define DO_COMPARE(a_, b_) ST_COMPARE((a_), (b_) ST_SORT_INVOKE_ARG)
@@ -218,10 +217,6 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n
#else
#define DO_COMPARE(a_, b_) ST_COMPARE((a_), (b_))
#endif
-#define DO_MED3(a_, b_, c_) \
- ST_MED3((a_), (b_), (c_) \
- ST_SORT_INVOKE_COMPARE \
- ST_SORT_INVOKE_ARG)
#define DO_SORT(a_, n_) \
ST_SORT_INTERNAL((a_), (n_) \
ST_SORT_INVOKE_ELEMENT_SIZE \
@@ -245,23 +240,6 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n
#define DO_SWAP(a_, b_) DO_SWAPN((a_), (b_), element_size)
#endif
-/*
- * Find the median of three values. Currently, performance seems to be best
- * if the comparator is inlined here, but the med3 function is not inlined
- * in the qsort function.
- */
-static pg_noinline ST_ELEMENT_TYPE *
-ST_MED3(ST_ELEMENT_TYPE * a,
- ST_ELEMENT_TYPE * b,
- ST_ELEMENT_TYPE * c
- ST_SORT_PROTO_COMPARE
- ST_SORT_PROTO_ARG)
-{
- return DO_COMPARE(a, b) < 0 ?
- (DO_COMPARE(b, c) < 0 ? b : (DO_COMPARE(a, c) < 0 ? c : a))
- : (DO_COMPARE(b, c) > 0 ? b : (DO_COMPARE(a, c) < 0 ? a : c));
-}
-
static inline void
ST_SWAP(ST_POINTER_TYPE * a, ST_POINTER_TYPE * b)
{
@@ -288,6 +266,18 @@ ST_SORT_INTERNAL(ST_POINTER_TYPE * data, size_t n
ST_SORT_PROTO_ARG)
{
ST_POINTER_TYPE *a = data,
+ *left,
+ *right,
+ *e1,
+ *e2,
+ *e3,
+ *e4,
+ *e5,
+ *pivot1,
+ *pivot2,
+ *less,
+ *great,
+ *k,
*pa,
*pb,
*pc,
@@ -296,9 +286,12 @@ ST_SORT_INTERNAL(ST_POINTER_TYPE * data, size_t n
*pm,
*pn;
size_t d1,
- d2;
+ d2,
+ d3,
+ seventh;
int r,
presorted;
+ bool unique_hint = true;
loop:
DO_CHECK_FOR_INTERRUPTS();
@@ -324,22 +317,164 @@ loop:
}
if (presorted)
return;
- pm = a + (n / 2) * ST_POINTER_STEP;
- if (n > 7)
+
+ left = a;
+ right = a + (n - 1) * ST_POINTER_STEP;
+
+#ifdef QSORT_DEBUG
+ elog(NOTICE, "start:");
+
+ for (ST_POINTER_TYPE *i = left; i <= right; i += ST_POINTER_STEP)
{
- pl = a;
- pn = a + (n - 1) * ST_POINTER_STEP;
- if (n > 40)
+ elog(NOTICE, "%d ", *i);
+ }
+#endif
+
+ /* select five pivot candidates spaced equally around the midpoint */
+ seventh = n / 7 * ST_POINTER_STEP;
+ e3 = a + (n / 2) * ST_POINTER_STEP;
+ e2 = e3 - seventh;
+ e1 = e2 - seventh;
+ e4 = e3 + seventh;
+ e5 = e4 + seventh;
+
+ /* do insertion sort on pivot candidates */
+ for (pm = e2; pm <= e5; pm += seventh)
+ for (pl = pm; pl > e1 && (r = DO_COMPARE(pl - seventh, pl)) >= 0;
+ pl -= seventh)
+ {
+ if (r == 0)
+ {
+ /* found two equal candidates */
+ unique_hint = false;
+ break;
+ }
+ else
+ DO_SWAP(pl, pl - seventh);
+ }
+
+ /*
+ * If the pivot candidates were all unique, use dual-pivot quicksort,
+ * otherwise use B&M quicksort since it is faster on inputs with many
+ * duplicates.
+ */
+ if (unique_hint)
+ {
+ DO_SWAP(e2, left);
+ DO_SWAP(e4, right);
+
+ pivot1 = left;
+ pivot2 = right;
+ less = left + ST_POINTER_STEP;
+ great = right - ST_POINTER_STEP;
+
+ /*
+ * dual-pivot partitioning
+ *
+ * left part center part right part
+ * +--------------------------------------------------------------+
+ * | < pivot1 | pivot1 <= && <= pivot2 | ? | > pivot2 |
+ * +--------------------------------------------------------------+
+ * ^ ^ ^
+ * | | |
+ * less k great
+ *
+ * Invariants:
+ *
+ * all in (left, less) < pivot1
+ * pivot1 <= all in [less, k) <= pivot2
+ * all in (great, right) > pivot2
+ *
+ * k points to the first element in the "?" part
+ */
+ for (k = less; k <= great; k += ST_POINTER_STEP)
{
- size_t d = (n / 8) * ST_POINTER_STEP;
+ if (DO_COMPARE(k, pivot1) < 0)
+ {
+ if (k > less)
+ DO_SWAP(k, less);
+ less += ST_POINTER_STEP;
+ }
+ else if (DO_COMPARE(k, pivot2) > 0)
+ {
+ while (k < great && DO_COMPARE(great, pivot2) > 0)
+ {
+ great-= ST_POINTER_STEP;
+ DO_CHECK_FOR_INTERRUPTS();
+ }
+
+ DO_SWAP(k, great);
+ great-= 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);
+ if (DO_COMPARE(k, pivot1) < 0)
+ {
+ if (k > less)
+ DO_SWAP(k, less);
+ less += ST_POINTER_STEP;
+ }
+ }
+ DO_CHECK_FOR_INTERRUPTS();
+ }
+
+ DO_SWAP(less - ST_POINTER_STEP, pivot1);
+ DO_SWAP(great + ST_POINTER_STEP, pivot2);
+
+ d1 = (less - ST_POINTER_STEP) - left;
+ d2 = (great + ST_POINTER_STEP) - less;
+ d3 = right - (great + ST_POINTER_STEP);
+
+ /* recurse on shorter subarrays to save stack space */
+ if (d1 > d2)
+ {
+ /* recurse on d2 */
+ DO_SORT(less, d2 / ST_POINTER_STEP);
+ if (d1 > d3)
+ {
+ /* recurse on d3 */
+ DO_SORT(great + 2 * ST_POINTER_STEP, d3 / ST_POINTER_STEP);
+ /* iterate on d1 */
+ /* DO_SORT(left, d1 / ST_POINTER_STEP) */
+ n = d1 / ST_POINTER_STEP;
+ goto loop;
+ }
+ else
+ {
+ goto recurse_d1;
+ }
+ }
+ else
+ {
+recurse_d1:
+ /* recurse on d1 */
+ DO_SORT(left, d1 / ST_POINTER_STEP);
+ if (d2 > d3)
+ {
+ /* recurse on d3 */
+ DO_SORT(great + 2 * ST_POINTER_STEP, d3 / ST_POINTER_STEP);
+ /* iterate on d2 */
+ /* DO_SORT(less, d2 / ST_POINTER_STEP) */
+ a = less;
+ n = d2 / ST_POINTER_STEP;
+ goto loop;
+ }
+ else
+ {
+ /* recurse on d2 */
+ DO_SORT(less, d2 / ST_POINTER_STEP);
+ /* iterate on d3 */
+ /* DO_SORT(great + 2 * ST_POINTER_STEP, d3 / ST_POINTER_STEP) */
+ a = great + 2 * ST_POINTER_STEP;
+ n = d3 / ST_POINTER_STEP;
+ goto loop;
+ }
}
- pm = DO_MED3(pl, pm, pn);
}
- DO_SWAP(a, pm);
+ else
+ /* classic B&M quicksort with a single pivot */
+ // WIP: clean up variables to match dual pivot more closely
+ {
+ /* use median of five for the pivot */
+ DO_SWAP(a, e3);
pa = pb = a + ST_POINTER_STEP;
pc = pd = a + (n - 1) * ST_POINTER_STEP;
for (;;)
@@ -404,6 +539,7 @@ loop:
goto loop;
}
}
+ }
}
/*
@@ -433,7 +569,6 @@ ST_SORT(ST_ELEMENT_TYPE * data, size_t n
#undef DO_CHECK_FOR_INTERRUPTS
#undef DO_COMPARE
-#undef DO_MED3
#undef DO_SORT
#undef DO_SWAP
#undef DO_SWAPN
@@ -444,10 +579,10 @@ ST_SORT(ST_ELEMENT_TYPE * data, size_t n
#undef ST_COMPARE_RUNTIME_POINTER
#undef ST_ELEMENT_TYPE
#undef ST_ELEMENT_TYPE_VOID
+#undef ST_INSERTION_SORT_THRESHOLD
#undef ST_MAKE_NAME
#undef ST_MAKE_NAME_
#undef ST_MAKE_PREFIX
-#undef ST_MED3
#undef ST_POINTER_STEP
#undef ST_POINTER_TYPE
#undef ST_SCOPE
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..5eb0b29b16 100644
--- a/src/test/regress/expected/geometry.out
+++ b/src/test/regress/expected/geometry.out
@@ -4308,8 +4308,8 @@ SELECT c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS \
distance <(100,200),10> | (-5,-12) | 226.577682802
<(3,5),0> | (1e+300,Infinity) | Infinity
<(3,5),0> | (Infinity,1e+300) | Infinity
- <(1,2),3> | (1e+300,Infinity) | Infinity
<(5,1),3> | (1e+300,Infinity) | Infinity
+ <(1,2),3> | (1e+300,Infinity) | Infinity
<(5,1),3> | (Infinity,1e+300) | Infinity
<(1,2),3> | (Infinity,1e+300) | Infinity
<(1,3),5> | (1e+300,Infinity) | Infinity
@@ -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 fcad5c4093..57dbb65a82 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -92,14 +92,14 @@ select a, b, grouping(a,b), sum(v), count(*), max(v)
---+---+----------+-----+-------+-----
| | 3 | 145 | 10 | 19
1 | | 1 | 60 | 5 | 14
- 1 | 1 | 0 | 21 | 2 | 11
2 | | 1 | 15 | 1 | 15
+ 1 | 1 | 0 | 21 | 2 | 11
3 | | 1 | 33 | 2 | 17
1 | 2 | 0 | 25 | 2 | 13
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..a02ac66298 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
@@ -451,8 +451,8 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY \
i;
--------------------+------------------
10.0.0.0/8 | 9.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.0.0.0/32 | 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
10.1.2.3/32 | 10.1.2.3
@@ -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
@@ -599,8 +599,8 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY \
i;
--------------------+------------------
10.0.0.0/8 | 9.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.0.0.0/32 | 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
10.1.2.3/32 | 10.1.2.3
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2538bd6a79..720d4ca4e8 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/merge.out \
b/src/test/regress/expected/merge.out index af670e28e7..a2a4aaaf85 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1554,11 +1554,11 @@ SELECT * FROM pa_target ORDER BY tid;
5 | 500 | initial
5 | 50 | inserted by merge
6 | 60 | inserted by merge
- 7 | 700 | initial
7 | 70 | inserted by merge
+ 7 | 700 | initial
8 | 80 | inserted by merge
- 9 | 90 | inserted by merge
9 | 900 | initial
+ 9 | 90 | inserted by merge
10 | 100 | inserted by merge
11 | 1100 | initial
11 | 110 | inserted by merge
@@ -1583,12 +1583,12 @@ SELECT * FROM pa_target ORDER BY tid;
-----+---------+--------------------------
2 | 110 | initial updated by merge
2 | 20 | inserted by merge
- 4 | 40 | inserted by merge
4 | 330 | initial updated by merge
- 6 | 550 | initial updated by merge
+ 4 | 40 | inserted by merge
6 | 60 | inserted by merge
- 8 | 80 | inserted by merge
+ 6 | 550 | initial updated by merge
8 | 770 | initial updated by merge
+ 8 | 80 | inserted by merge
10 | 990 | initial updated by merge
10 | 100 | inserted by merge
12 | 1210 | initial updated by merge
@@ -1658,18 +1658,18 @@ SELECT * FROM pa_target ORDER BY tid;
-----+---------+--------------------------
1 | 110 | initial updated by merge
2 | 20 | inserted by merge
- 3 | 30 | inserted by merge
3 | 300 | initial
+ 3 | 30 | inserted by merge
4 | 40 | inserted by merge
6 | 60 | inserted by merge
- 7 | 700 | initial
7 | 70 | inserted by merge
+ 7 | 700 | initial
8 | 80 | inserted by merge
9 | 900 | initial
9 | 90 | inserted by merge
10 | 100 | inserted by merge
- 11 | 110 | inserted by merge
11 | 1100 | initial
+ 11 | 110 | inserted by merge
12 | 120 | inserted by merge
13 | 1300 | initial
13 | 130 | inserted by merge
@@ -1691,12 +1691,12 @@ SELECT * FROM pa_target ORDER BY tid;
-----+---------+--------------------------
2 | 110 | initial updated by merge
2 | 20 | inserted by merge
- 4 | 40 | inserted by merge
4 | 330 | initial updated by merge
- 6 | 550 | initial updated by merge
+ 4 | 40 | inserted by merge
6 | 60 | inserted by merge
- 8 | 80 | inserted by merge
+ 6 | 550 | initial updated by merge
8 | 770 | initial updated by merge
+ 8 | 80 | inserted by merge
10 | 990 | initial updated by merge
10 | 100 | inserted by merge
12 | 1210 | initial updated by merge
diff --git a/src/test/regress/expected/sqljson.out \
b/src/test/regress/expected/sqljson.out index 0883261535..db0f2c08ad 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..f4c87d7a17 100644
--- a/src/test/regress/expected/tsrf.out
+++ b/src/test/regress/expected/tsrf.out
@@ -348,11 +348,11 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM \
few GROUP BY CUBE(d SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few \
GROUP BY CUBE(dataa, datab) ORDER BY g; dataa | b | g | count
-------+-----+---+-------
- a | bar | 1 | 1
+ b | | 1 | 1
a | foo | 1 | 1
a | | 1 | 2
b | bar | 1 | 1
- b | | 1 | 1
+ a | bar | 1 | 1
| | 1 | 3
| bar | 1 | 2
| foo | 1 | 1
@@ -398,59 +398,59 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM \
few GROUP BY CUBE(d SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few \
GROUP BY CUBE(dataa, datab, g) ORDER BY dataa; dataa | b | g | count
-------+-----+---+-------
- a | foo | | 2
- a | | | 4
+ a | | 1 | 2
+ a | foo | 1 | 1
a | | 2 | 2
a | bar | 1 | 1
+ a | foo | 2 | 1
+ a | foo | | 2
+ a | | | 4
a | bar | 2 | 1
a | bar | | 2
- a | foo | 1 | 1
- a | foo | 2 | 1
- a | | 1 | 2
+ b | bar | | 2
b | bar | 1 | 1
+ b | bar | 2 | 1
b | | | 2
b | | 1 | 1
- b | bar | 2 | 1
- b | bar | | 2
b | | 2 | 1
- | | 2 | 3
- | | | 6
| bar | 1 | 2
- | bar | 2 | 2
- | bar | | 4
- | foo | 1 | 1
| foo | 2 | 1
| foo | | 2
+ | foo | 1 | 1
+ | | 2 | 3
| | 1 | 3
+ | | | 6
+ | bar | 2 | 2
+ | bar | | 4
(24 rows)
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY \
CUBE(dataa, datab, g) ORDER BY g; dataa | b | g | count
-------+-----+---+-------
- a | bar | 1 | 1
+ a | | 1 | 2
a | foo | 1 | 1
- b | bar | 1 | 1
| bar | 1 | 2
+ b | bar | 1 | 1
| foo | 1 | 1
- a | | 1 | 2
+ a | bar | 1 | 1
b | | 1 | 1
| | 1 | 3
+ a | foo | 2 | 1
+ | | 2 | 3
a | | 2 | 2
- 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 | bar | 2 | 1
+ b | | 2 | 1
+ a | foo | | 2
b | bar | | 2
b | | | 2
| | | 6
- a | foo | | 2
- a | bar | | 2
| bar | | 4
| foo | | 2
+ a | bar | | 2
+ a | | | 4
(24 rows)
reset enable_hashagg;
@@ -533,9 +533,9 @@ SELECT DISTINCT ON (a) a, b, generate_series(1,3) g
FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b);
a | b | g
---+---+---
- 1 | 1 | 1
- 3 | 2 | 1
- 5 | 3 | 1
+ 1 | 4 | 3
+ 3 | 2 | 3
+ 5 | 3 | 3
(3 rows)
-- unreferenced in DISTINCT ON or ORDER BY
@@ -597,9 +597,9 @@ SELECT DISTINCT ON (g) a, b, generate_series(1,3) g
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
+ 1 | 1 | 1
+ 5 | 3 | 2
+ 1 | 1 | 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..b3ad4cc5f3 100644
--- a/src/test/regress/expected/tuplesort.out
+++ b/src/test/regress/expected/tuplesort.out
@@ -242,9 +242,9 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing \
| noabort_increasing | noabort_decreasing \
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 20003 | | \
| |
0 | | \
| | 20002 | \
| | \
|
- 20003 | | \
| | 20001 | \
00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | \
00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 20010 | \
00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | \
00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 (5 rows)
@@ -260,11 +260,11 @@ FROM abbrev_abort_uuids
ORDER BY ctid LIMIT 5;
id | abort_increasing | abort_decreasing \
| noabort_increasing | noabort_decreasing \
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 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 + 20010 | \
00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | \
00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 20000 | \
00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | \
00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 19999 | \
00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002 | \
00009989-0000-0000-0000-000000019998 | \
00000002-0000-0000-0000-000000000002
- 20009 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 \
| 00009988-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 + 19998 \
| 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 | \
00009988-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 (5 rows)
-- tail
@@ -273,9 +273,9 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing \
| noabort_increasing | noabort_decreasing \
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | \
| |
- 20002 | | \
| | 20003 | \
| | | + \
20002 | | | \
| + 0 | | \
| |
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 (5 \
rows) @@ -304,8 +304,8 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing \
| noabort_increasing | noabort_decreasing \
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | \
| | 20002 | \
| | | + \
0 | | | \
| 20003 | | \
| | 10009 | \
00000000-0000-0000-0000-000000010008 | 00000000-0000-0000-0000-000000009992 | \
00010008-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 10008 | \
00000000-0000-0000-0000-000000010007 | 00000000-0000-0000-0000-000000009993 | \
00010007-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 @@ -322,8 \
+322,8 @@ FROM abbrev_abort_uuids ORDER BY ctid LIMIT 5;
id | abort_increasing | abort_decreasing \
| noabort_increasing | noabort_decreasing \
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 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 + 20010 | \
00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | \
00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 9992 | \
00000000-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 | \
00009991-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 20000 | \
00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | \
00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 9991 | \
00000000-0000-0000-0000-000000009990 | 00000000-0000-0000-0000-000000010010 | \
00009990-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010 @@ -335,9 \
+335,9 @@ FROM abbrev_abort_uuids ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing \
| noabort_increasing | noabort_decreasing \
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 20002 | | \
| |
0 | | \
| | 20003 | \
| | \
|
- 20002 | | \
| | 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 433a0bb025..aa173e5318 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
@@ -3686,7 +3686,7 @@ SELECT * FROM
depname | empno | salary | enroll_date | c1 | rn | c2 | c3
-----------+-------+--------+-------------+----+----+----+----
personnel | 5 | 3500 | 12-10-2007 | 2 | 1 | 2 | 2
- sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3
+ sales | 1 | 5000 | 10-01-2006 | 3 | 1 | 3 | 3
(2 rows)
-- Tests to ensure we don't push down the run condition when it's not valid to
@@ -3824,7 +3824,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.36.1
["v2-dual-vs-single-12.ods" (application/vnd.oasis.opendocument.spreadsheet)]
["dualpivot-thresholds-microbenchmark-20220630-graph.ods" (application/vnd.oasis.opendocument.spreadsheet)]
["singlepivot-thresholds-microbenchmark-20220630-graph.ods" (application/vnd.oasis.opendocument.spreadsheet)]
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic