Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- func to create random strings
CREATE OR REPLACE FUNCTION f_random_string(int)
  RETURNS text AS
$func$
SELECT array_to_string(ARRAY(
   SELECT substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', (ceil(random()*62))::int, 1)
   FROM generate_series(1, $1)
   ), '')
$func$  LANGUAGE sql VOLATILE;

-- test tbl with 100K rows
CREATE TABLE tbl(str text);
INSERT INTO tbl
SELECT f_random_string(15)
FROM   generate_series(1, 100000) g;
100000 rows affected

VACUUM ANALYZE tbl;
-- 1: your test function 1 (inefficient)
CREATE OR REPLACE FUNCTION sort1(text)  RETURNS text AS
$func$  -- your test function 1 (very inefficient)
SELECT string_agg(c, '')
FROM  (SELECT unnest(regexp_split_to_array($1, '')) AS c ORDER  BY c) t;
$func$ LANGUAGE sql IMMUTABLE;

-- 2: your test function 2 ( inefficient)
CREATE OR REPLACE FUNCTION sort2(text)  RETURNS text AS
$func$
WITH t(s) AS (VALUES ($1))
SELECT string_agg(substr(t.s, g.g, 1), '' ORDER BY substr(t.s, g.g, 1))
FROM   t
CROSS  JOIN LATERAL generate_series(1, length(t.s)) g;
$func$  LANGUAGE sql IMMUTABLE;

-- 3: remove pointless CTE from sort2
CREATE OR REPLACE FUNCTION sort3(text)  RETURNS text AS
$func$
SELECT string_agg(substr($1, g, 1), '' ORDER BY substr($1, g, 1))
FROM   generate_series(1, length($1)) g;
$func$  LANGUAGE sql IMMUTABLE;

-- 4: use unnest instead of calling substr N times
CREATE OR REPLACE FUNCTION sort4(text)  RETURNS text AS
$func$
SELECT string_agg(c, '' ORDER BY c)
FROM   unnest(string_to_array($1, NULL)) c
$func$  LANGUAGE sql IMMUTABLE;

-- 5: ORDER BY in subquery
CREATE OR REPLACE FUNCTION sort5(text)  RETURNS text AS
$func$
SELECT string_agg(c, '')
FROM  (
   SELECT c
   FROM   unnest(string_to_array($1, NULL)) c
   ORDER  BY c
   ) sub
$func$  LANGUAGE sql IMMUTABLE;

-- 6: SRF in SELECT list
CREATE OR REPLACE FUNCTION sort6(text)  RETURNS text AS
$func$
SELECT string_agg(c, '')
FROM  (SELECT unnest(string_to_array($1, NULL)) c ORDER BY 1) sub
$func$  LANGUAGE sql IMMUTABLE;

-- 7: ARRAY constructor instead of aggregate func
CREATE OR REPLACE FUNCTION sort7(text)  RETURNS text AS
$func$
SELECT array_to_string(ARRAY(SELECT unnest(string_to_array($1, NULL)) c ORDER BY c), '')
$func$  LANGUAGE sql IMMUTABLE;

-- 8: The same with COLLATE "C"
CREATE OR REPLACE FUNCTION sort8(text)  RETURNS text AS
$func$
SELECT array_to_string(ARRAY(SELECT unnest(string_to_array($1 COLLATE "C", NULL)) c ORDER BY c), '')
$func$  LANGUAGE sql IMMUTABLE;
SELECT str, sort1(str), sort2(str), sort3(str), sort4(str), sort5(str), sort6(str), sort7(str), sort8(str) FROM tbl LIMIT 1;  -- result sample 


str
sort1
sort2
sort3
sort4
sort5
sort6
sort7
sort8


0UasyV8TC2WANnI
028aACInNsTUVWy
028aACInNsTUVWy
028aACInNsTUVWy
028aACInNsTUVWy
028aACInNsTUVWy
028aACInNsTUVWy
028aACInNsTUVWy
028ACINTUVWansy


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort1(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.046 ms

Execution time: 2790.733 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort2(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.096 ms

Execution time: 2706.412 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort3(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.075 ms

Execution time: 2355.536 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort4(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.097 ms

Execution time: 2224.578 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort5(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.074 ms

Execution time: 2079.255 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort6(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.072 ms

Execution time: 2019.719 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort7(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.078 ms

Execution time: 1985.665 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT sort8(str) FROM tbl;


QUERY PLAN


Seq Scan on tbl  (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1)

Planning time: 0.071 ms

Execution time: 1682.665 ms