Index backwards
First, and most prominently, the order of columns in your multicolumn index is backwards:
[SharedOrder] (order_id, shared_to_owner)
You need an index on "SharedOrder" (shared_to_owner, order_id) for this query. See:
You may or may not need the one you have now (additionally) for other queries. (That's the common case!) From what I see here, (shared_to_owner, order_id) should be UNIQUE NOT NULL and probably the PRIMARY KEY to begin with. Like:
CREATE TABLE shared_order (
order_owner_id int REFERENCES order_owner
, order_id int REFERENCES orders -- plural to make it legal identifier
, CONSTRAINT shared_order_pkey PRIMARY KEY (order_owner_id, order_id) -- columns in this order
);
Also suggesting legal, lower-case, unquoted identifiers instead of the CaMeL-case confusion. See:
While being at it, assuming owner, id, updated_at are types int, int, timestamptz, a "covering" index on "Order" might help some more:
CREATE INDEX ON orders (owner, id, updated_at);
Or
CREATE INDEX ON orders (owner) INCLUDE (id, updated_at);
Possibly replacing the index you have now (depends on the complete situation):
[Order] (owner)
See:
"Ugly OR"
That's an ugly ORif I have ever seen one. See:
Since your SELECT list contains the PK and there is no join in the FROM list, and assuming (shared_to_owner, order_id) is, in fact, UNIQUE, this rewrite with UNION is 100 % equivalent, and typically resolves to more optimal query plans:
SELECT id, owner, updated_at
FROM "Order" -- double-quoted illegal name
WHERE owner = [owner_id]
UNION
SELECT o.id, o.owner, o.updated_at
FROM "SharedOrder" so
JOIN "Order" o ON o.id = so.order_id
WHERE so.shared_to_owner = [owner_id];
It should certainly avoid the corner case performance regression you reported.
Using your original identifiers for the purpose of this demo. Wouldn't do that in real life.
Alternative relational design
You could get rid of the ugly OR in the query completely with a different relational design. Put all owners into the many-to-many table, including the creator. To still force one valid creator, add a FK constraint. Assuming the current version Postgres 13.
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 -- NOT NULL ?
, 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
db<>fiddle here
Then your query can just be (and you'll never get a bad query plan):
SELECT o.*
FROM orders_owner oo
JOIN orders o USING (order_id)
WHERE oo.owner_id = 2;
I allow orders.creator_id to be NULL. This way you can insert data in separate steps if you want. You can even make it NOT NULL to force a creator always, then you have to use CTEs or deferrable FK constraints to insert new data as demonstrated in the fiddle and related answers below.
Adding columns to orders_pkey with the INCLUDE clause is optional. The intention is to allow index-only scans. Decision depends on the complete situation.
Related answers with discussion and more explanation:
