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. 3601466 fiddles created (47977 in the last week).

-- erwin org. recursive func 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)


-- ypercube's recursive func CREATE OR REPLACE FUNCTION word_permutations_yper(_word text) RETURNS SETOF text LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS $func$ declare world_length int := length(_word); BEGIN IF world_length > 1 THEN RETURN QUERY SELECT wl || s || wr FROM (VALUES (''), (' ')) sep(s) , word_permutations(left(_word, world_length/2)) wl -- call to erwin func , word_permutations(right(_word, -(world_length/2))) wr; ELSE RETURN NEXT _word; END IF; END $func$; SELECT word_permutations_yper('ABCD');
word_permutations_yper
ABCD
AB CD
A BCD
A B CD
ABC D
AB C D
A BC D
A B C D
 hidden batch(es)


-- optimized CREATE OR REPLACE FUNCTION word_permutations2(_word text) RETURNS SETOF text LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS $func$ DECLARE world_len int := length(_word); BEGIN CASE world_len WHEN 2 THEN RETURN NEXT _word; RETURN NEXT OVERLAY(_word PLACING ' ' FROM 2 FOR 0); WHEN 1, 0 THEN -- corner cases RETURN NEXT _word; ELSE RETURN QUERY SELECT wl || s || wr FROM (VALUES (''), (' ')) sep(s) , word_permutations2(left(_word, world_len/2)) wl , word_permutations2(right(_word, -(world_len/2))) wr; END CASE; END $func$; SELECT word_permutations2('ABCD');
word_permutations2
ABCD
AB CD
A BCD
A B CD
ABC D
AB C D
A BC D
A B C D
 hidden batch(es)


-- With 16 char (local optimum) 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.012 ms
Execution Time: 27.925 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations_yper('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.025 ms
Execution Time: 10.949 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations2('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.019 ms
Execution Time: 11.564 ms
 hidden batch(es)


-- With 17 char EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations('ABCDEFGHIJKLMNOPQ');
QUERY PLAN
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual rows=65536 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.019 ms
Execution Time: 60.356 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations_yper('ABCDEFGHIJKLMNOPQ');
QUERY PLAN
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual rows=65536 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.028 ms
Execution Time: 28.819 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations2('ABCDEFGHIJKLMNOPQ');
QUERY PLAN
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual rows=65536 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.027 ms
Execution Time: 27.761 ms
 hidden batch(es)


-- With 21 char EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations('ABCDEFGHIJKLMNOPQRSTU');
QUERY PLAN
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual rows=1048576 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.036 ms
Execution Time: 1014.899 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations_yper('ABCDEFGHIJKLMNOPQRSTU');
QUERY PLAN
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual rows=1048576 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.029 ms
Execution Time: 420.173 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT word_permutations2('ABCDEFGHIJKLMNOPQRSTU');
QUERY PLAN
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual rows=1048576 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
Planning Time: 0.028 ms
Execution Time: 407.697 ms
 hidden batch(es)