[GitHub] [incubator-doris] Kikyou1997 opened a new issue, #10167: [Bug] Default join reorder algorithm is almost not working at all.

[GitBox <gi...@apache.org>]

Kikyou1997 opened a new issue, #10167:
URL: https://github.com/apache/incubator-doris/issues/10167

   ### Search before asking
   
   - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues.
   
   
   ### Version
   
   latest version
   
   ### What's Wrong?
   
   The default join reorder algorithm is realized in the method `SelectStmt::reorderTable`. If user not manually set `enable_cost_based_join_reorder = true` , Doris will always impose join reorder by this algorithm.
   However, I found this algorithm only works when there is a star schema query.In other words, it will not work at all for the most query.
   What's worse, if this method `reorderTable(Analyzer analyzer, TableRef firstRef)` return `false` (as in the most case it will), the caller method `reorderTable(Analyzer analyzer)`, will place the `tableRef` into the `fromClause` by the row count of talbe in desc order, which menas it will finally cause the cross join of the biggest table,this will totally corrupt the performance of Doris.
   
   ### What You Expected?
   
   fix or simply delete this algorithm.
   
   ### How to Reproduce?
   
   You can reproduce it by following SQL:
   
   Simply insert some data to table t1, table t2.and keep t3 empty.
   
   ```sql
    explain select x.col2 from t1,t2,t3 x,t3 y where x.col1=t2.col1 and y.col1=t2.col2 and t1.col1=y.col1;
   ```
   
   Or run tpcds q17.
   
   ```sql
   
   explain select 
     i_item_id, 
     i_item_desc, 
     s_state, 
     count(ss_quantity) as store_sales_quantitycount, 
     avg(ss_quantity) as store_sales_quantityave, 
     stddev_samp(ss_quantity) as store_sales_quantitystdev, 
     stddev_samp(ss_quantity)/ avg(ss_quantity) as store_sales_quantitycov, 
     count(sr_return_quantity) as store_returns_quantitycount, 
     avg(sr_return_quantity) as store_returns_quantityave, 
     stddev_samp(sr_return_quantity) as store_returns_quantitystdev, 
     stddev_samp(sr_return_quantity)/ avg(sr_return_quantity) as store_returns_quantitycov, 
     count(cs_quantity) as catalog_sales_quantitycount, 
     avg(cs_quantity) as catalog_sales_quantityave, 
     stddev_samp(cs_quantity) as catalog_sales_quantitystdev, 
     stddev_samp(cs_quantity)/ avg(cs_quantity) as catalog_sales_quantitycov 
   from 
     store_sales, 
     store_returns, 
     catalog_sales, 
     date_dim d1, 
     date_dim d2, 
     date_dim d3, 
     store,  
     item 
   where 
     d1.d_quarter_name = '1998Q1' 
     and d1.d_date_sk = ss_sold_date_sk 
     and i_item_sk = ss_item_sk 
     and s_store_sk = ss_store_sk 
     and ss_customer_sk = sr_customer_sk 
     and ss_item_sk = sr_item_sk 
     and ss_ticket_number = sr_ticket_number 
     and sr_returned_date_sk = d2.d_date_sk 
     and d2.d_quarter_name in ('1998Q1', '1998Q2', '1998Q3') 
     and sr_customer_sk = cs_bill_customer_sk 
     and sr_item_sk = cs_item_sk 
     and cs_sold_date_sk = d3.d_date_sk 
     and d3.d_quarter_name in ('1998Q1', '1998Q2', '1998Q3') 
   group by 
     i_item_id, 
     i_item_desc, 
     s_state 
   order by 
     i_item_id, 
     i_item_desc, 
     s_state 
   limit 
     100;
   ```
   
   ### Anything Else?
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

[GitHub] [incubator-doris] Kikyou1997 closed issue #10167: [Bug] Default join reorder algorithm is almost not working at all.

[GitBox <gi...@apache.org>]

Kikyou1997 closed issue #10167: [Bug] Default join reorder algorithm is almost not working at all.
URL: https://github.com/apache/incubator-doris/issues/10167


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org