saadtajwar commented on PR #23957:
URL: https://github.com/apache/datafusion/pull/23957#issuecomment-5227308527
@kumarUjjawal thanks for the PR comments! Pushed some commits to address
those, please let me know your thoughts!
I couldn't find any existing benchmarks that exercises this path, so I just
ran the below on my local machine! Please let me know your thoughts - thanks
again for taking the time to review!
```sql
-- large outer (nullable key)
CREATE TABLE big_outer AS
SELECT
CASE WHEN value % 17 = 0 THEN CAST(NULL AS BIGINT) ELSE value END AS k,
value AS payload
FROM range(5000000);
-- small subquery side (nullable key)
CREATE TABLE small_dim AS
SELECT
CASE WHEN value % 19 = 0 THEN CAST(NULL AS BIGINT) ELSE value END AS k
FROM range(20000);
SET datafusion.optimizer.join_reordering = true;
SET datafusion.optimizer.prefer_hash_join = true;
EXPLAIN
SELECT count(*)
FROM big_outer o
WHERE o.k NOT IN (SELECT k FROM small_dim);
```
Query under test:
```sql
SELECT count(*)
FROM big_outer o
WHERE o.k NOT IN (SELECT k FROM small_dim);
```
Runner flags (same on both sides):
```bash
--iterations 3 --partitions 2 --batch-size 4096 --memory-limit 2G
```
#### Plans
| Branch | Hash join |
|---|---|
| `main` | `LeftAnti`, `null_aware` (build on **outer** / 5M rows) |
| this PR | `RightAnti`, `CollectLeft`, `null_aware` (build on **subquery**
/ 20K rows) |
#### Results
| | iter 0 | iter 1 | iter 2 | Peak pool reserved |
|---|---:|---:|---:|---:|
| **main** | 58.8 ms | 57.6 ms | 51.9 ms | **77.5 MB** |
| **this PR** | 36.2 ms | 31.6 ms | 31.3 ms | **314.9 KB** |
So for this shape the PR cuts peak join memory by ~250x (build moves from
the outer table to the subquery) and is ~1.7x faster wall-clock on this machine
--
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]
