0

I'm having a problem with writing a Postgres query that really appreciates anyone's help. I have 3 tables related to each other as below:

Order:

  • id (pk)
  • updated_at (datetime)
  • owner (foreign key, to OrderOwner)

OrderOwner:

  • id (pk)
  • name

SharedOrder:

  • id (pk)
  • shared_to_owner (foreign key, to OrderOwner)
  • order_id (foreign key, to Order)

Indexes:

  • [Order] (owner)
  • [SharedOrder] (order_id, shared_to_owner)

Now, I need to write a query that fetches Orders that belong to an owner OR are shared with the owner. My query is as below:

SELECT id, owner, updated_at FROM Order 
WHERE
    (owner = [owner_id]) OR 
    (id in (SELECT order_id from SharedOrder where shared_to_owner = [owner_id]))

The query runs really fast (<200ms) for cases where the selected owners have a large number of orders created by them (500k+) and a small number of orders shared with them (~80k).

The weird case happens when I try to run this query for owners that only have 1 order created by them and around 200k+ orders shared with them. It takes more than 600s and is not able to complete. I'm quite new to database optimization and still not able to understand the big difference. How to explain and improve this?

2
  • 2
    Please consider reading this advice. Also tag your question with the exact Postgres version you're using.
    – mustaccio
    Jun 13 at 15:42
  • Most importantly, actual CREATE TABLE scripts showing data types and constraints would be much more instrumental than the vague free-hand description. Order is a reserved word and illegal as table name. It's really "Order", I assume? Jun 13 at 21:57
2

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:

Not the answer you're looking for? Browse other questions tagged or ask your own question.