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 owner (
  owner_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, owner    text NOT NULL
);

CREATE TABLE orders (
  order_id   int GENERATED BY DEFAULT AS IDENTITY
, creator_id int
, updated_at timestamptz NOT NULL DEFAULT now()
, CONSTRAINT orders_pkey PRIMARY KEY (order_id) INCLUDE (creator_id, updated_at)
);

CREATE TABLE orders_owner (
  owner_id int REFERENCES owner
, order_id int REFERENCES orders
, CONSTRAINT orders_owner_pkey PRIMARY KEY (owner_id, order_id)  -- columns in this order
);

-- FK can only be added after referenced table exists
ALTER TABLE orders
  ADD CONSTRAINT primary_owner_fk
  FOREIGN KEY (creator_id, order_id) REFERENCES orders_owner;  -- columns in this order  
INSERT INTO owner(owner) VALUES ('owner1'), ('owner2'), ('owner3');
3 rows affected

-- insert orders and primary owner together
WITH ins1 AS (
   INSERT INTO orders_owner(owner_id, order_id) VALUES
     (1, nextval(pg_get_serial_sequence('orders', 'order_id')))
   , (1, nextval(pg_get_serial_sequence('orders', 'order_id')))
   , (2, nextval(pg_get_serial_sequence('orders', 'order_id')))
   RETURNING order_id, owner_id
   )
INSERT INTO orders (order_id, creator_id)
TABLE  ins1;
3 rows affected

-- order 1 gets additional owners
INSERT INTO orders_owner (owner_id, order_id) VALUES
  (2, 1)
, (3, 1);
2 rows affected

TABLE owner;


owner_id
owner


1
owner1

2
owner2

3
owner3


TABLE orders;


order_id
creator_id
updated_at


1
1
2021-06-14 01:24:33.659516+01

2
1
2021-06-14 01:24:33.659516+01

3
2
2021-06-14 01:24:33.659516+01


TABLE orders_owner;


owner_id
order_id


1
1

1
2

2
3

2
1

3
1


-- NOW, your query can just be:
SELECT o.*
FROM   orders_owner oo
JOIN   orders o USING (order_id)
WHERE  oo.owner_id = 2;


order_id
creator_id
updated_at


1
1
2021-06-14 01:24:33.659516+01

3
2
2021-06-14 01:24:33.659516+01

order_id	creator_id	updated_at
1	1	2021-06-14 01:24:33.659516+01
2	1	2021-06-14 01:24:33.659516+01
3	2	2021-06-14 01:24:33.659516+01