Postgres Pro
Downloads
Home   >   mailing lists

Thread: How is bushy plans generated in join_search_one_lev

How is bushy plans generated in join_search_one_lev

From
Andy Fan
Date:
Greeting. 

I do see the README says we support bushy plans and I also see bushy
plans in real life (for example tpc-h Q20) like below. However I don't know
how it is generated with the algorithm in join_search_one_lev since it always
make_rels_by_clause_join with joinrel[1] which is initial_rels which is baserel. 
Am I missing something?

===
 Sort
   Sort Key: supplier.s_name
   ->  Nested Loop Semi Join
         ->  Nested Loop
               Join Filter: (supplier.s_nationkey = nation.n_nationkey)
               ->  Index Scan using supplier_pkey on supplier
               ->  Materialize
                     ->  Seq Scan on nation
                           Filter: (n_name = 'KENYA'::bpchar)
         ->  Nested Loop
               ->  Index Scan using idx_partsupp_suppkey on partsupp
                     Index Cond: (ps_suppkey = supplier.s_suppkey)
                     Filter: ((ps_availqty)::numeric > (SubPlan 1))
                     SubPlan 1
                       ->  Aggregate
                             ->  Index Scan using idx_lineitem_part_supp on lineitem
                                   Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
                                   Filter: ((l_shipdate >= '01-JAN-97 00:00:00'::timestamp without time zone) AND (l_shipdate < '01-JAN-98 00:00:00'::timestamp without time zone))
               ->  Index Scan using part_pkey on part
                     Index Cond: (p_partkey = partsupp.ps_partkey)
                     Filter: ((p_name)::text ~~ 'lavender%'::text)
(21 rows)


--
Best Regards
Andy Fan

Re: How is bushy plans generated in join_search_one_lev

From
Tom Lane
Date:
Andy Fan <zhihui.fan1213@gmail.com> writes:
> I do see the README says we support bushy plans and I also see bushy
> plans in real life (for example tpc-h Q20) like below. However I don't know
> how it is generated with the algorithm in join_search_one_lev since it
> always
> make_rels_by_clause_join with joinrel[1] which is initial_rels which is
> baserel.

Hmm?  Bushy plans are created by the second loop in join_search_one_level,
starting about line 150 in joinrels.c.

            regards, tom lane

Re: How is bushy plans generated in join_search_one_lev

From
Andy Fan
Date:


On Thu, Aug 27, 2020 at 8:05 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andy Fan <zhihui.fan1213@gmail.com> writes:
> I do see the README says we support bushy plans and I also see bushy
> plans in real life (for example tpc-h Q20) like below. However I don't know
> how it is generated with the algorithm in join_search_one_lev since it
> always
> make_rels_by_clause_join with joinrel[1] which is initial_rels which is
> baserel.

Hmm?  Bushy plans are created by the second loop in join_search_one_level,
starting about line 150 in joinrels.c.

                        regards, tom lane

Yes.. I missed the second loop:(:(:( 

--
Best Regards
Andy Fan