I have a query on 4 tables with 4 MergeJoin hints. Why are they not used

Question

[question from a YugabyteDB database user]

Need help with query hinting - what I’m doing wrong? For this query I’ve got following execution plan:

SET pg_hint_plan.enable_hint=ON;
/** Set(work_mem "4000MB") 
MergeJoin(self_0 self_1) 
MergeJoin(self_0 self_2) 
MergeJoin(self_0 self_3)   
MergeJoin(self_0 self_4) **/ 
EXPLAIN
    SELECT count(*)
    FROM self_0
             inner join self_1 on self_0.k = self_1.k
             inner join self_2 on self_0.k = self_2.k
             inner join self_3 on self_0.k = self_3.k
             inner join self_4 on self_0.k = self_4.k;

SET
QUERY PLAN
Aggregate  (cost=558.04..558.05 rows=1 width=8)
  ->  Nested Loop  (cost=0.00..555.54 rows=1000 width=0)
        ->  Nested Loop  (cost=0.00..441.66 rows=1000 width=128)
              ->  Nested Loop  (cost=0.00..327.77 rows=1000 width=96)
                    ->  Nested Loop  (cost=0.00..213.89 rows=1000 width=64)
                          ->  Seq Scan on self_0  (cost=0.00..100.00 rows=1000 width=32)
                          ->  Index Scan using self_1_pkey on self_1  (cost=0.00..0.11 rows=1 width=32)
                                Index Cond: (k = self_0.k)
                    ->  Index Scan using self_2_pkey on self_2  (cost=0.00..0.11 rows=1 width=32)
                          Index Cond: (k = self_0.k)
              ->  Index Scan using self_3_pkey on self_3  (cost=0.00..0.11 rows=1 width=32)
                    Index Cond: (k = self_0.k)
        ->  Index Scan using self_4_pkey on self_4  (cost=0.00..0.11 rows=1 width=32)
              Index Cond: (k = self_0.k)
(14 rows)

Answer 1

What your four MergeJoin hints are saying is that if the query planner decides to start with joining self_0 with one of the four other tables, then use a Merge Join. But then joining to others is up to its decision. Also, if it decides to start joining, say, self_1 to self_2 first, none of your hints will not be used.

If you want to fix an execution plan, you will need more hints. See: https://dev.to/yugabyte/predictable-plans-with-pghintplan-full-hinting-1do3. The join hints must mention all tables joined up to the one it is hinting. With two aliases only it will apply when those two are the first join.