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 */
 ,CONSTRAINT PK_Element PRIMARY KEY (Element)
 ,CONSTRAINT CK_Element_Is_Uppercase CHECK (Element = UPPER(Element))
)
INSERT INTO Element VALUES ('A'),('B'),('C'),('D')
4 rows affected

WITH RECURSIVE Tuple AS
(
  SELECT
    CAST(Element AS Text) AS Tuple
   ,1 AS TupleLength
  FROM
    Element
  
    UNION ALL
    
  SELECT
    T.Tuple || E.Element
   ,TupleLength + 1
  FROM
    Tuple T
  INNER JOIN
    Element E
      ON E.Element >= RIGHT(T.Tuple,1)
  WHERE
    TupleLength < (SELECT COUNT(*) FROM Element)
)
SELECT
  Tuple
FROM
  Tuple
WHERE TupleLength = 4
ORDER BY
  TupleLength
 ,Tuple


tuple


AAAA

AAAB

AAAC

AAAD

AABB

AABC

AABD

AACC

AACD

AADD

ABBB

ABBC

ABBD

ABCC

ABCD

ABDD

ACCC

ACCD

ACDD

ADDD

BBBB

BBBC

BBBD

BBCC

BBCD

BBDD

BCCC

BCCD

BCDD

BDDD

CCCC

CCCD

CCDD

CDDD

DDDD