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

CREATE TABLE


INSERT INTO Element VALUES ('A'),('B'),('C'),('D')

INSERT 0 4


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)
)
SELECT
  Tuple
FROM
  Tuple
ORDER BY
  TupleLength
 ,Tuple


tuple


A

B

C

D

AB

AC

AD

BC

BD

CD

ABC

ABD

ACD

BCD

ABCD



SELECT 15