2

I defined the join order with Leading(b a) but PostgreSQL, or compatible database like YugabyteDB, still join from a to b:

yugabyte=# explain (costs off, timing off)
          /*+ Leading( b a ) */ 
          select * from table_a a join table_b b using(id);

                    QUERY PLAN
--------------------------------------------------
 Nested Loop
   ->  Seq Scan on table_a a
   ->  Index Scan using table_b_pkey on table_b b
         Index Cond: (id = a.id)
FranckPachot
  • 414
  • 4
  • 10

1 Answers1

3

There are two syntaxes for the Leading hint. One just lists the table in the order they should be considered by the query planner, like Leading ( b a ), leaving the choice of swapping the inner and outer table to the optimizer. The other defines each join pair, with extra parenthesis, as Leading( (b a) ) where left/right item is (outer inner). With a third table it will be either Leading( ( (b a) c ) ) or Leading( ( c (b a) ) )

Generally, fixing an execution plan requires the second syntax, plus a join method hint for each join, and an access method hint for each table.

Examples here: https://dev.to/yugabyte/predictable-plans-with-pghintplan-full-hinting-1do3

FranckPachot
  • 414
  • 4
  • 10