Looks correct. 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. Thanks Mostafa On Fri, Jul 25, 2014 at 3:08 PM, Julian Hyde <[email protected]> wrote: > I want to make sure I’m getting my join inputs the right way round. > > Suppose 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 > where c.city = ‘San Francisco' > > It’s a classic snowflake join: > > filtered-customer (1k) <= sales (86k) => product (1.5k) => product_class > (110) > > where “=>” means ‘many-to-one join’ and the numbers are row-counts. > > Is this the best plan? (Each join node has the smaller input on the LHS.) > > ((scan(product_class) join scan(product)) join (filter(scan(customer)) > join scan(sales))) > > I’m close to producing this in OptimizeBushyJoinRule, but I want to check > that it’s the right thing. > > 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.
