2010YOUY01 commented on issue #17259:
URL: https://github.com/apache/datafusion/issues/17259#issuecomment-3206973073
I checked q4, the join order is not optimal. After applying the filter left
table is still way larger than the right table.
I think another reason to make the performance gap larger is: this bad join
order caused memory usage greater than physical memory limit (I tried it's
using 21GB peak memory, which is > 16GB in the report), and the OS swapping
makes it significantly slower.
<details>
```sh
DataFusion CLI v49.0.1
> CREATE EXTERNAL TABLE IF NOT EXISTS lineitem (
l_orderkey BIGINT,
l_partkey BIGINT,
l_suppkey BIGINT,
l_linenumber INTEGER,
l_quantity DECIMAL(15, 2),
l_extendedprice DECIMAL(15, 2),
l_discount DECIMAL(15, 2),
l_tax DECIMAL(15, 2),
l_returnflag VARCHAR,
l_linestatus VARCHAR,
l_shipdate DATE,
l_commitdate DATE,
l_receiptdate DATE,
l_shipinstruct VARCHAR,
l_shipmode VARCHAR,
l_comment VARCHAR,
) STORED AS parquet
LOCATION '/Users/yongting/Code/datafusion/benchmarks/data/tpch_sf1/lineitem';
CREATE EXTERNAL TABLE orders
STORED AS PARQUET
LOCATION '/Users/yongting/Code/datafusion/benchmarks/data/tpch_sf1/orders/';
0 row(s) fetched.
Elapsed 0.023 seconds.
0 row(s) fetched.
Elapsed 0.009 seconds.
> explain select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
+---------------+------------------------------------------------------------+
| plan_type | plan
|
+---------------+------------------------------------------------------------+
| physical_plan | ┌───────────────────────────┐
|
| | │ SortPreservingMergeExec │
|
| | │ -------------------- │
|
| | │ o_orderpriority ASC NULLS │
|
| | │ LAST │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ SortExec │
|
| | │ -------------------- │
|
| | │ o_orderpriority@0 ASC │
|
| | │ NULLS LAST │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ ProjectionExec │
|
| | │ -------------------- │
|
| | │ o_orderpriority: │
|
| | │ o_orderpriority │
|
| | │ │
|
| | │ order_count: │
|
| | │ count(Int64(1)) │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ AggregateExec │
|
| | │ -------------------- │
|
| | │ aggr: count(1) │
|
| | │ │
|
| | │ group_by: │
|
| | │ o_orderpriority │
|
| | │ │
|
| | │ mode: │
|
| | │ FinalPartitioned │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ CoalesceBatchesExec │
|
| | │ -------------------- │
|
| | │ target_batch_size: │
|
| | │ 8192 │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ RepartitionExec │
|
| | │ -------------------- │
|
| | │ partition_count(in->out): │
|
| | │ 14 -> 14 │
|
| | │ │
|
| | │ partitioning_scheme: │
|
| | │ Hash([o_orderpriority@0], │
|
| | │ 14) │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ AggregateExec │
|
| | │ -------------------- │
|
| | │ aggr: count(1) │
|
| | │ │
|
| | │ group_by: │
|
| | │ o_orderpriority │
|
| | │ │
|
| | │ mode: Partial │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ CoalesceBatchesExec │
|
| | │ -------------------- │
|
| | │ target_batch_size: │
|
| | │ 8192 │
|
| | └─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐
|
| | │ HashJoinExec │
|
| | │ -------------------- │
|
| | │ join_type: RightSemi │
|
| | │ ├──────────────┐
|
| | │ on: │ │
|
| | │ (l_orderkey = o_orderkey) │ │
|
| | └─────────────┬─────────────┘ │
|
| | ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
|
| | │ CoalesceBatchesExec ││ CoalesceBatchesExec │
|
| | │ -------------------- ││ -------------------- │
|
| | │ target_batch_size: ││ target_batch_size: │
|
| | │ 8192 ││ 8192 │
|
| | └─────────────┬─────────────┘└─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
|
| | │ RepartitionExec ││ RepartitionExec │
|
| | │ -------------------- ││ -------------------- │
|
| | │ partition_count(in->out): ││ partition_count(in->out): │
|
| | │ 14 -> 14 ││ 14 -> 14 │
|
| | │ ││ │
|
| | │ partitioning_scheme: ││ partitioning_scheme: │
|
| | │ Hash([l_orderkey@0], 14) ││ Hash([o_orderkey@0], 14) │
|
| | └─────────────┬─────────────┘└─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
|
| | │ CoalesceBatchesExec ││ CoalesceBatchesExec │
|
| | │ -------------------- ││ -------------------- │
|
| | │ target_batch_size: ││ target_batch_size: │
|
| | │ 8192 ││ 8192 │
|
| | └─────────────┬─────────────┘└─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
|
| | │ FilterExec ││ FilterExec │
|
| | │ -------------------- ││ -------------------- │
|
| | │ predicate: ││ predicate: │
|
| | │ l_receiptdate > ││ o_orderdate >= 1993-07-01 │
|
| | │ l_commitdate ││ AND o_orderdate < 1993 │
|
| | │ ││ -10-01 │
|
| | └─────────────┬─────────────┘└─────────────┬─────────────┘
|
| | ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
|
| | │ DataSourceExec ││ DataSourceExec │
|
| | │ -------------------- ││ -------------------- │
|
| | │ files: 21 ││ files: 21 │
|
| | │ format: parquet ││ format: parquet │
|
| | │ ││ │
|
| | │ predicate: ││ predicate: │
|
| | │ l_receiptdate > ││ o_orderdate >= 1993-07-01 │
|
| | │ l_commitdate ││ AND o_orderdate < 1993 │
|
| | │ ││ -10-01 │
|
| | └───────────────────────────┘└───────────────────────────┘
|
| |
|
+---------------+------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.050 seconds
```
</details>
--
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: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
