

\timing
-- this function produces the const string (length multiple of 4).
DROP FUNCTION IF EXISTS string_4k();
CREATE FUNCTION string_4k( ) RETURNS text
AS $$

DECLARE
text_4k  text;
i int;
BEGIN
  select CONCAT(text_4k, 'junk_123') into text_4k;
FOR i IN 1..6 LOOP
  select CONCAT(text_4k, text_4k) into text_4k; 
END LOOP;
    RETURN text_4k;
END
$$ LANGUAGE plpgsql;

DROP function IF EXISTS test_orderby_asc();
DROP function IF EXISTS test_orderby_desc();

-- sort in ascending order.
CREATE FUNCTION test_orderby_asc( ) RETURNS int
AS $$
#print_strict_params on
DECLARE
gs int;
jk text;
BEGIN
SELECT string_4k, generate_series INTO  jk, gs
        FROM so order by string_4k, generate_series;

    RETURN gs;
END
$$ LANGUAGE plpgsql;


-- sort in descending order.
CREATE FUNCTION test_orderby_desc( ) RETURNS int
AS $$
#print_strict_params on
DECLARE
gs int;
jk text;
BEGIN
SELECT string_4k, generate_series INTO jk,gs
        FROM so order by string_4k, generate_series desc;

    RETURN gs;
END
$$ LANGUAGE plpgsql;

select char_length(string_4k());


-- To create table with presorted data, change the parameter of generate_series for more/less rows.
create table so as select string_4k(), * FROM generate_series(1, 100000*2*2*2*2*2);
\dt+
select test_orderby_asc( ) ;
select  test_orderby_desc( ) ;
explain select string_4k,generate_series from so;
\timing



--- To create table with randomly distributed sort keys.
create table so as select string_4k(), (random()*100000*2*2*2*2*2)::int as generate_series FROM generate_series(1, 100000*2*2*2*2*2);
\dt+
select test_orderby_asc( ) ;
select  test_orderby_desc( ) ;
explain select string_4k,generate_series from so;
\timing
