Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- 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');

CREATE FUNCTION

word_permutations

ABCD

A BCD

AB CD

A B CD

ABC D

A BC D

AB C D

A B C D

SELECT 8

-- 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

SELECT 8

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: 192.563 ms

EXPLAIN

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.150 ms

Execution Time: 409.403 ms

EXPLAIN

-- 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.349 ms

Execution Time: 9976.111 ms

EXPLAIN