nuno-faria opened a new issue, #26606:
URL: https://github.com/apache/datafusion/issues/26606
### Describe the bug
String views can cause some queries to be very slow due to many duplicate
references to the same buffers.
For example, consider this query:
```sql
select *
from t1, t2, t3
where t1.c1 = t2.c1
and t1.c1 = t3.c1
and t1.c2 < 10;
```
The plan is something like this:
```sql
HashJoinExec
HashJoinExec output_bytes=258.4 GB (!)
DataSourceExec t1 output_bytes=8.0 GB -- contains utf8view columns
DataSourceExec t2 output_bytes=39.1 KB
DataSourceExec t3
```
Since t1 is on the build side, one batch will be created with refs to all
the buffers. Then, each batch produced by the join will itself point to all the
buffers. In the next join this is repeated, so there will be even more
references to the same buffers.
This causes issues when 1) building batches from the hash join and 2) when
tracking the memory usage metric. The first issue comes from a suboptimal
implementation in `set_source` of `arrow-select`, so that can be easily
optimized. For the second issue, one option would be to `gc()` the build batch
in the hash join. I think a good rule would be to `gc` every time the number of
buffers is greater than some ratio of the number rows (e.g., each buffer is
only used by 4 rows on average), then we would know that there are a lot of
duplicates. If anyone has a better fix for 2) please let me know.
### To Reproduce
This shows the first issue well:
```sql
copy (
select value % 100000 as c1,
case when value = 1 then 1000000000 else 0 end as c2,
'a' || value as c3,
'b' || value as c4,
'c' || value as c5,
'd' || value as c6
from generate_series(1, 30000000)
) to 't1.parquet';
copy (select value as c1 from generate_series(1, 5000)) to 't2.parquet';
copy (select (random() * 10000000)::bigint as c1 from generate_series(1,
200000000)) to 't3.parquet';
create external table t1 stored as parquet location 't1.parquet';
create external table t2 stored as parquet location 't2.parquet';
create external table t3 stored as parquet location 't3.parquet';
explain analyze
select *
from t1, t2, t3
where t1.c1 = t2.c1
and t1.c1 = t3.c1
and t1.c2 < 10;
```
```sql
-- with string view enabled
~46s
-- without string view
~2.9s
```
With string views 97% of the time is spent on `set_source`:
<img width="1393" height="396" alt="Image"
src="https://github.com/user-attachments/assets/70796688-be3c-4b6b-a400-8c8150937443";
/>
For the second issue we need to force more matches in the join with t3:
```sql
copy (select (random() * 200000)::bigint as c1 from generate_series(1,
50000000)) to 't3.parquet';
```
```sql
-- with string view enabled
~202s
-- without string view
~8.5s
```
Here 71% is spent tracking the memory used (and 27% in `set_source`):
<img width="1238" height="425" alt="Image"
src="https://github.com/user-attachments/assets/629fcbbc-5ccd-498b-a3c1-3e2524d043ac";
/>
### Expected behavior
Similar performance with and without string views.
### Additional context
This issue appears to be somewhat related:
https://github.com/apache/datafusion/issues/25712
### Willingness to contribute
I would like to contribute a fix for this bug, but request guidance from the
DataFusion community
--
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]
