Postgres 12

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 things (
  id smallint PRIMARY KEY
, name text -- UNIQUE NOT NULL  -- ?
);

CREATE TABLE things_orders (
  person text NOT NULL
, thing_id smallint NOT NULL  -- REFERENCES things
, qty integer
, CONSTRAINT things_orders_pk PRIMARY KEY (person, thing_id)
);

INSERT INTO things VALUES 
 (1, 'Apple')
,(2, 'Banana')
,(3, 'Cherry')
;

INSERT INTO things_orders VALUES
 ('Adam', 1, 1)
,('Adam', 2, 2)
,('Adam', 3, 3)
,('Ben', 2, 2)
,('Ben', 3, 3)
;
3 rows affected

5 rows affected

WITH RECURSIVE persons AS (
   (
   SELECT person
   FROM   things_orders
   ORDER  BY person
   LIMIT  1
   )
   UNION ALL
   SELECT x.person
   FROM   persons p
   CROSS  JOIN LATERAL (
      SELECT person
      FROM   things_orders
      WHERE  person > p.person
      ORDER  BY person
      LIMIT  1
      ) x
   )
TABLE persons;


person


Adam

Ben


WITH RECURSIVE persons AS (
   (
   SELECT person
   FROM   things_orders
   ORDER  BY person
   LIMIT  1
   )
   UNION ALL
   SELECT x.person
   FROM   persons p
   CROSS  JOIN LATERAL (
      SELECT person
      FROM   things_orders
      WHERE  person > p.person
      ORDER  BY person
      LIMIT  1
      ) x
   )
SELECT p.person, t.thing_id, t.name, tp.qty, COALESCE(tp.qty, 0) As alt_qty
FROM   persons p
CROSS  JOIN (SELECT id AS thing_id, name FROM things) t
LEFT   JOIN things_orders tp USING (thing_id, person);


person
thing_id
name
qty
alt_qty


Adam
1
Apple
1
1

Adam
2
Banana
2
2

Adam
3
Cherry
3
3

Ben
1
Apple
null
0

Ben
2
Banana
2
2

Ben
3
Cherry
3
3

person	thing_id	name	qty	alt_qty
Adam	1	Apple	1	1
Adam	2	Banana	2	2
Adam	3	Cherry	3	3
Ben	1	Apple	null	0
Ben	2	Banana	2	2
Ben	3	Cherry	3	3