Incorrect join order when using join method hints

[simplerick]

Hi, I am using 1.3.7 version downloaded from releases for PostgreSQL 13.1. I also set the parameters join_collapse_limit=25; from_collapse_limit=25; geqo_threshold = 25; work_mem = "2000MB";

When I run explain of the following query:

/*+
Leading((rt (it ((n (chn (mc (mi (t (ci an)))))) cn))))
HashJoin(ci an)
NestLoop(t ci an)
NestLoop(mi t ci an)
NestLoop(ci an mi t mc)
NestLoop(ci chn an mi t mc)
NestLoop(ci chn n mi t an mc)
NestLoop(ci chn cn n mi t an mc)
NestLoop(mi an it ci chn n cn t mc)
NestLoop(rt mi it ci chn n an cn t mc)
*/
SELECT   MIN(n.name) AS voicing_actress,
       MIN(t.title) AS jap_engl_voiced_movie
 FROM  aka_name AS an,
     char_name AS chn,
     cast_info AS ci,
     company_name AS cn,
     info_type AS it,
     movie_companies AS mc,
     movie_info AS mi,
     name AS n,
     role_type AS rt,
     title AS t
WHERE ci.note IN ('(voice)',
                  '(voice: Japanese version)',
                  '(voice) (uncredited)',
                  '(voice: English version)')
  AND cn.country_code ='[us]'
  AND it.info = 'release dates'
  AND mi.info IS NOT NULL
  AND (mi.info LIKE 'Japan:%200%'
       OR mi.info LIKE 'USA:%200%')
  AND n.gender ='f'
  AND n.name LIKE '%An%'
  AND rt.role ='actress'
  AND t.production_year > 2000
  AND t.id = mi.movie_id
  AND t.id = mc.movie_id
  AND t.id = ci.movie_id
  AND mc.movie_id = ci.movie_id
  AND mc.movie_id = mi.movie_id
  AND mi.movie_id = ci.movie_id
  AND cn.id = mc.company_id
  AND it.id = mi.info_type_id
  AND n.id = ci.person_id
  AND rt.id = ci.role_id
  AND n.id = an.person_id
  AND ci.person_id = an.person_id
  AND chn.id = ci.person_role_id;

I get the following:

Aggregate  (cost=35459384571.50..35459384571.51 rows=1 width=64)
  ->  Nested Loop  (cost=30000031677.62..35459384571.49 rows=2 width=32)
        Join Filter: (ci.role_id = rt.id)
        ->  Seq Scan on role_type rt  (cost=0.00..1.15 rows=1 width=4)
              Filter: ((role)::text = 'actress'::text)
        ->  Nested Loop  (cost=30000031677.62..35459384570.08 rows=21 width=36)
              Join Filter: (mi.info_type_id = it.id)
              ->  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=4)
                    Filter: ((info)::text = 'release dates'::text)
              ->  Nested Loop  (cost=30000031677.62..35459384537.91 rows=2380 width=40)
                    ->  Nested Loop  (cost=30000031677.20..35459376985.73 rows=6487 width=44)
                          Join Filter: (ci.person_id = n.id)
                          ->  Seq Scan on name n  (cost=0.00..118123.77 rows=28675 width=19)
                                Filter: (((name)::text ~~ '%An%'::text) AND ((gender)::text = 'f'::text))
                          ->  Materialize  (cost=30000031677.20..35053766466.68 rows=942737 width=37)
                                ->  Nested Loop  (cost=30000031677.20..35053761752.99 rows=942737 width=37)
                                      ->  Nested Loop  (cost=20000031676.77..25053296164.95 rows=176294 width=45)
                                            ->  Nested Loop  (cost=10000031676.34..15052975508.48 rows=360864 width=49)
                                                  Join Filter: (t.id = mi.movie_id)
                                                  ->  Index Scan using info_type_id_movie_info on movie_info mi  (cost=0.43..7142663.77 rows=308977 width=8)
                                                        Filter: ((info IS NOT NULL) AND (((info)::text ~~ 'Japan:%200%'::text) OR ((info)::text ~~ 'USA:%200%'::text)))
                                                  ->  Materialize  (cost=10000031675.91..10003632177.12 rows=1087935 width=41)
                                                        ->  Nested Loop  (cost=10000031675.91..10003626737.45 rows=1087935 width=41)
                                                              ->  Hash Join  (cost=31675.47..851969.06 rows=1982120 width=20)
                                                                    Hash Cond: (ci.person_id = an.person_id)
                                                                    ->  Seq Scan on cast_info ci  (cost=0.00..796166.82 rows=861114 width=16)
                                                                          Filter: ((note)::text = ANY ('{(voice),"(voice: Japanese version)","(voice) (uncredited)","(voice: English version)"}'::text[]))
                                                                    ->  Hash  (cost=20409.10..20409.10 rows=901310 width=4)
                                                                          ->  Seq Scan on aka_name an  (cost=0.00..20409.10 rows=901310 width=4)
                                                              ->  Index Scan using title_pkey on title t  (cost=0.43..1.40 rows=1 width=21)
                                                                    Index Cond: (id = ci.movie_id)
                                                                    Filter: (production_year > 2000)
                                            ->  Index Only Scan using char_name_pkey on char_name chn  (cost=0.43..0.89 rows=1 width=4)
                                                  Index Cond: (id = ci.person_role_id)
                                      ->  Index Scan using movie_id_movie_companies on movie_companies mc  (cost=0.43..2.59 rows=5 width=8)
                                            Index Cond: (movie_id = t.id)
                    ->  Index Scan using company_name_pkey on company_name cn  (cost=0.42..1.16 rows=1 width=4)
                          Index Cond: (id = mc.company_id)
                          Filter: ((country_code)::text = '[us]'::text)

First chn is joined, and then mc. But it should be in the opposite order. By the way if I remove join method hints and use only Leading the order became exactly what was specified. Am I doing everything right? Thank you!

[lucifer12346]

I've encountered the same problem. Have you got any idea about how to fix it?

[ZhengtongYan]

I checked your configurations and hints without finding any problems. I recommend you use the latest PostgreSQL 16.3 version and pg_hint_plan. 16.3 version could generate the correct plan with the specified join order and join algorithms in your hint.

[michaelpq]

I have little idea how to act on that. Trying to use the latest version of PostgreSQL with the latest version of pg_hint_plan may help, or not.

If you think that this is a bug, please provide a self-contained test case. You are providing a big query, which is perhaps fine for you, but nobody can really act on that. For now I am closing that, if you can provide more information, please feel free to reopen or create a new ticket with more information. Thanks.