src/test/regress/expected/tuplesort.out

   1 -- only use parallelism when explicitly intending to do so
   2 SET max_parallel_maintenance_workers = 0;
   3 SET max_parallel_workers = 0;
   4 -- A table with contents that, when sorted, triggers abbreviated
   5 -- key aborts. One easy way to achieve that is to use uuids that all
   6 -- have the same prefix, as abbreviated keys for uuids just use the
   7 -- first sizeof(Datum) bytes.
   8 CREATE TEMP TABLE abbrev_abort_uuids (
   9     id serial not null,
  10     abort_increasing uuid,
  11     abort_decreasing uuid,
  12     noabort_increasing uuid,
  13     noabort_decreasing uuid);
  14 INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing)
  15     SELECT
  16         ('00000000-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid abort_increasing,
  17         ('00000000-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid abort_decreasing,
  18         (to_char(g.i % 10009, '00000000FM')||'-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid noabort_increasing,
  19         (to_char(((20000 - g.i) % 10009), '00000000FM')||'-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid noabort_decreasing
  20     FROM generate_series(0, 20000, 1) g(i);
  21 -- and a few NULLs
  22 INSERT INTO abbrev_abort_uuids(id) VALUES(0);
  23 INSERT INTO abbrev_abort_uuids DEFAULT VALUES;
  24 INSERT INTO abbrev_abort_uuids DEFAULT VALUES;
  25 -- add just a few duplicates
  26 INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing)
  27     SELECT abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
  28     FROM abbrev_abort_uuids
  29     WHERE (id < 10 OR id > 19990) AND id % 3 = 0 AND abort_increasing is not null;
  30 ----
  31 -- Check sort node uses of tuplesort wrt. abbreviated keys
  32 ----
  33 -- plain sort triggering abbreviated abort
  34 SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing OFFSET 20000 - 4;
  35            abort_increasing           |           abort_decreasing
  36 --------------------------------------+--------------------------------------
  37  00000000-0000-0000-0000-000000019992 | 00000000-0000-0000-0000-000000000008
  38  00000000-0000-0000-0000-000000019993 | 00000000-0000-0000-0000-000000000007
  39  00000000-0000-0000-0000-000000019994 | 00000000-0000-0000-0000-000000000006
  40  00000000-0000-0000-0000-000000019994 | 00000000-0000-0000-0000-000000000006
  41  00000000-0000-0000-0000-000000019995 | 00000000-0000-0000-0000-000000000005
  42  00000000-0000-0000-0000-000000019996 | 00000000-0000-0000-0000-000000000004
  43  00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003
  44  00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003
  45  00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002
  46  00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001
  47  00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
  48  00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
  49                                       |
  50                                       |
  51                                       |
  52 (15 rows)
  53
  54 SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing NULLS FIRST OFFSET 20000 - 4;
  55            abort_increasing           |           abort_decreasing
  56 --------------------------------------+--------------------------------------
  57  00000000-0000-0000-0000-000000000011 | 00000000-0000-0000-0000-000000019989
  58  00000000-0000-0000-0000-000000000010 | 00000000-0000-0000-0000-000000019990
  59  00000000-0000-0000-0000-000000000009 | 00000000-0000-0000-0000-000000019991
  60  00000000-0000-0000-0000-000000000008 | 00000000-0000-0000-0000-000000019992
  61  00000000-0000-0000-0000-000000000008 | 00000000-0000-0000-0000-000000019992
  62  00000000-0000-0000-0000-000000000007 | 00000000-0000-0000-0000-000000019993
  63  00000000-0000-0000-0000-000000000006 | 00000000-0000-0000-0000-000000019994
  64  00000000-0000-0000-0000-000000000005 | 00000000-0000-0000-0000-000000019995
  65  00000000-0000-0000-0000-000000000005 | 00000000-0000-0000-0000-000000019995
  66  00000000-0000-0000-0000-000000000004 | 00000000-0000-0000-0000-000000019996
  67  00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997
  68  00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
  69  00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
  70  00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999
  71  00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000
  72 (15 rows)
  73
  74 -- plain sort not triggering abbreviated abort
  75 SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing OFFSET 20000 - 4;
  76           noabort_increasing          |          noabort_decreasing
  77 --------------------------------------+--------------------------------------
  78  00009997-0000-0000-0000-000000009997 | 00010003-0000-0000-0000-000000010003
  79  00009998-0000-0000-0000-000000009998 | 00010002-0000-0000-0000-000000010002
  80  00009999-0000-0000-0000-000000009999 | 00010001-0000-0000-0000-000000010001
  81  00010000-0000-0000-0000-000000010000 | 00010000-0000-0000-0000-000000010000
  82  00010001-0000-0000-0000-000000010001 | 00009999-0000-0000-0000-000000009999
  83  00010002-0000-0000-0000-000000010002 | 00009998-0000-0000-0000-000000009998
  84  00010003-0000-0000-0000-000000010003 | 00009997-0000-0000-0000-000000009997
  85  00010004-0000-0000-0000-000000010004 | 00009996-0000-0000-0000-000000009996
  86  00010005-0000-0000-0000-000000010005 | 00009995-0000-0000-0000-000000009995
  87  00010006-0000-0000-0000-000000010006 | 00009994-0000-0000-0000-000000009994
  88  00010007-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993
  89  00010008-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992
  90                                       |
  91                                       |
  92                                       |
  93 (15 rows)
  94
  95 SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing NULLS FIRST OFFSET 20000 - 4;
  96           noabort_increasing          |          noabort_decreasing
  97 --------------------------------------+--------------------------------------
  98  00010006-0000-0000-0000-000000010006 | 00009994-0000-0000-0000-000000009994
  99  00010005-0000-0000-0000-000000010005 | 00009995-0000-0000-0000-000000009995
 100  00010004-0000-0000-0000-000000010004 | 00009996-0000-0000-0000-000000009996
 101  00010003-0000-0000-0000-000000010003 | 00009997-0000-0000-0000-000000009997
 102  00010002-0000-0000-0000-000000010002 | 00009998-0000-0000-0000-000000009998
 103  00010001-0000-0000-0000-000000010001 | 00009999-0000-0000-0000-000000009999
 104  00010000-0000-0000-0000-000000010000 | 00010000-0000-0000-0000-000000010000
 105  00009999-0000-0000-0000-000000009999 | 00010001-0000-0000-0000-000000010001
 106  00009998-0000-0000-0000-000000009998 | 00010002-0000-0000-0000-000000010002
 107  00009997-0000-0000-0000-000000009997 | 00010003-0000-0000-0000-000000010003
 108  00009996-0000-0000-0000-000000009996 | 00010004-0000-0000-0000-000000010004
 109  00009995-0000-0000-0000-000000009995 | 00010005-0000-0000-0000-000000010005
 110  00009994-0000-0000-0000-000000009994 | 00010006-0000-0000-0000-000000010006
 111  00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007
 112  00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008
 113 (15 rows)
 114
 115 -- bounded sort (disables abbreviated keys)
 116 SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
 117            abort_increasing           |          noabort_increasing
 118 --------------------------------------+--------------------------------------
 119  00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000000000
 120  00000000-0000-0000-0000-000000000001 | 00000001-0000-0000-0000-000000000001
 121  00000000-0000-0000-0000-000000000002 | 00000002-0000-0000-0000-000000000002
 122  00000000-0000-0000-0000-000000000002 | 00000002-0000-0000-0000-000000000002
 123  00000000-0000-0000-0000-000000000003 | 00000003-0000-0000-0000-000000000003
 124 (5 rows)
 125
 126 SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY noabort_increasing NULLS FIRST LIMIT 5;
 127            abort_increasing           |          noabort_increasing
 128 --------------------------------------+--------------------------------------
 129                                       |
 130                                       |
 131                                       |
 132  00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000000000
 133  00000000-0000-0000-0000-000000010009 | 00000000-0000-0000-0000-000000010009
 134 (5 rows)
 135
 136 ----
 137 -- Check index creation uses of tuplesort wrt. abbreviated keys
 138 ----
 139 -- index creation using abbreviated keys successfully
 140 CREATE INDEX abbrev_abort_uuids__noabort_increasing_idx ON abbrev_abort_uuids (noabort_increasing);
 141 CREATE INDEX abbrev_abort_uuids__noabort_decreasing_idx ON abbrev_abort_uuids (noabort_decreasing);
 142 -- verify
 143 EXPLAIN (COSTS OFF)
 144 SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5;
 145                                        QUERY PLAN
 146 -----------------------------------------------------------------------------------------
 147  Limit
 148    ->  Index Scan using abbrev_abort_uuids__noabort_increasing_idx on abbrev_abort_uuids
 149 (2 rows)
 150
 151 SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5;
 152   id   |          noabort_increasing          |          noabort_decreasing
 153 -------+--------------------------------------+--------------------------------------
 154      1 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
 155  10010 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991
 156      2 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
 157  10011 | 00000001-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990
 158      3 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
 159 (5 rows)
 160
 161 EXPLAIN (COSTS OFF)
 162 SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5;
 163                                        QUERY PLAN
 164 -----------------------------------------------------------------------------------------
 165  Limit
 166    ->  Index Scan using abbrev_abort_uuids__noabort_decreasing_idx on abbrev_abort_uuids
 167 (2 rows)
 168
 169 SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5;
 170   id   |          noabort_increasing          |          noabort_decreasing
 171 -------+--------------------------------------+--------------------------------------
 172  20001 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 173  20010 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 174   9992 | 00009991-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009
 175  20000 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001
 176   9991 | 00009990-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010
 177 (5 rows)
 178
 179 -- index creation using abbreviated keys, hitting abort
 180 CREATE INDEX abbrev_abort_uuids__abort_increasing_idx ON abbrev_abort_uuids (abort_increasing);
 181 CREATE INDEX abbrev_abort_uuids__abort_decreasing_idx ON abbrev_abort_uuids (abort_decreasing);
 182 -- verify
 183 EXPLAIN (COSTS OFF)
 184 SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
 185                                       QUERY PLAN
 186 ---------------------------------------------------------------------------------------
 187  Limit
 188    ->  Index Scan using abbrev_abort_uuids__abort_increasing_idx on abbrev_abort_uuids
 189 (2 rows)
 190
 191 SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
 192   id   |           abort_increasing           |           abort_decreasing
 193 -------+--------------------------------------+--------------------------------------
 194      1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000
 195      2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999
 196      3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
 197  20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
 198      4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997
 199 (5 rows)
 200
 201 EXPLAIN (COSTS OFF)
 202 SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5;
 203                                       QUERY PLAN
 204 ---------------------------------------------------------------------------------------
 205  Limit
 206    ->  Index Scan using abbrev_abort_uuids__abort_decreasing_idx on abbrev_abort_uuids
 207 (2 rows)
 208
 209 SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5;
 210   id   |           abort_increasing           |           abort_decreasing
 211 -------+--------------------------------------+--------------------------------------
 212  20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 213  20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 214  20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001
 215  19999 | 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002
 216  19998 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003
 217 (5 rows)
 218
 219 ----
 220 -- Check CLUSTER uses of tuplesort wrt. abbreviated keys
 221 ----
 222 -- when aborting, increasing order
 223 BEGIN;
 224 SET LOCAL enable_indexscan = false;
 225 CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_increasing_idx;
 226 -- head
 227 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 228 FROM abbrev_abort_uuids
 229 ORDER BY ctid LIMIT 5;
 230   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 231 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 232      1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
 233      2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
 234      3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
 235  20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
 236      4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997
 237 (5 rows)
 238
 239 -- tail
 240 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 241 FROM abbrev_abort_uuids
 242 ORDER BY ctid DESC LIMIT 5;
 243   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 244 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 245      0 |                                      |                                      |                                      |
 246  20002 |                                      |                                      |                                      |
 247  20003 |                                      |                                      |                                      |
 248  20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 249  20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 250 (5 rows)
 251
 252 ROLLBACK;
 253 -- when aborting, decreasing order
 254 BEGIN;
 255 SET LOCAL enable_indexscan = false;
 256 CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_decreasing_idx;
 257 -- head
 258 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 259 FROM abbrev_abort_uuids
 260 ORDER BY ctid LIMIT 5;
 261   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 262 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 263  20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 264  20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 265  20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001
 266  19999 | 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002
 267  20009 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003
 268 (5 rows)
 269
 270 -- tail
 271 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 272 FROM abbrev_abort_uuids
 273 ORDER BY ctid DESC LIMIT 5;
 274   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 275 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 276      0 |                                      |                                      |                                      |
 277  20002 |                                      |                                      |                                      |
 278  20003 |                                      |                                      |                                      |
 279      1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
 280      2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
 281 (5 rows)
 282
 283 ROLLBACK;
 284 -- when not aborting, increasing order
 285 BEGIN;
 286 SET LOCAL enable_indexscan = false;
 287 CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_increasing_idx;
 288 -- head
 289 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 290 FROM abbrev_abort_uuids
 291 ORDER BY ctid LIMIT 5;
 292   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 293 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 294      1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
 295  10010 | 00000000-0000-0000-0000-000000010009 | 00000000-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991
 296      2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
 297  10011 | 00000000-0000-0000-0000-000000010010 | 00000000-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990
 298  20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
 299 (5 rows)
 300
 301 -- tail
 302 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 303 FROM abbrev_abort_uuids
 304 ORDER BY ctid DESC LIMIT 5;
 305   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 306 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 307      0 |                                      |                                      |                                      |
 308  20002 |                                      |                                      |                                      |
 309  20003 |                                      |                                      |                                      |
 310  10009 | 00000000-0000-0000-0000-000000010008 | 00000000-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992
 311  10008 | 00000000-0000-0000-0000-000000010007 | 00000000-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993
 312 (5 rows)
 313
 314 ROLLBACK;
 315 -- when no aborting, decreasing order
 316 BEGIN;
 317 SET LOCAL enable_indexscan = false;
 318 CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_decreasing_idx;
 319 -- head
 320 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 321 FROM abbrev_abort_uuids
 322 ORDER BY ctid LIMIT 5;
 323   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 324 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 325  20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 326  20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
 327   9992 | 00000000-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009
 328  20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001
 329   9991 | 00000000-0000-0000-0000-000000009990 | 00000000-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010
 330 (5 rows)
 331
 332 -- tail
 333 SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
 334 FROM abbrev_abort_uuids
 335 ORDER BY ctid DESC LIMIT 5;
 336   id   |           abort_increasing           |           abort_decreasing           |          noabort_increasing          |          noabort_decreasing
 337 -------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 338      0 |                                      |                                      |                                      |
 339  20003 |                                      |                                      |                                      |
 340  20002 |                                      |                                      |                                      |
 341   9993 | 00000000-0000-0000-0000-000000009992 | 00000000-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008
 342   9994 | 00000000-0000-0000-0000-000000009993 | 00000000-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007
 343 (5 rows)
 344
 345 ROLLBACK;
 346 ----
 347 -- test sorting of large datums VALUES
 348 ----
 349 -- Ensure the order is correct and values look intact
 350 SELECT LEFT(a,10),b FROM
 351     (VALUES(REPEAT('a', 512 * 1024),1),(REPEAT('b', 512 * 1024),2)) v(a,b)
 352 ORDER BY v.a DESC;
 353     left    | b
 354 ------------+---
 355  bbbbbbbbbb | 2
 356  aaaaaaaaaa | 1
 357 (2 rows)
 358
 359 ----
 360 -- test forward and backward scans for in-memory and disk based tuplesort
 361 ----
 362 -- in-memory
 363 BEGIN;
 364 SET LOCAL enable_indexscan = false;
 365 -- unfortunately can't show analyze output confirming sort method,
 366 -- the memory used output wouldn't be stable
 367 EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
 368               QUERY PLAN
 369 --------------------------------------
 370  Sort
 371    Sort Key: noabort_decreasing
 372    ->  Seq Scan on abbrev_abort_uuids
 373 (3 rows)
 374
 375 DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
 376 -- first and second
 377 FETCH NEXT FROM c;
 378           noabort_decreasing
 379 --------------------------------------
 380  00000000-0000-0000-0000-000000000000
 381 (1 row)
 382
 383 FETCH NEXT FROM c;
 384           noabort_decreasing
 385 --------------------------------------
 386  00000000-0000-0000-0000-000000000000
 387 (1 row)
 388
 389 -- scroll beyond beginning
 390 FETCH BACKWARD FROM c;
 391           noabort_decreasing
 392 --------------------------------------
 393  00000000-0000-0000-0000-000000000000
 394 (1 row)
 395
 396 FETCH BACKWARD FROM c;
 397  noabort_decreasing
 398 --------------------
 399 (0 rows)
 400
 401 FETCH BACKWARD FROM c;
 402  noabort_decreasing
 403 --------------------
 404 (0 rows)
 405
 406 FETCH BACKWARD FROM c;
 407  noabort_decreasing
 408 --------------------
 409 (0 rows)
 410
 411 FETCH NEXT FROM c;
 412           noabort_decreasing
 413 --------------------------------------
 414  00000000-0000-0000-0000-000000000000
 415 (1 row)
 416
 417 -- scroll beyond end
 418 FETCH LAST FROM c;
 419  noabort_decreasing
 420 --------------------
 421
 422 (1 row)
 423
 424 FETCH BACKWARD FROM c;
 425  noabort_decreasing
 426 --------------------
 427
 428 (1 row)
 429
 430 FETCH NEXT FROM c;
 431  noabort_decreasing
 432 --------------------
 433
 434 (1 row)
 435
 436 FETCH NEXT FROM c;
 437  noabort_decreasing
 438 --------------------
 439 (0 rows)
 440
 441 FETCH NEXT FROM c;
 442  noabort_decreasing
 443 --------------------
 444 (0 rows)
 445
 446 FETCH BACKWARD FROM c;
 447  noabort_decreasing
 448 --------------------
 449
 450 (1 row)
 451
 452 FETCH NEXT FROM c;
 453  noabort_decreasing
 454 --------------------
 455 (0 rows)
 456
 457 COMMIT;
 458 -- disk based
 459 BEGIN;
 460 SET LOCAL enable_indexscan = false;
 461 SET LOCAL work_mem = '100kB';
 462 -- unfortunately can't show analyze output confirming sort method,
 463 -- the memory used output wouldn't be stable
 464 EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
 465               QUERY PLAN
 466 --------------------------------------
 467  Sort
 468    Sort Key: noabort_decreasing
 469    ->  Seq Scan on abbrev_abort_uuids
 470 (3 rows)
 471
 472 DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
 473 -- first and second
 474 FETCH NEXT FROM c;
 475           noabort_decreasing
 476 --------------------------------------
 477  00000000-0000-0000-0000-000000000000
 478 (1 row)
 479
 480 FETCH NEXT FROM c;
 481           noabort_decreasing
 482 --------------------------------------
 483  00000000-0000-0000-0000-000000000000
 484 (1 row)
 485
 486 -- scroll beyond beginning
 487 FETCH BACKWARD FROM c;
 488           noabort_decreasing
 489 --------------------------------------
 490  00000000-0000-0000-0000-000000000000
 491 (1 row)
 492
 493 FETCH BACKWARD FROM c;
 494  noabort_decreasing
 495 --------------------
 496 (0 rows)
 497
 498 FETCH BACKWARD FROM c;
 499  noabort_decreasing
 500 --------------------
 501 (0 rows)
 502
 503 FETCH BACKWARD FROM c;
 504  noabort_decreasing
 505 --------------------
 506 (0 rows)
 507
 508 FETCH NEXT FROM c;
 509           noabort_decreasing
 510 --------------------------------------
 511  00000000-0000-0000-0000-000000000000
 512 (1 row)
 513
 514 -- scroll beyond end
 515 FETCH LAST FROM c;
 516  noabort_decreasing
 517 --------------------
 518
 519 (1 row)
 520
 521 FETCH BACKWARD FROM c;
 522  noabort_decreasing
 523 --------------------
 524
 525 (1 row)
 526
 527 FETCH NEXT FROM c;
 528  noabort_decreasing
 529 --------------------
 530
 531 (1 row)
 532
 533 FETCH NEXT FROM c;
 534  noabort_decreasing
 535 --------------------
 536 (0 rows)
 537
 538 FETCH NEXT FROM c;
 539  noabort_decreasing
 540 --------------------
 541 (0 rows)
 542
 543 FETCH BACKWARD FROM c;
 544  noabort_decreasing
 545 --------------------
 546
 547 (1 row)
 548
 549 FETCH NEXT FROM c;
 550  noabort_decreasing
 551 --------------------
 552 (0 rows)
 553
 554 COMMIT;
 555 ----
 556 -- test tuplesort using both in-memory and disk sort
 557 ---
 558 -- memory based
 559 SELECT
 560     -- fixed-width by-value datum
 561     (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5],
 562     -- fixed-width by-ref datum
 563     (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5],
 564     -- variable-width datum
 565     (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5],
 566     -- fixed width by-value datum tuplesort
 567     percentile_disc(0.99) WITHIN GROUP (ORDER BY id),
 568     -- ensure state is shared
 569     percentile_disc(0.01) WITHIN GROUP (ORDER BY id),
 570     -- fixed width by-ref datum tuplesort
 571     percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing),
 572     -- variable width by-ref datum tuplesort
 573     percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text),
 574     -- multi-column tuplesort
 575     rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text)
 576 FROM (
 577     SELECT * FROM abbrev_abort_uuids
 578     UNION ALL
 579     SELECT NULL, NULL, NULL, NULL, NULL) s;
 580            array_agg            |                                                                                         array_agg                                                                                          |         array_agg          | percentile_disc | percentile_disc |           percentile_disc            | percentile_disc | rank
 581 --------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------------+-----------------+--------------------------------------+-----------------+------
 582  {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
 583 (1 row)
 584
 585 -- disk based (see also above)
 586 BEGIN;
 587 SET LOCAL work_mem = '100kB';
 588 SELECT
 589     (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5],
 590     (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5],
 591     (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5],
 592     percentile_disc(0.99) WITHIN GROUP (ORDER BY id),
 593     percentile_disc(0.01) WITHIN GROUP (ORDER BY id),
 594     percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing),
 595     percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text),
 596     rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text)
 597 FROM (
 598     SELECT * FROM abbrev_abort_uuids
 599     UNION ALL
 600     SELECT NULL, NULL, NULL, NULL, NULL) s;
 601            array_agg            |                                                                                         array_agg                                                                                          |         array_agg          | percentile_disc | percentile_disc |           percentile_disc            | percentile_disc | rank
 602 --------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------------+-----------------+--------------------------------------+-----------------+------
 603  {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
 604 (1 row)
 605
 606 ROLLBACK;
 607 ----
 608 -- test tuplesort mark/restore
 609 ---
 610 CREATE TEMP TABLE test_mark_restore(col1 int, col2 int, col12 int);
 611 -- need a few duplicates for mark/restore to matter
 612 INSERT INTO test_mark_restore(col1, col2, col12)
 613    SELECT a.i, b.i, a.i * b.i FROM generate_series(1, 500) a(i), generate_series(1, 5) b(i);
 614 BEGIN;
 615 SET LOCAL enable_nestloop = off;
 616 SET LOCAL enable_hashjoin = off;
 617 SET LOCAL enable_material = off;
 618 -- set query into variable once, to avoid repetition of the fairly long query
 619 SELECT $$
 620     SELECT col12, count(distinct a.col1), count(distinct a.col2), count(distinct b.col1), count(distinct b.col2), count(*)
 621     FROM test_mark_restore a
 622         JOIN test_mark_restore b USING(col12)
 623     GROUP BY 1
 624     HAVING count(*) > 1
 625     ORDER BY 2 DESC, 1 DESC, 3 DESC, 4 DESC, 5 DESC, 6 DESC
 626     LIMIT 10
 627 $$ AS qry \gset
 628 -- test mark/restore with in-memory sorts
 629 EXPLAIN (COSTS OFF) :qry;
 630                                                                                  QUERY PLAN
 631 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 632  Limit
 633    ->  Sort
 634          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
 635          ->  GroupAggregate
 636                Group Key: a.col12
 637                Filter: (count(*) > 1)
 638                ->  Incremental Sort
 639                      Sort Key: a.col12 DESC, a.col1
 640                      Presorted Key: a.col12
 641                      ->  Merge Join
 642                            Merge Cond: (a.col12 = b.col12)
 643                            ->  Sort
 644                                  Sort Key: a.col12 DESC
 645                                  ->  Seq Scan on test_mark_restore a
 646                            ->  Sort
 647                                  Sort Key: b.col12 DESC
 648                                  ->  Seq Scan on test_mark_restore b
 649 (17 rows)
 650
 651 :qry;
 652  col12 | count | count | count | count | count
 653 -------+-------+-------+-------+-------+-------
 654    480 |     5 |     5 |     5 |     5 |    25
 655    420 |     5 |     5 |     5 |     5 |    25
 656    360 |     5 |     5 |     5 |     5 |    25
 657    300 |     5 |     5 |     5 |     5 |    25
 658    240 |     5 |     5 |     5 |     5 |    25
 659    180 |     5 |     5 |     5 |     5 |    25
 660    120 |     5 |     5 |     5 |     5 |    25
 661     60 |     5 |     5 |     5 |     5 |    25
 662    960 |     4 |     4 |     4 |     4 |    16
 663    900 |     4 |     4 |     4 |     4 |    16
 664 (10 rows)
 665
 666 -- test mark/restore with on-disk sorts
 667 SET LOCAL work_mem = '100kB';
 668 EXPLAIN (COSTS OFF) :qry;
 669                                                                                  QUERY PLAN
 670 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 671  Limit
 672    ->  Sort
 673          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
 674          ->  GroupAggregate
 675                Group Key: a.col12
 676                Filter: (count(*) > 1)
 677                ->  Incremental Sort
 678                      Sort Key: a.col12 DESC, a.col1
 679                      Presorted Key: a.col12
 680                      ->  Merge Join
 681                            Merge Cond: (a.col12 = b.col12)
 682                            ->  Sort
 683                                  Sort Key: a.col12 DESC
 684                                  ->  Seq Scan on test_mark_restore a
 685                            ->  Sort
 686                                  Sort Key: b.col12 DESC
 687                                  ->  Seq Scan on test_mark_restore b
 688 (17 rows)
 689
 690 :qry;
 691  col12 | count | count | count | count | count
 692 -------+-------+-------+-------+-------+-------
 693    480 |     5 |     5 |     5 |     5 |    25
 694    420 |     5 |     5 |     5 |     5 |    25
 695    360 |     5 |     5 |     5 |     5 |    25
 696    300 |     5 |     5 |     5 |     5 |    25
 697    240 |     5 |     5 |     5 |     5 |    25
 698    180 |     5 |     5 |     5 |     5 |    25
 699    120 |     5 |     5 |     5 |     5 |    25
 700     60 |     5 |     5 |     5 |     5 |    25
 701    960 |     4 |     4 |     4 |     4 |    16
 702    900 |     4 |     4 |     4 |     4 |    16
 703 (10 rows)
 704
 705 COMMIT;