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

select version();


version


PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE meal
(
  mname TEXT NOT NULL
);
CREATE TABLE drink
(
  dname TEXT NOT NULL
)
INSERT INTO meal (mname) VALUES ('Omlette'), ('Fried Egg'), ('Sausage');
3 rows affected

INSERT INTO drink (dname) VALUES ('Coffee'), ('Tea'), ('Orange Juice');
3 rows affected

SELECT * FROM meal, drink;


mname
dname


Omlette
Coffee

Omlette
Tea

Omlette
Orange Juice

Fried Egg
Coffee

Fried Egg
Tea

Fried Egg
Orange Juice

Sausage
Coffee

Sausage
Tea

Sausage
Orange Juice


SELECT *
FROM meal
CROSS JOIN drink;


mname
dname


Omlette
Coffee

Omlette
Tea

Omlette
Orange Juice

Fried Egg
Coffee

Fried Egg
Tea

Fried Egg
Orange Juice

Sausage
Coffee

Sausage
Tea

Sausage
Orange Juice


SELECT m.mname, d.dname 
FROM meal m
CROSS JOIN drink d;


mname
dname


Omlette
Coffee

Omlette
Tea

Omlette
Orange Juice

Fried Egg
Coffee

Fried Egg
Tea

Fried Egg
Orange Juice

Sausage
Coffee

Sausage
Tea

Sausage
Orange Juice


SELECT m.mname, d.dname 
FROM meal m
JOIN drink d ON TRUE;  -- or 1 = 1;


mname
dname


Omlette
Coffee

Omlette
Tea

Omlette
Orange Juice

Fried Egg
Coffee

Fried Egg
Tea

Fried Egg
Orange Juice

Sausage
Coffee

Sausage
Tea

Sausage
Orange Juice

mname	dname
Omlette	Coffee
Omlette	Tea
Omlette	Orange Juice
Fried Egg	Coffee
Fried Egg	Tea
Fried Egg	Orange Juice
Sausage	Coffee
Sausage	Tea
Sausage	Orange Juice