Query execution time difference between deltatable QueryBuilder and using DataFusion directly.

[debajyoti-truefoundry]

Describe the bug
What happened:
There are two ways of querying a Delta Table using DataFusion.

1. Using DataFusion directly.
2. Using the Query Builder from Delta.

deltalake==1.0.2
datafusion==47.0.0

import time
from datafusion import SessionContext
from deltalake import DeltaTable, QueryBuilder

dt = DeltaTable("./delta_traces_3/otel_traces")
sql = """
SELECT
  *
FROM tbl
WHERE
("MlRepoId" = 1089) AND ("TracingProjectId" = '222fde49-1f7a-4752-8ec1-06bcdbf570c5') AND ("TraceId" = '8728990bd3d11fa91a688e9d9964bca1') AND ("SpanId" = '82c0a65e80000450')
"""

qb = QueryBuilder().register("tbl", dt)
start = time.monotonic()
table = qb.execute(sql).read_all()
print("Delta QueryBuilder: ", time.monotonic() - start)

ctx = SessionContext()
ctx.register_table_provider("tbl", dt)
start = time.monotonic()
arrow_list = ctx.sql(sql).collect()
print("DataFusion: ", time.monotonic() - start)

python perf_diff.py 
Delta QueryBuilder:  1.2023070430004736
DataFusion:  136.57222191900019

As we can see in the above result, I am noticing a massive difference in the query execution time.

To Reproduce
Steps to reproduce the behavior:

Expected behavior
I was expecting a near-identical execution time.

Additional context
delta-io/delta-rs#3517 (comment)

[timsaucer]

For whoever picks this up, it may be worth first investigating if it's due to the table provider or the ffi interface. I would probably try to reproduce in a pure rust ecosystem without ffi. Then we could add in ffi (still in rust) to eliminate any potential other sources of issues.

[ion-elgreco]

@timsaucer the QueryBuilder is the same tableprovider but just executed in delta-rs directly and then returning a Lazy RecordBatchReader, which you collect with read_all

[renato2099]

I gave this a try with the following:

deltalake              1.5.0
datafusion             52.3.0
pyarrow                20.0.0

generated some data with the following script generate_delta_traces.py and run some queries against that delta file, no special datafusion configuration

def run_delta_query_builder():
    QueryBuilder().register("tbl", dt).execute(sql).read_all()


def run_datafusion():
    ctx = SessionContext()
    ctx.register_table("tbl", dt)
    ctx.sql(sql).collect()


def run_datafusion_single_partition():
    config = SessionConfig().with_target_partitions(1)
    ctx = SessionContext(config)
    ctx.register_table("tbl", dt)
    ctx.sql(sql).collect()

I run the following query which has 0.1% selectivity

sql = """
SELECT
  *
FROM tbl
WHERE
("MlRepoId" = 1089) AND ("TracingProjectId" = '222fde49-1f7a-4752-8ec1-06bcdbf570c5') AND ("TraceId" = '8728990bd3d11fa91a688e9d9964bca1') AND ("SpanId" = '82c0a65e80000450')
"""

the results I got are really similar, there doesn't seem to be a performance regression in the python side

Delta QueryBuilder: avg=0.002449s over 100 runs
DataFusion: avg=0.002464s over 100 runs
DataFusion (single partition): avg=0.001728s over 100 runs

because I didn't see any performance issues, I didn't dive into the rust side yet.

@timsaucer @ion-elgreco: do you think there is something else we need to check here? otherwise I think we can close this issue.

[renato2099]

Hi @timsaucer , could you please us closing this issue ?