The algorithm is correct. As we don't benefit much from carrying over the extra columns throughout the join, since store is not expected to reduce the join output.
On cn105 30TB TPC-DS Q17 the delta is 20% between the two plans, the faster plan is the one where store and item are joined late in the plan. Check the email with title "Performance of Q17 for bushy plan rewrite on CN105". Thanks Mostafa On Fri, Jul 25, 2014 at 3:33 PM, Julian Hyde <[email protected]> wrote: > On Jul 25, 2014, at 3:14 PM, Mostafa Mokhtar <[email protected]> > wrote: > > What would be the plan if we have this query? > > select * > from sales as s > join customer as c on s.customer_id = c.customer_id > join product as p on s.product_id = p.product_id > join product_class as pc on p.product_class_id = pc.product_class_id > join store as st on s.store_id = st.store_id > where c.city = ‘San Francisco' > > > Where store doesn't have any filters and row count is 20. > > > The algorithm is currently telling me > > (((product_class x product) > x (customer x sales)) > x store) > > My gut says > > (store > x ((product_class x product) > x (customer x sales))) > > What do you think? > > Julian > -- CONFIDENTIALITY NOTICE NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
