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-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
