/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/tuplesort.out
705 строк
36 KB
John Naylor
Perform radix sort on SortTuples with pass-by-value Datums
14 фев 2026, 09:50
14 фев 2026, 09:50
ef3c3cf
Код
Авторство
О чём код?
-- 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 -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- 20002 | | | | 20003 | | | | 0 | | | | 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 -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- 20002 | | | | 20003 | | | | 0 | | | | 9993 | 00000000-0000-0000-0000-000000009992 | 00000000-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008 9994 | 00000000-0000-0000-0000-000000009993 | 00000000-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007 (5 rows) ROLLBACK; ---- -- test sorting of large datums VALUES ---- -- Ensure the order is correct and values look intact SELECT LEFT(a,10),b FROM (VALUES(REPEAT('a', 512 * 1024),1),(REPEAT('b', 512 * 1024),2)) v(a,b) ORDER BY v.a DESC; left | b ------------+--- bbbbbbbbbb | 2 aaaaaaaaaa | 1 (2 rows) ---- -- 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 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 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) -> Incremental Sort Sort Key: a.col12 DESC, a.col1 Presorted Key: a.col12 -> 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 (17 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) -> Incremental Sort Sort Key: a.col12 DESC, a.col1 Presorted Key: a.col12 -> 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 (17 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;