[Python] python polars numerous joins crashing

This is for a POC to see if polars can do some things faster/better/cheaper than a current SQL solution. The first test case involves a count(*) over an eight-table join. The eight tables are loaded into lazy dataframes and then the following code is run:

lazy_final = (
noun1.lazy().select(["noun_airport_nk"])
.join(adj2.lazy().select(["noun_airport_nk"]), left_on="noun_airport_nk", right_on="noun_airport_nk", how="inner", suffix="__adj2")
.join(verb3.lazy().select(["noun_airport_nk","noun_coupon_nk"]), on="noun_airport_nk", how="inner", suffix="__verb3")
.join(noun4.lazy().select(["noun_coupon_nk"]), left_on="noun_coupon_nk", right_on="noun_coupon_nk", how="inner", suffix="__noun4")
.join(adj5.lazy().select(["noun_coupon_nk"]), on="noun_coupon_nk", how="inner", suffix="__adj5")
.join(verb6.lazy().select(["noun_airport_nk"]), on="noun_airport_nk", how="inner", suffix="__verb6")
.join(noun7.lazy().select(["noun_airport_nk"]), on="noun_airport_nk", how="inner", suffix="__noun7")
.join(adj8.lazy().select(["noun_airport_nk"]), on="noun_airport_nk", how="inner", suffix="__adj8")
)

count_df = lazy_final.select(pl.len().alias('count'))
print(count_df.collect(engine="gpu"))


The right answer is ~30m. But the above code runs out of memory and crashes on a 4-core 16GB system with an NVIDIA L4 GPU. This is larger than the test SQL platform currently in test.

Most of the tables are narrow (<10 columns) but a few are wider (>30 columns). We've tried limiting to only the columns needed for the joins but that hasn't helped.

Is there a solution here or is this not a good use case for polars?

UPDATE: removing the collect(engine="gpu") option prevents crashing and returns the execution plan (as expected). Turning this into a 2-3 table join also prevents crashing. The crashes begin as the larger tables are incorporated into the statement.

UPDATE2: Apologies for using the word "crash". Nothing is crashing in the technical sense, the process is being killed for running out of 16GB system memory (not 64GB GPU memory - to @ritchie46 question). I agree that I don't need the gpu, but I have the same result if I use:

.collect(streaming=True)
.collect(engine="gpu")
.collect()


I think the datatypes are compacted as much as possible - although there are many String types, unfortunately.

I'm using 1.24 in python but when I try new_streaming=True I get the "not implemented" message (I also get the deprecation warning when using streaming=True). So I'm not sure what I'm doing wrong here.

Continue reading...