Query Optimization & EXPLAIN

One-to-one is a unique foreign key, one-to-many and many-to-one are the two views of a foreign key on the child, and many-to-many needs a junction table with two foreign keys.

Read the plan first, fix the dominant cost with the least invasive change (index, rewrite, join, sargability), escalate to partitioning, caching, schema, or hardware only if needed, and re-check the plan every time.

Measure first with EXPLAIN ANALYZE, fix the cheapest proven cause, and re-measure after every change.

EXPLAIN shows the plan the optimizer chose with estimates; EXPLAIN ANALYZE runs it and shows actuals: and the first thing you hunt for is a sequential scan on a big table.

Match the plan against four signatures: unexpected Seq Scan, Nested Loop on a huge outer, costly Sort, and estimate-versus-actual gap: each with a specific fix.