mohamd764 commented on issue #1805:
URL:
https://github.com/apache/datafusion-python/issues/1805#issuecomment-6476820583
I ran this on datafusion 55.0.0 (Python 3.13, 8 cores) and got about 11-12s,
while Python's sqlite3 (3.46.1) took about 2s on the same machine.
Looking at the EXPLAIN output, most of the time seems to go into the
`digits` CTE. It's recursive too, and it gets inlined twice into the recursive
step of `x` (once as `d` and once inside the NOT EXISTS), so it looks like it's
re-run on every iteration of `x` instead of being computed once.
When I replaced it with a plain list, keeping everything else the same:
```sql
digits(z, lp) AS (
VALUES
('1',1),('2',2),('3',3),('4',4),('5',5),('6',6),('7',7),('8',8),('9',9)
)
```
the query dropped to about 1.6-2s with the same two solutions, so roughly on
par with SQLite. Setting target_partitions to 1 only made a small difference
compared to that.
The fix probably belongs in DataFusion core (materializing a CTE that's
referenced more than once, or at least a recursive one used inside another
recursive CTE), but the VALUES rewrite works as a workaround for now.
--
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]
