end;

$$;

-- A single large group tested around each mode transition point.

insert into t(a, b) select 1, i from generate_series(1, 100) n(i);

insert into t(a, b) select i/100 + 1, i + 1 from generate_series(0, 999) n(i);

analyze t;

explain (costs off) select * from (select * from t order by a) s order by a, b limit 31;

QUERY PLAN

---------------------------------

delete from t;

-- An initial large group followed by a small group.

insert into t(a, b) select (case when i < 50 then 1 else 2 end), i from generate_series(1, 100) n(i);

insert into t(a, b) select i/50 + 1, i + 1 from generate_series(0, 999) n(i);

analyze t;

explain (costs off) select * from (select * from t order by a) s order by a, b limit 55;

QUERY PLAN

---------------------------------

1 | 47

1 | 48

1 | 49

2 | 50

1 | 50

2 | 51

2 | 52

2 | 53

Sort Key: t.a, t.b

Presorted Key: t.a

Full-sort Groups: 2 Sort Methods: top-N heapsort, quicksort Memory: avg=NNkB peak=NNkB

-> Sort (actual rows=100 loops=1)

-> Sort (actual rows=101 loops=1)

Sort Key: t.a

Sort Method: quicksort Memory: NNkB

-> Seq Scan on t (actual rows=100 loops=1)

-> Seq Scan on t (actual rows=1000 loops=1)

(9 rows)

select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from (select * from t order by a) s order by a, b limit 55'));

delete from t;

-- An initial small group followed by a large group.

insert into t(a, b) select (case when i < 5 then i else 9 end), i from generate_series(1, 100) n(i);

insert into t(a, b) select (case when i < 5 then i else 9 end), i from generate_series(1, 1000) n(i);

analyze t;

explain (costs off) select * from (select * from t order by a) s order by a, b limit 70;

QUERY PLAN

---------------------------------

rollback;

-- Test EXPLAIN ANALYZE with both fullsort and presorted groups.

select explain_analyze_without_memory('select * from (select * from t order by a) s order by a, b limit 70');

explain_analyze_without_memory

-----------------------------------------------------------------------------------------------------------------------------------------------------

explain_analyze_without_memory

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Limit (actual rows=70 loops=1)

-> Incremental Sort (actual rows=70 loops=1)

Sort Key: t.a, t.b

Presorted Key: t.a

Full-sort Groups: 1 Sort Method: quicksort Memory: avg=NNkB peak=NNkB Presorted Groups: 5 SortMethod: quicksort Memory: avg=NNkB peak=NNkB

-> Sort (actual rows=100 loops=1)

Full-sort Groups: 1 Sort Method: quicksort Memory: avg=NNkB peak=NNkB Presorted Groups: 5 SortMethods: top-N heapsort, quicksort Memory: avg=NNkB peak=NNkB

-> Sort (actual rows=1000 loops=1)

Sort Key: t.a

Sort Method: quicksort Memory: NNkB

-> Seq Scan on t (actual rows=100 loops=1)

-> Seq Scan on t (actual rows=1000 loops=1)

(9 rows)

select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from (select * from t order by a) s order by a, b limit 70'));

"Presorted Groups": { +

"Group Count": 5, +

"Sort Methods Used": [ +

"top-N heapsort", +

"quicksort" +

], +

"Sort Space Memory": { +

delete from t;

-- Small groups of 10 tuples each tested around each mode transition point.

insert into t(a, b) select i / 10, i from generate_series(1, 70) n(i);

insert into t(a, b) select i / 10, i from generate_series(1, 1000) n(i);

analyze t;

explain (costs off) select * from (select * from t order by a) s order by a, b limit 31;

QUERY PLAN

---------------------------------

delete from t;

-- Small groups of only 1 tuple each tested around each mode transition point.

insert into t(a, b) select i, i from generate_series(1, 70) n(i);

insert into t(a, b) select i, i from generate_series(1, 1000) n(i);

analyze t;

explain (costs off) select * from (select * from t order by a) s order by a, b limit 31;

QUERY PLAN

---------------------------------

$$;

insert into t(a, b)select1, ifrom generate_series(1,100) n(i);

insert into t(a, b)select i/100+1, i+1from generate_series(0,999) n(i);

analyze t;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit31;

select*from (select*from torder by a) sorder by a, blimit31;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit32;

deletefrom t;

insert into t(a, b)select (case when i<50 then1 else2 end), ifrom generate_series(1,100) n(i);

insert into t(a, b)select i/50+1, i+1from generate_series(0,999) n(i);

analyze t;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit55;

select*from (select*from torder by a) sorder by a, blimit55;

deletefrom t;

insert into t(a, b)select (case when i<5 then i else9 end), ifrom generate_series(1,100) n(i);

insert into t(a, b)select (case when i<5 then i else9 end), ifrom generate_series(1,1000) n(i);

analyze t;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit70;

select*from (select*from torder by a) sorder by a, blimit70;

deletefrom t;

insert into t(a, b)select i/10, ifrom generate_series(1,70) n(i);

insert into t(a, b)select i/10, ifrom generate_series(1,1000) n(i);

analyze t;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit31;

select*from (select*from torder by a) sorder by a, blimit31;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit32;

deletefrom t;

insert into t(a, b)select i, ifrom generate_series(1,70) n(i);

insert into t(a, b)select i, ifrom generate_series(1,1000) n(i);

analyze t;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit31;

select*from (select*from torder by a) sorder by a, blimit31;

explain (costs off)select*from (select*from torder by a) sorder by a, blimit32;