How is bushy plans generated in join_search_one_lev

Started by Andy Fanover 5 years ago3 messages
#1Andy Fan
zhihui.fan1213@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Andy Fan (#1)
Re: How is bushy plans generated in join_search_one_lev

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

#3Andy Fan
zhihui.fan1213@gmail.com
In reply to: Tom Lane (#2)
Re: How is bushy plans generated in join_search_one_lev

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