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.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit

SELECT 1

create table products (
   product_id   serial primary key
 , product_name varchar unique not null
);

create table orders (
   order_id   serial primary key
 , order_name varchar unique not null
);

create table product_orders (
   product_id  int references products
 , order_id    int references orders
 , primary key (product_id, order_id)
);

-- a product without an order. This actually makes since. 
insert into products(product_name) values ('P1'); 

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 1

select * from products;
select * from orders;
select * from product_orders;

product_id
product_name

1
P1

SELECT 1

order_id
order_name

SELECT 0

product_id
order_id

SELECT 0

create or replace 
procedure gen_product_order(
          myproduct varchar
        , myorder   varchar
        )
  language sql
as $$
    insert into products(product_name) 
         values (myproduct) 
             on conflict (product_name) do nothing; 
  
    insert into orders (order_name)  
         values (myorder)
             on conflict (order_name) do nothing;      
 
    insert into product_orders (product_id, order_id) 
         select product_id, order_id 
           from (select product_id 
                   from products 
                  where product_name = myproduct
                ) prd
              , (select order_id 
                   from orders
                  where order_name = myorder
                ) ord  
          on conflict (product_id, order_id) do nothing; 
$$;

CREATE PROCEDURE

do $$
begin
   -- Old Product, New Orders 
   call gen_product_order('P1','Oa') ; 
   call gen_product_order('P1','Ob') ; 
  
   -- New Product, Old Orders
   call gen_product_order('P2','Oa') ;     
   call gen_product_order('P2','Ob') ;  
  
   -- New Product, New Order 
   call gen_product_order('P3','Oc') ;
   
   -- Duplicate Old Product Old Order DUPLICATE
   call gen_product_order('P3','Oc') ;  -- perhaps from another user or next day  
 
   --Old Product, Old Order
   call gen_product_order('P1','Oc') ; 
 
end; 
$$;

DO

select * from products;
select * from orders;
select * from product_orders;

product_id
product_name

1
P1

4
P2

6
P3

SELECT 3

order_id
order_name

1
Oa

2
Ob

5
Oc

SELECT 3

product_id
order_id

1
1

1
2

4
1

4
2

6
5

1
5

SELECT 6

-- Order and Product
select order_name , product_name
  from product_orders  po 
  join products        p
    on (p.product_id = po.product_id)
  join orders          o
    on (o.order_id = po.order_id) 
 order by order_name , product_name;    

order_name
product_name

Oa
P1

Oa
P2

Ob
P1

Ob
P2

Oc
P1

Oc
P3

SELECT 6