Postgres 13

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

CREATE TABLE Element
(
  Element  CHAR(1)  NOT NULL COLLATE "en_US" /* Chosen since a < A < B < C.  Case support varies by Postgres version, check constraint handles this */
  , element_id serial
 -- ,CONSTRAINT PK_Element PRIMARY KEY (Element)
 ,CONSTRAINT CK_Element_Is_Uppercase CHECK (Element = UPPER(Element))
)
INSERT INTO Element VALUES ('A'),('B'),('C'),('C')
4 rows affected

WITH RECURSIVE Tuple AS
(
  SELECT
    CAST(Element AS Text) AS Tuple
   ,1 AS TupleLength
   ,element_id
  FROM
    Element
  
    UNION ALL
    
  SELECT
    T.Tuple || E.Element
   ,TupleLength + 1
   ,E.element_id
  FROM
    Tuple T
  INNER JOIN
    Element E
      ON E.Element > RIGHT(T.Tuple,1)
      OR E.Element = RIGHT(T.Tuple,1)
         AND E.element_id > t.element_id
)
SELECT
  Tuple, TupleLength
FROM
  Tuple
ORDER BY
  TupleLength
 ,Tuple


tuple
tuplelength


A
1

B
1

C
1

C
1

AB
2

AC
2

AC
2

BC
2

BC
2

CC
2

ABC
3

ABC
3

ACC
3

BCC
3

ABCC
4