It is good it works, but the above workaround is not really end-user facing or very ergonomic

Perhaps that could be used as a fallback for when pyarrow isn't installed?

There is no need to go via PyArrow, even when it is installed.
Not requiring PyArrow is one the the goals of the PyCapsule interface ("...instead of hardcoding support for specific Arrow producers." - i.e., PyArrow)

For sure, I meant this as a workaround.

That said, I'm curious what happens if you pass a relation to a relation in this manner.

I tested reverting the change in #13798, which was to fix duckdb/duckdb#13793:

On the plus side, using the pycapsule stream was 30% faster and slightly less (9%) memory.

On the negative side, the same error* occurs from 13793 when the stream is consumed twice: _duckdb.InternalException: INTERNAL Error: ArrowArrayStream was released by another thread/library

This error is the same as #105.

My test case was:

 df = duckdb.sql("SELECT range AS id, 'value_' || range::VARCHAR AS name FROM range(10000000)").pl()
 result = duckdb.sql("SELECT COUNT(*) as cnt FROM df").fetchone()[0]

---- Edit
See #70 . Fixing this would fix the original issue (13793), which would allow 13798 to be reverted.

I wonder if this would be a better solution to duckdb/duckdb#19356.... Oh wow, it is!

With this change, peak memory in the 19356 test case is cut from 3.5x to 2.5x. Which makes sense, since we're going direct from relation -> polars, instead of relation -> pyarrow ->rechunking -> polars.

(削除) edit: Altho, it takes twice as long... tanstaafl. (削除ここまで)- nm

See #137

Reading through https://github.com/duckdb/duckdb-python/blob/main/duckdb/polars_io.py, it looks like PyArrow is only really used to consume the result as a pyarrow.RecordBatchReader. The actual pushdown happens with the DuckDB relational API.

Maybe this request should only consider DataFrames, and not the LazyFrames. Polars LazyFrames don't implement __arrow_c_stream__, so I'm not too sure how that would work anyway.

Please if I am missing anything, let me know. Or would be keen to understand how it is working in bareduckdb.

Re getting rid of the need for PyArrow, Polars recently released a batched iterator, but I'm not quite sure if it is ready/stable as this stage (ref: pola-rs/polars#23980). How are you batching the data in bareduckdb?

TLDR; getting this working for DataFrame and Series and adding the lazy/pushdown support later would definitely be a step in the right direction. I don't think it necessarily has to happen all at once

(this is my non-duckdb core developer understanding, so I am probably wrong somewhere here):

There's (confusingly) two different pushdowns involved here:

1. pushing SQL expressions into an Arrow Scanner: pyarrow_filter_pushdown.cpp
2. pushing Polars operations into a DuckDB relation: polars_io.py

Pushing SQL expressions down

For a Polars LazyFrame or DataFrame, if you do:

duckdb.sql("select * from mylazyframe where id=10")

What happens is:

1. If LazyFrame, collect. If DataFrame, skip to step 2
2. The LazyFrame/DataFrame is converted to a PyArrow Table, via to_arrow
   

   duckdb-python/src/duckdb_py/python_replacement_scan.cpp

   Lines 149 to 150 in 694922b

   auto materialized = entry.attr("collect")();

   auto arrow_dataset = materialized.attr("to_arrow")();
3. The Table is then converted to a Scanner

   duckdb-python/src/duckdb_py/arrow/arrow_array_stream.cpp

   Line 61 in 694922b

   unique_ptr<ArrowArrayStreamWrapper> PythonTableArrowArrayStreamFactory::Produce(uintptr_t factory_ptr,
4. Pushdowns are applied

   duckdb-python/src/duckdb_py/arrow/arrow_array_stream.cpp

   Line 52 in 694922b

   auto filter = PyArrowFilterPushdown::TransformFilter(*filters, parameters.projected_columns.projection_map,
   and implemented in

   duckdb-python/src/duckdb_py/arrow/pyarrow_filter_pushdown.cpp

   Line 307 in 694922b

   py::object PyArrowFilterPushdown::TransformFilter(TableFilterSet &filter_collection,
5. The Scanner is converted to a RecordBatchReader

   duckdb-python/src/duckdb_py/arrow/arrow_array_stream.cpp

   Line 117 in 694922b

   auto record_batches = scanner.attr("to_reader")();

BUT, if you provide a PyCapsule, all of this is bypassed and the data is consumed without the benefit of pushdown.

polars_io.py

polars_io.py pushes SQL expressions into the DuckDB query, when you're using a LazyFrame.

duckdb.sql("SELECT * FROM parquet_file").pl(lazy=True).filter(pl.col("price") > 100)

In this case, polars_io rewrites the query to be "SELECT * FROM parquet_file where price > 100".

Possible Improvements

The two differences between bareduckdb and duckdb-python that I was highlighting:

producing and consuming pyarrow capsules

• bareduckdb can produce and consume pyarrow capsules without pyarrow.
• duckdb can't produce a Polars table right now without pyarrow, but a small change would make it possible as I noted here: Feature Request: Remove PyArrow requirement for Polars import/export #132 (comment)

pushdowns into Polars

• if given a Polars pycapsule, pushdown is bypassed.
• I implemented an experimental option in bareduckdb that implements the same type of logic as pyarrow_filter_pushdown, but using Polars primitives. This allows pushdown on both Polars DataFrames and... most importantly... on Polars LazyFrames before collection.

* just to be clear, I'm not suggesting you use bareduckdb. It's purely an experimental project. I'm sharing only to explain possible improvements to duckdb-python. Happy to discuss more at bareduckdb.