jayzhan211 commented on issue #22761: URL: https://github.com/apache/datafusion/issues/22761#issuecomment-6384439909
@neilconway Here is a concrete case where the unscaled `byte_size` changes the plan. #25861 fixed the null counts, but inner and outer joins still copy each input column's `byte_size`. A semi join on top then adds those column sizes up into its `total_byte_size`, and `JoinSelection` compares bytes before rows, so the next join builds on the larger side. Run in `datafusion-cli` from an empty directory: ```sql COPY (SELECT value AS id, value AS v FROM generate_series(1, 20000000)) TO 'data/big.parquet'; COPY (SELECT value AS id FROM generate_series(1, 100)) TO 'data/dim.parquet'; COPY (SELECT value AS id FROM generate_series(1, 100)) TO 'data/sel.parquet'; COPY (SELECT value AS id FROM generate_series(1, 10000000)) TO 'data/mid.parquet'; CREATE EXTERNAL TABLE big STORED AS PARQUET LOCATION 'data/big.parquet'; CREATE EXTERNAL TABLE dim STORED AS PARQUET LOCATION 'data/dim.parquet'; CREATE EXTERNAL TABLE sel STORED AS PARQUET LOCATION 'data/sel.parquet'; CREATE EXTERNAL TABLE mid STORED AS PARQUET LOCATION 'data/mid.parquet'; SET datafusion.explain.format = 'indent'; SET datafusion.explain.show_statistics = true; -- Query 1 EXPLAIN SELECT big.id, big.v FROM big JOIN dim ON big.id = dim.id WHERE big.id IN (SELECT id FROM sel); -- Query 2 SELECT count(*) FROM (SELECT big.id, big.v FROM big JOIN dim ON big.id = dim.id WHERE big.id IN (SELECT id FROM sel)) q JOIN mid ON q.id = mid.id; -- Query 3 SELECT count(*) FROM (SELECT big.id, big.v FROM big JOIN dim ON big.id = dim.id JOIN sel ON big.id = sel.id) q JOIN mid ON q.id = mid.id; ``` | Query | DataFusion | Expected | |---|---|---| | 1 | The semi join reports `Rows=Inexact(100), Bytes=Inexact(320000000)` | About 1,600 bytes: 100 rows of two `BIGINT` columns | | 2 | Builds the hash table on `mid` (10M rows); the `mid` scan reads all 10 row groups; 29 ms | Builds on the 100-row side, as Query 3 does | | 3 | Builds on the 100-row side; the dynamic filter prunes `mid` to 1 of 10 row groups; 3 ms | Same | Query 3 is Query 2 with the `IN` written as an inner join. `sel.id` is unique, so both return 100, and the inner join reports `Bytes=Absent`, so the comparison falls back to rows. This also shows up with join reordering: running JOB on #24456, 23b builds a 4.52M-row hash table against a side that produces 6 rows, because that side reports 45 MB for an estimated 164K rows. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
