Laurenz Albe: Forcing a join order in PostgreSQL

© Laurenz Albe 2023 Different from many other database systems, PostgreSQL does not support query hints. That makes it difficult to force the hand of the query planner when it comes to a certain join order that you know to be good. This article explains how you can influence execution plans in PostgreSQL. Why no query hints? The PostgreSQL TODO list lists optimizer hints under “Features We Do Not Want” and links to a discussion page that explains why. The discussion page references mailing list discussions that show that this decision has not been unanimous. However, one of the strong points of PostgreSQL is its extensibility. If you really want query hints, you can get them: all you have to do is install the extension pg_hint_plan. This extension offers a comprehensive set of Oracle-style query hints, and it does not require a modified version of the PostgreSQL server. But perhaps you don’t want to install third-party software, or your database is running at a hosting provider and you have no access to the operating system. In that case, read on for alternative ways to force the join order. An example query and its join order We have three tables a, b and c and want to calculate the natural join between them. The optimizer chooses the following plan: EXPLAIN (COSTS OFF) SELECT b.b_id, a.value FROM a JOIN b USING (a_id) JOIN c USING (a_id) WHERE c.c_id < 300; QUERY PLAN ═══════════════════════════════════════════════════════ Hash Join Hash Cond: (c.a_id = a.a_id) -> Seq Scan on c Filter: (c_id < 300) -> Hash -> Nested Loop -> Seq Scan on b -> Memoize Cache Key: b.a_id Cache Mode: logical -> Index Scan using a_pkey on a Index Cond: (a_id = b.a_id) (12 rows) The PostgreSQL optimizer decided to first join b and a, then join the result with c. But we would like to join b and [...]