diff options
Diffstat (limited to 'src/test/regress/expected/tuplesort.out')
-rw-r--r-- | src/test/regress/expected/tuplesort.out | 686 |
1 files changed, 686 insertions, 0 deletions
diff --git a/src/test/regress/expected/tuplesort.out b/src/test/regress/expected/tuplesort.out new file mode 100644 index 0000000..418f296 --- /dev/null +++ b/src/test/regress/expected/tuplesort.out @@ -0,0 +1,686 @@ +-- only use parallelism when explicitly intending to do so +SET max_parallel_maintenance_workers = 0; +SET max_parallel_workers = 0; +-- A table with contents that, when sorted, triggers abbreviated +-- key aborts. One easy way to achieve that is to use uuids that all +-- have the same prefix, as abbreviated keys for uuids just use the +-- first sizeof(Datum) bytes. +CREATE TEMP TABLE abbrev_abort_uuids ( + id serial not null, + abort_increasing uuid, + abort_decreasing uuid, + noabort_increasing uuid, + noabort_decreasing uuid); +INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing) + SELECT + ('00000000-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid abort_increasing, + ('00000000-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid abort_decreasing, + (to_char(g.i % 10009, '00000000FM')||'-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid noabort_increasing, + (to_char(((20000 - g.i) % 10009), '00000000FM')||'-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid noabort_decreasing + FROM generate_series(0, 20000, 1) g(i); +-- and a few NULLs +INSERT INTO abbrev_abort_uuids(id) VALUES(0); +INSERT INTO abbrev_abort_uuids DEFAULT VALUES; +INSERT INTO abbrev_abort_uuids DEFAULT VALUES; +-- add just a few duplicates +INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing) + SELECT abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing + FROM abbrev_abort_uuids + WHERE (id < 10 OR id > 19990) AND id % 3 = 0 AND abort_increasing is not null; +---- +-- Check sort node uses of tuplesort wrt. abbreviated keys +---- +-- plain sort triggering abbreviated abort +SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing OFFSET 20000 - 4; + abort_increasing | abort_decreasing +--------------------------------------+-------------------------------------- + 00000000-0000-0000-0000-000000019992 | 00000000-0000-0000-0000-000000000008 + 00000000-0000-0000-0000-000000019993 | 00000000-0000-0000-0000-000000000007 + 00000000-0000-0000-0000-000000019994 | 00000000-0000-0000-0000-000000000006 + 00000000-0000-0000-0000-000000019994 | 00000000-0000-0000-0000-000000000006 + 00000000-0000-0000-0000-000000019995 | 00000000-0000-0000-0000-000000000005 + 00000000-0000-0000-0000-000000019996 | 00000000-0000-0000-0000-000000000004 + 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 + 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 + 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002 + 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 + 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + | + | + | +(15 rows) + +SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing NULLS FIRST OFFSET 20000 - 4; + abort_increasing | abort_decreasing +--------------------------------------+-------------------------------------- + 00000000-0000-0000-0000-000000000011 | 00000000-0000-0000-0000-000000019989 + 00000000-0000-0000-0000-000000000010 | 00000000-0000-0000-0000-000000019990 + 00000000-0000-0000-0000-000000000009 | 00000000-0000-0000-0000-000000019991 + 00000000-0000-0000-0000-000000000008 | 00000000-0000-0000-0000-000000019992 + 00000000-0000-0000-0000-000000000008 | 00000000-0000-0000-0000-000000019992 + 00000000-0000-0000-0000-000000000007 | 00000000-0000-0000-0000-000000019993 + 00000000-0000-0000-0000-000000000006 | 00000000-0000-0000-0000-000000019994 + 00000000-0000-0000-0000-000000000005 | 00000000-0000-0000-0000-000000019995 + 00000000-0000-0000-0000-000000000005 | 00000000-0000-0000-0000-000000019995 + 00000000-0000-0000-0000-000000000004 | 00000000-0000-0000-0000-000000019996 + 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997 + 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 + 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 + 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 + 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 +(15 rows) + +-- plain sort not triggering abbreviated abort +SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing OFFSET 20000 - 4; + noabort_increasing | noabort_decreasing +--------------------------------------+-------------------------------------- + 00009997-0000-0000-0000-000000009997 | 00010003-0000-0000-0000-000000010003 + 00009998-0000-0000-0000-000000009998 | 00010002-0000-0000-0000-000000010002 + 00009999-0000-0000-0000-000000009999 | 00010001-0000-0000-0000-000000010001 + 00010000-0000-0000-0000-000000010000 | 00010000-0000-0000-0000-000000010000 + 00010001-0000-0000-0000-000000010001 | 00009999-0000-0000-0000-000000009999 + 00010002-0000-0000-0000-000000010002 | 00009998-0000-0000-0000-000000009998 + 00010003-0000-0000-0000-000000010003 | 00009997-0000-0000-0000-000000009997 + 00010004-0000-0000-0000-000000010004 | 00009996-0000-0000-0000-000000009996 + 00010005-0000-0000-0000-000000010005 | 00009995-0000-0000-0000-000000009995 + 00010006-0000-0000-0000-000000010006 | 00009994-0000-0000-0000-000000009994 + 00010007-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 + 00010008-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 + | + | + | +(15 rows) + +SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing NULLS FIRST OFFSET 20000 - 4; + noabort_increasing | noabort_decreasing +--------------------------------------+-------------------------------------- + 00010006-0000-0000-0000-000000010006 | 00009994-0000-0000-0000-000000009994 + 00010005-0000-0000-0000-000000010005 | 00009995-0000-0000-0000-000000009995 + 00010004-0000-0000-0000-000000010004 | 00009996-0000-0000-0000-000000009996 + 00010003-0000-0000-0000-000000010003 | 00009997-0000-0000-0000-000000009997 + 00010002-0000-0000-0000-000000010002 | 00009998-0000-0000-0000-000000009998 + 00010001-0000-0000-0000-000000010001 | 00009999-0000-0000-0000-000000009999 + 00010000-0000-0000-0000-000000010000 | 00010000-0000-0000-0000-000000010000 + 00009999-0000-0000-0000-000000009999 | 00010001-0000-0000-0000-000000010001 + 00009998-0000-0000-0000-000000009998 | 00010002-0000-0000-0000-000000010002 + 00009997-0000-0000-0000-000000009997 | 00010003-0000-0000-0000-000000010003 + 00009996-0000-0000-0000-000000009996 | 00010004-0000-0000-0000-000000010004 + 00009995-0000-0000-0000-000000009995 | 00010005-0000-0000-0000-000000010005 + 00009994-0000-0000-0000-000000009994 | 00010006-0000-0000-0000-000000010006 + 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007 + 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008 +(15 rows) + +-- bounded sort (disables abbreviated keys) +SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5; + abort_increasing | noabort_increasing +--------------------------------------+-------------------------------------- + 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000000000 + 00000000-0000-0000-0000-000000000001 | 00000001-0000-0000-0000-000000000001 + 00000000-0000-0000-0000-000000000002 | 00000002-0000-0000-0000-000000000002 + 00000000-0000-0000-0000-000000000002 | 00000002-0000-0000-0000-000000000002 + 00000000-0000-0000-0000-000000000003 | 00000003-0000-0000-0000-000000000003 +(5 rows) + +SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY noabort_increasing NULLS FIRST LIMIT 5; + abort_increasing | noabort_increasing +--------------------------------------+-------------------------------------- + | + | + | + 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000000000 + 00000000-0000-0000-0000-000000010009 | 00000000-0000-0000-0000-000000010009 +(5 rows) + +---- +-- Check index creation uses of tuplesort wrt. abbreviated keys +---- +-- index creation using abbreviated keys successfully +CREATE INDEX abbrev_abort_uuids__noabort_increasing_idx ON abbrev_abort_uuids (noabort_increasing); +CREATE INDEX abbrev_abort_uuids__noabort_decreasing_idx ON abbrev_abort_uuids (noabort_decreasing); +-- verify +EXPLAIN (COSTS OFF) +SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5; + QUERY PLAN +----------------------------------------------------------------------------------------- + Limit + -> Index Scan using abbrev_abort_uuids__noabort_increasing_idx on abbrev_abort_uuids +(2 rows) + +SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5; + id | noabort_increasing | noabort_decreasing +-------+--------------------------------------+-------------------------------------- + 1 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 + 10010 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991 + 2 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 + 10011 | 00000001-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990 + 3 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998 +(5 rows) + +EXPLAIN (COSTS OFF) +SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5; + QUERY PLAN +----------------------------------------------------------------------------------------- + Limit + -> Index Scan using abbrev_abort_uuids__noabort_decreasing_idx on abbrev_abort_uuids +(2 rows) + +SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5; + id | noabort_increasing | noabort_decreasing +-------+--------------------------------------+-------------------------------------- + 20001 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + 20010 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + 9992 | 00009991-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 + 20000 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 + 9991 | 00009990-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010 +(5 rows) + +-- index creation using abbreviated keys, hitting abort +CREATE INDEX abbrev_abort_uuids__abort_increasing_idx ON abbrev_abort_uuids (abort_increasing); +CREATE INDEX abbrev_abort_uuids__abort_decreasing_idx ON abbrev_abort_uuids (abort_decreasing); +-- verify +EXPLAIN (COSTS OFF) +SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5; + QUERY PLAN +--------------------------------------------------------------------------------------- + Limit + -> Index Scan using abbrev_abort_uuids__abort_increasing_idx on abbrev_abort_uuids +(2 rows) + +SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5; + id | abort_increasing | abort_decreasing +-------+--------------------------------------+-------------------------------------- + 1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 + 2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 + 3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 + 20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 + 4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997 +(5 rows) + +EXPLAIN (COSTS OFF) +SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5; + QUERY PLAN +--------------------------------------------------------------------------------------- + Limit + -> Index Scan using abbrev_abort_uuids__abort_decreasing_idx on abbrev_abort_uuids +(2 rows) + +SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5; + id | abort_increasing | abort_decreasing +-------+--------------------------------------+-------------------------------------- + 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + 20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 + 19999 | 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002 + 19998 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 +(5 rows) + +---- +-- Check CLUSTER uses of tuplesort wrt. abbreviated keys +---- +-- when aborting, increasing order +BEGIN; +SET LOCAL enable_indexscan = false; +CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_increasing_idx; +-- head +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +FROM abbrev_abort_uuids +ORDER BY ctid LIMIT 5; + id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing +-------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- + 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 + 4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997 +(5 rows) + +-- tail +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +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 + 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 +(5 rows) + +ROLLBACK; +-- when aborting, decreasing order +BEGIN; +SET LOCAL enable_indexscan = false; +CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_decreasing_idx; +-- head +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +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 + 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 +(5 rows) + +-- tail +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +FROM abbrev_abort_uuids +ORDER BY ctid DESC LIMIT 5; + id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing +-------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- + 0 | | | | + 20002 | | | | + 20003 | | | | + 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) + +ROLLBACK; +-- when not aborting, increasing order +BEGIN; +SET LOCAL enable_indexscan = false; +CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_increasing_idx; +-- head +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +FROM abbrev_abort_uuids +ORDER BY ctid LIMIT 5; + id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing +-------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- + 1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 + 10010 | 00000000-0000-0000-0000-000000010009 | 00000000-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991 + 2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 + 10011 | 00000000-0000-0000-0000-000000010010 | 00000000-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990 + 20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998 +(5 rows) + +-- tail +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +FROM abbrev_abort_uuids +ORDER BY ctid DESC LIMIT 5; + id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing +-------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- + 0 | | | | + 20002 | | | | + 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 +(5 rows) + +ROLLBACK; +-- when no aborting, decreasing order +BEGIN; +SET LOCAL enable_indexscan = false; +CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_decreasing_idx; +-- head +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +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 + 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 +(5 rows) + +-- tail +SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing +FROM abbrev_abort_uuids +ORDER BY ctid DESC LIMIT 5; + id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing +-------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- + 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) + +ROLLBACK; +---- +-- test forward and backward scans for in-memory and disk based tuplesort +---- +-- in-memory +BEGIN; +SET LOCAL enable_indexscan = false; +-- unfortunately can't show analyze output confirming sort method, +-- the memory used output wouldn't be stable +EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing; + QUERY PLAN +-------------------------------------- + Sort + Sort Key: noabort_decreasing + -> Seq Scan on abbrev_abort_uuids +(3 rows) + +DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing; +-- first and second +FETCH NEXT FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +-- scroll beyond beginning +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +-- scroll beyond end end +FETCH LAST FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- +(0 rows) + +COMMIT; +-- disk based +BEGIN; +SET LOCAL enable_indexscan = false; +SET LOCAL work_mem = '100kB'; +-- unfortunately can't show analyze output confirming sort method, +-- the memory used output wouldn't be stable +EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing; + QUERY PLAN +-------------------------------------- + Sort + Sort Key: noabort_decreasing + -> Seq Scan on abbrev_abort_uuids +(3 rows) + +DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing; +-- first and second +FETCH NEXT FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +-- scroll beyond beginning +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------------------------- + 00000000-0000-0000-0000-000000000000 +(1 row) + +-- scroll beyond end end +FETCH LAST FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- +(0 rows) + +FETCH BACKWARD FROM c; + noabort_decreasing +-------------------- + +(1 row) + +FETCH NEXT FROM c; + noabort_decreasing +-------------------- +(0 rows) + +COMMIT; +---- +-- test tuplesort using both in-memory and disk sort +--- +-- memory based +SELECT + -- fixed-width by-value datum + (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5], + -- fixed-width by-ref datum + (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5], + -- variable-width datum + (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5], + -- fixed width by-value datum tuplesort + percentile_disc(0.99) WITHIN GROUP (ORDER BY id), + -- ensure state is shared + percentile_disc(0.01) WITHIN GROUP (ORDER BY id), + -- fixed width by-ref datum tuplesort + percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing), + -- variable width by-ref datum tuplesort + percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text), + -- multi-column tuplesort + rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text) +FROM ( + SELECT * FROM abbrev_abort_uuids + UNION ALL + SELECT NULL, NULL, NULL, NULL, NULL) s; + array_agg | array_agg | array_agg | percentile_disc | percentile_disc | percentile_disc | percentile_disc | rank +--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------------+-----------------+--------------------------------------+-----------------+------ + {NULL,20010,20009,20008,20007} | {00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000019999,00000000-0000-0000-0000-000000019998,00000000-0000-0000-0000-000000019997} | {9999,9998,9997,9996,9995} | 19810 | 200 | 00000000-0000-0000-0000-000000016003 | 136 | 2 +(1 row) + +-- disk based (see also above) +BEGIN; +SET LOCAL work_mem = '100kB'; +SELECT + (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5], + (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5], + (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5], + percentile_disc(0.99) WITHIN GROUP (ORDER BY id), + percentile_disc(0.01) WITHIN GROUP (ORDER BY id), + percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing), + percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text), + rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text) +FROM ( + SELECT * FROM abbrev_abort_uuids + UNION ALL + SELECT NULL, NULL, NULL, NULL, NULL) s; + array_agg | array_agg | array_agg | percentile_disc | percentile_disc | percentile_disc | percentile_disc | rank +--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------------+-----------------+--------------------------------------+-----------------+------ + {NULL,20010,20009,20008,20007} | {00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000019999,00000000-0000-0000-0000-000000019998,00000000-0000-0000-0000-000000019997} | {9999,9998,9997,9996,9995} | 19810 | 200 | 00000000-0000-0000-0000-000000016003 | 136 | 2 +(1 row) + +ROLLBACK; +---- +-- test tuplesort mark/restore +--- +CREATE TEMP TABLE test_mark_restore(col1 int, col2 int, col12 int); +-- need a few duplicates for mark/restore to matter +INSERT INTO test_mark_restore(col1, col2, col12) + SELECT a.i, b.i, a.i * b.i FROM generate_series(1, 500) a(i), generate_series(1, 5) b(i); +BEGIN; +SET LOCAL enable_nestloop = off; +SET LOCAL enable_hashjoin = off; +SET LOCAL enable_material = off; +-- set query into variable once, to avoid repetition of the fairly long query +SELECT $$ + SELECT col12, count(distinct a.col1), count(distinct a.col2), count(distinct b.col1), count(distinct b.col2), count(*) + FROM test_mark_restore a + JOIN test_mark_restore b USING(col12) + GROUP BY 1 + HAVING count(*) > 1 + ORDER BY 2 DESC, 1 DESC, 3 DESC, 4 DESC, 5 DESC, 6 DESC + LIMIT 10 +$$ AS qry \gset +-- test mark/restore with in-memory sorts +EXPLAIN (COSTS OFF) :qry; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Limit + -> Sort + Sort Key: (count(DISTINCT a.col1)) DESC, a.col12 DESC, (count(DISTINCT a.col2)) DESC, (count(DISTINCT b.col1)) DESC, (count(DISTINCT b.col2)) DESC, (count(*)) DESC + -> GroupAggregate + Group Key: a.col12 + Filter: (count(*) > 1) + -> Merge Join + Merge Cond: (a.col12 = b.col12) + -> Sort + Sort Key: a.col12 DESC + -> Seq Scan on test_mark_restore a + -> Sort + Sort Key: b.col12 DESC + -> Seq Scan on test_mark_restore b +(14 rows) + +:qry; + col12 | count | count | count | count | count +-------+-------+-------+-------+-------+------- + 480 | 5 | 5 | 5 | 5 | 25 + 420 | 5 | 5 | 5 | 5 | 25 + 360 | 5 | 5 | 5 | 5 | 25 + 300 | 5 | 5 | 5 | 5 | 25 + 240 | 5 | 5 | 5 | 5 | 25 + 180 | 5 | 5 | 5 | 5 | 25 + 120 | 5 | 5 | 5 | 5 | 25 + 60 | 5 | 5 | 5 | 5 | 25 + 960 | 4 | 4 | 4 | 4 | 16 + 900 | 4 | 4 | 4 | 4 | 16 +(10 rows) + +-- test mark/restore with on-disk sorts +SET LOCAL work_mem = '100kB'; +EXPLAIN (COSTS OFF) :qry; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Limit + -> Sort + Sort Key: (count(DISTINCT a.col1)) DESC, a.col12 DESC, (count(DISTINCT a.col2)) DESC, (count(DISTINCT b.col1)) DESC, (count(DISTINCT b.col2)) DESC, (count(*)) DESC + -> GroupAggregate + Group Key: a.col12 + Filter: (count(*) > 1) + -> Merge Join + Merge Cond: (a.col12 = b.col12) + -> Sort + Sort Key: a.col12 DESC + -> Seq Scan on test_mark_restore a + -> Sort + Sort Key: b.col12 DESC + -> Seq Scan on test_mark_restore b +(14 rows) + +:qry; + col12 | count | count | count | count | count +-------+-------+-------+-------+-------+------- + 480 | 5 | 5 | 5 | 5 | 25 + 420 | 5 | 5 | 5 | 5 | 25 + 360 | 5 | 5 | 5 | 5 | 25 + 300 | 5 | 5 | 5 | 5 | 25 + 240 | 5 | 5 | 5 | 5 | 25 + 180 | 5 | 5 | 5 | 5 | 25 + 120 | 5 | 5 | 5 | 5 | 25 + 60 | 5 | 5 | 5 | 5 | 25 + 960 | 4 | 4 | 4 | 4 | 16 + 900 | 4 | 4 | 4 | 4 | 16 +(10 rows) + +COMMIT; |