clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601446 fiddles created (47986 in the last week).

-- recursive function CREATE OR REPLACE FUNCTION word_permutations(_word text) RETURNS SETOF text LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS $func$ BEGIN IF length(_word) > 1 THEN RETURN QUERY SELECT left(_word, 1) || s || w FROM (VALUES (''), (' ')) sep(s) , word_permutations(right(_word, -1)) w; ELSE RETURN NEXT _word; END IF; END $func$; SELECT word_permutations('ABCD');
word_permutations
ABCD
A BCD
AB CD
A B CD
ABC D
A BC D
AB C D
A B C D
 hidden batch(es)


-- rCTE WITH RECURSIVE val(w) AS (SELECT 'ABCD') -- input , sep(s) AS (VALUES (''), (' ')) , cte AS ( SELECT LEFT(w, 1) AS perm, right(w, -1) AS rest FROM val UNION ALL SELECT perm || s || LEFT(rest, 1), right(rest, -1) FROM cte, sep WHERE rest <> '' ) SELECT perm FROM cte WHERE rest = '';
perm
ABCD
ABC D
AB CD
AB C D
A BCD
A BC D
A B CD
A B C D
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations('ABCDEFGHIJKLMNOP');
QUERY PLAN
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual rows=32768 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.013 ms
Execution Time: 23.584 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) WITH RECURSIVE val(w) AS (SELECT 'ABCDEFGHIJKLMNOP') , sep(s) AS (VALUES (''), (' ')) , cte AS ( SELECT LEFT(w, 1) AS perm, right(w, -1) AS rest FROM val UNION ALL SELECT perm || s || LEFT(rest, 1), right(rest, -1) FROM cte, sep WHERE rest <> '' ) SELECT perm FROM cte WHERE rest = ''
QUERY PLAN
CTE Scan on cte (cost=10.23..14.30 rows=1 width=32) (actual rows=32768 loops=1)
Filter: (rest = ''::text)
Rows Removed by Filter: 32767
CTE cte
-> Recursive Union (cost=0.00..10.23 rows=181 width=64) (actual rows=65535 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=64) (actual rows=1 loops=1)
-> Nested Loop (cost=0.00..0.66 rows=18 width=64) (actual rows=4096 loops=16)
-> WorkTable Scan on cte cte_1 (cost=0.00..0.22 rows=9 width=64) (actual rows=2048 loops=16)
Filter: (rest <> ''::text)
Rows Removed by Filter: 2048
-> Materialize (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=32767)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32) (actual rows=2 loops=1)
Planning Time: 0.125 ms
Execution Time: 55.447 ms
 hidden batch(es)


-- Laurenz' function EXPLAIN (ANALYZE, TIMING OFF) WITH s(s) AS (VALUES ('ABCDEFGHIJKLMNOP')) SELECT substr(s, 1, 1) || string_agg( CASE WHEN i & (2::numeric ^ p)::bigint = 0 THEN '' ELSE ' ' END || substr(s, p + 2, 1), '' ) FROM s CROSS JOIN generate_series(0, (2::numeric ^ (length(s) - 1) - 1)::bigint) AS i CROSS JOIN generate_series(0, length(s) - 2) AS p GROUP BY s, i;
QUERY PLAN
HashAggregate (cost=23347.35..23350.35 rows=200 width=72) (actual rows=32768 loops=1)
Group Key: 'ABCDEFGHIJKLMNOP'::text, i.i
Batches: 33 Memory Usage: 3529kB Disk Usage: 28152kB
-> Nested Loop (cost=0.01..9830.55 rows=491520 width=44) (actual rows=491520 loops=1)
-> Function Scan on generate_series p (cost=0.00..0.15 rows=15 width=4) (actual rows=15 loops=1)
-> Function Scan on generate_series i (cost=0.00..327.68 rows=32768 width=8) (actual rows=32768 loops=15)
Planning Time: 0.273 ms
Execution Time: 1128.110 ms
 hidden batch(es)