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');


word_permutations


ABCD

A BCD

AB CD

A B CD

ABC D

A BC D

AB C D

A B C D


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


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

Execution Time: 23.939 ms


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

Execution Time: 56.211 ms


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

Execution Time: 1126.010 ms


-- ypercube
EXPLAIN (ANALYZE, TIMING OFF)
WITH s(s) AS (VALUES ('ABCDEFGHIJKLMNOP'))
SELECT 
   words
FROM s
   , generate_series(0, (2::numeric ^ 15 - 1)::bigint) AS i
   , lateral
   ( select 
        replace(replace(
           string_agg(
              substr(s, g+1, 1)||
              substr(i::bit(15)::text, g+1, 1),
              ''
              order by g
              ),
          '0', ''),
          '1', ' ') as words--
     from generate_series(0, 15) as g
   ) as ag ;


QUERY PLAN


Nested Loop  (cost=0.53..18677.76 rows=32768 width=32) (actual rows=32768 loops=1)

  ->  Function Scan on generate_series i  (cost=0.00..327.68 rows=32768 width=8) (actual rows=32768 loops=1)

  ->  Aggregate  (cost=0.52..0.54 rows=1 width=32) (actual rows=1 loops=32768)

        ->  Function Scan on generate_series g  (cost=0.00..0.16 rows=16 width=4) (actual rows=16 loops=32768)

Planning Time: 0.150 ms

Execution Time: 453.432 ms


-- experiment
-- EXPLAIN (ANALYZE, TIMING OFF)
WITH
   val(w) AS (SELECT 'ABCD'::text)      -- input
 , sep(s) AS (VALUES (''::text), (' '))
 select 
     v4.w as words
 from 
   ( select substr(val.w, 1, 1) as w from val) as v1,
   lateral
   ( select v1.w || s || substr(val.w, 2, 1) as w
     from sep, val
   ) as v2,
   lateral
   ( select v2.w || s || substr(val.w, 3, 1) as w
     from sep, val
   ) as v3,
  lateral
   ( select v3.w || s || substr(val.w, 4, 1) as w
     from sep, val
   ) as v4


words


ABCD

A BCD

AB CD

A B CD

ABC D

A BC D

AB C D

A B C D


-- experiment
-- EXPLAIN (ANALYZE, TIMING OFF)
WITH
   val(w) AS (SELECT 'ABCD'::text)      -- input
 , sep(s) AS (VALUES (''::text), (' '))
 select 
     v4.w as words
 from 
   ( select substr(val.w, 1, 1) as w from val) as v1,
   lateral
   ( select v1.w || s || substr(val.w, 2, 1) as w
     from sep, val
   ) as v2,
   lateral
   ( select v2.w || s || substr(val.w, 3, 1) as w
     from sep, val
   ) as v3,
  lateral
   ( select v3.w || s || substr(val.w, 4, 1) as w
     from sep, val
   ) as v4


words


ABCD

A BCD

AB CD

A B CD

ABC D

A BC D

AB C D

A B C D


-- experiment 2
-- EXPLAIN (ANALYZE, TIMING OFF)
WITH
   val(w) AS (SELECT 'ABCD')      -- input
 , sep(s) AS (VALUES (''), (' '))
 select 
        substr(w, 1, 1) || sep1.s
     || substr(w, 2, 1) || sep2.s
     || substr(w, 3, 1) || sep3.s
     || substr(w, 4, 1)
     as words
 from
   val, 
   sep as sep1,
   sep as sep2,
   sep as sep3 ;


words


ABCD

AB CD

A BCD

A B CD

ABC D

AB C D

A BC D

A B C D


-- experiment 2
EXPLAIN (ANALYZE, TIMING OFF)
WITH
   val(w) AS (SELECT 'ABCDEFGHIJKLMNOP')      -- input
 , sep(s) AS (VALUES (''), (' '))
 select 
        substr(w, 1, 1) || sep1.s
     || substr(w, 2, 1) || sep2.s
     || substr(w, 3, 1) || sep3.s
     || substr(w, 4, 1) || sep4.s
     || substr(w, 5, 1) || sep5.s
     || substr(w, 6, 1) || sep6.s
     || substr(w, 7, 1) || sep7.s
     || substr(w, 8, 1) || sep8.s
     || substr(w, 9, 1) || sep9.s
     || substr(w, 10, 1) || sep10.s
     || substr(w, 11, 1) || sep11.s
     || substr(w, 12, 1) || sep12.s
     || substr(w, 13, 1) || sep13.s
     || substr(w, 14, 1) || sep14.s
     || substr(w, 15, 1) || sep15.s
     || substr(w, 16, 1)
     as words
 from
   val as v, sep as sep1,
   sep as sep2,
   sep as sep3,
   sep as sep4,
   sep as sep5,
   sep as sep6,
   sep as sep7,
   sep as sep8,
   sep as sep9,
   sep as sep10,
   sep as sep11,
   sep as sep12,
   sep as sep13,
   sep as sep14,
   sep as sep15
   ;


QUERY PLAN


Nested Loop  (cost=0.03..3564.08 rows=32768 width=32) (actual rows=32768 loops=1)

  CTE sep

    ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32) (actual rows=2 loops=1)

  ->  Nested Loop  (cost=0.00..451.07 rows=16384 width=448) (actual rows=16384 loops=1)

        ->  CTE Scan on sep sep2  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

        ->  Materialize  (cost=0.00..266.71 rows=8192 width=416) (actual rows=8192 loops=2)

              ->  Nested Loop  (cost=0.00..225.75 rows=8192 width=416) (actual rows=8192 loops=1)

                    ->  CTE Scan on sep sep8  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                    ->  Materialize  (cost=0.00..133.55 rows=4096 width=384) (actual rows=4096 loops=2)

                          ->  Nested Loop  (cost=0.00..113.07 rows=4096 width=384) (actual rows=4096 loops=1)

                                ->  CTE Scan on sep sep12  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                ->  Materialize  (cost=0.00..66.95 rows=2048 width=352) (actual rows=2048 loops=2)

                                      ->  Nested Loop  (cost=0.00..56.71 rows=2048 width=352) (actual rows=2048 loops=1)

                                            ->  CTE Scan on sep sep6  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                            ->  Materialize  (cost=0.00..33.63 rows=1024 width=320) (actual rows=1024 loops=2)

                                                  ->  Nested Loop  (cost=0.00..28.51 rows=1024 width=320) (actual rows=1024 loops=1)

                                                        ->  CTE Scan on sep sep1  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                        ->  Materialize  (cost=0.00..16.95 rows=512 width=288) (actual rows=512 loops=2)

                                                              ->  Nested Loop  (cost=0.00..14.39 rows=512 width=288) (actual rows=512 loops=1)

                                                                    ->  CTE Scan on sep sep9  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                    ->  Materialize  (cost=0.00..8.59 rows=256 width=256) (actual rows=256 loops=2)

                                                                          ->  Nested Loop  (cost=0.00..7.31 rows=256 width=256) (actual rows=256 loops=1)

                                                                                ->  CTE Scan on sep sep10  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                                ->  Materialize  (cost=0.00..4.39 rows=128 width=224) (actual rows=128 loops=2)

                                                                                      ->  Nested Loop  (cost=0.00..3.75 rows=128 width=224) (actual rows=128 loops=1)

                                                                                            ->  CTE Scan on sep sep15  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                                            ->  Materialize  (cost=0.00..2.27 rows=64 width=192) (actual rows=64 loops=2)

                                                                                                  ->  Nested Loop  (cost=0.00..1.95 rows=64 width=192) (actual rows=64 loops=1)

                                                                                                        ->  CTE Scan on sep sep4  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                                                        ->  Materialize  (cost=0.00..1.19 rows=32 width=160) (actual rows=32 loops=2)

                                                                                                              ->  Nested Loop  (cost=0.00..1.03 rows=32 width=160) (actual rows=32 loops=1)

                                                                                                                    ->  CTE Scan on sep sep5  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                                                                    ->  Materialize  (cost=0.00..0.63 rows=16 width=128) (actual rows=16 loops=2)

                                                                                                                          ->  Nested Loop  (cost=0.00..0.55 rows=16 width=128) (actual rows=16 loops=1)

                                                                                                                                ->  CTE Scan on sep sep7  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                                                                                ->  Materialize  (cost=0.00..0.33 rows=8 width=96) (actual rows=8 loops=2)

                                                                                                                                      ->  Nested Loop  (cost=0.00..0.29 rows=8 width=96) (actual rows=8 loops=1)

                                                                                                                                            ->  CTE Scan on sep sep11  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                                                                                            ->  Materialize  (cost=0.00..0.16 rows=4 width=64) (actual rows=4 loops=2)

                                                                                                                                                  ->  Nested Loop  (cost=0.00..0.14 rows=4 width=64) (actual rows=4 loops=1)

                                                                                                                                                        ->  CTE Scan on sep sep14  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=1)

                                                                                                                                                        ->  CTE Scan on sep sep3  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=2)

  ->  CTE Scan on sep sep13  (cost=0.00..0.04 rows=2 width=32) (actual rows=2 loops=16384)

Planning Time: 9.936 ms

Execution Time: 56.137 ms


-- experiment 3
-- variation on Erwin's recursive function
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
           , 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


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

Execution Time: 9.486 ms