Huge performance gap between driver and CLI

[ndarilek]

Hello, not sure this is strictly speaking a driver issue but it's where I encounter it. I'm essentially running this query:

install httpfs;
load httpfs;
install spatial;
load spatial;
set s3_access_key_id = '';
set s3_secret_access_key = '';
set s3_region='us-west-2';
create table place as select * from read_parquet('s3://overturemaps-us-west-2/release/2025-03-19.0/theme=places/type=place/*') where bbox.xmin > -97.734375 and bbox.xmax < -97.3828125 and bbox.ymin > 30.234375 and bbox.ymax < 30.41015625;

nolan@flynode ~/P/Waynav (main) [1]> time duckdb -s "install httpfs; load httpfs
; install spatial; load spatial; set s3_access_key_id = ''; set s3_secret_access
_key = ''; set s3_region='us-west-2'; create table place as select * from read_p
arquet('s3://overturemaps-us-west-2/release/2025-03-19.0/theme=places/type=place
/*') where bbox.xmin > -97.734375 and bbox.xmax < -97.3828125 and bbox.ymin > 30
.234375 and bbox.ymax < 30.41015625;"
100% ▕60 █ characters▏ 
56 _ characters
Executed in   18.94 secs    fish           external
   usr time   13.93 secs    2.11 millis   13.93 secs
   sys time    0.36 secs    0.09 millis    0.36 secs

Now I want to run that same set of queries via Rust, so I wrote this:

use duckdb::{params, Connection};

const ROOT: &str = "s3://overturemaps-us-west-2/release/2025-03-19.0";

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let conn = Connection::open_in_memory()?;
    let _ = conn.execute_batch(
        r#"install httpfs;
        load httpfs;
        install spatial;
        load spatial;
        set s3_access_key_id = '';
        set s3_secret_access_key = '';
        set s3_region='us-west-2';
        "#,
    )?;
    let table = "place";
    let theme = "theme=places/type=place";
    let xmin = -97.734375;
    let xmax = -97.3828125;
    let ymin = 30.234375;
    let ymax = 30.41015625;
    let root = format!("{ROOT}/{theme}/*");
    println!("Making table");
    println!(r#"create table {table} as select * from read_parquet("{root}") where bbox.xmin > {xmin} and bbox.xmax < {xmax} and bbox.ymin > {ymin} and bbox.ymax < {ymax};"#);
    let query = format!(r#"create table {table} as select * from read_parquet(?) where bbox.xmin > ? and bbox.xmax < ? and bbox.ymin > ? and bbox.ymax < ?;"#);
    conn.execute(&query, params![root, xmin, xmax, ymin, ymax,])?;
    println!("Counting");
    conn.query_row("select count() from place;", [], |row| {
        let count: usize = row.get(0).unwrap();
        println!("{count}");
        Ok(())
    })?;
    Ok(())
}

But it runs far slower:

nolan@flynode ~/P/duckdb_test> time cargo run --release
    Finished `release` profile [optimized] target(s) in 0.22s
     Running `target/release/duckdb_test`
Making table
create table place as select * from read_parquet("s3://overturemaps-us-west-2/re
lease/2025-03-19.0/theme=places/type=place/*") where bbox.xmin > -97.734375 and 
bbox.xmax < -97.3828125 and bbox.ymin > 30.234375 and bbox.ymax < 30.41015625;
Counting
16119
56 _ characters
Executed in  547.47 secs    fish           external
   usr time  109.85 secs  253.00 micros  109.85 secs
   sys time   24.08 secs  278.00 micros   24.08 secs

I know the Rust code does a bit more with the count, but even so it takes a long time to get there.

Is there some tuning the CLI does that makes this workload run orders of magnitude faster there? I wouldn't sweat if it was a small difference but this is huge enough to make the project I'm working on non-viable.

Thanks for any help.

[pmetras]

Having a similar performance problem, I profiled my executable and I found that installing extensions had a major impact on the execution time:

• If the extension is not part of the executable, and that's the case for important extensions like icu, install icu; load icu takes a lot of time/CPU power while Duckdb checks the signature of the extension file in duckdb::ComputeSHA256FileSegment function.
• When it's the first run, duckdb must download the extension from the extension repository (http://extensions.duckdb.org).

Probably that if you manage to link the httpfs and spatial extensions to your executable, the install and load would be much faster because Duckdb would not have to check the validity of these extensions as it is supposed to be done at compilation-time instead of run-time.

[ndarilek]

Interesting analysis, thanks for doing the work!

I'm still unclear why the statements are faster in the CLI. My assumption was that these codepaths were identical--one just runs statements in the CLI while the other runs them through the driver. In this case I'd expect the same extension bottlenecks while running the CLI, assuming it loads extensions via the same mechanism. So I'm wondering if the CLI sets settings that speed things up somehow, maybe bypassing whatever slow checks you've spotted.

Thanks again.

[pmetras]

No, the CLI and the duckdb-rs crate don't load the same extensions. Here you can see the extensions that are included (linked) in the code of the CLI, but the duckdb-rs does not include parquet, for instance. You can list the preloaded extensions from your code by executing select * from duckdb_extensions().

[ndarilek]

Hmm, OK, so maybe part of this is the driver needing to pull in more extensions than I'm actually loading?

Do extensions load dependencies, or even have dependencies? If I'm loading Overture Maps data like I am here, which is in geoparquet, is the parquet support provided by that extension or by GDAL/whatever handles the geospatial formats?

And I was actually thinking the slowdown may come from different settings in the CLI, not necessarily different extensions loaded, but I haven't done a diff of what settings the CLI sets vs. whatever settings the drivers set by default.

[ndarilek]

I've done a bit more digging and am no closer to a solution. Here's what I've done:

The test cases are packaged here. test1.sh builds the Rust example and runs it. I haven't let this run to conclusion yet but it'd likely take hours. test2.sh runs similar commands to test1 but doesn't create spatial indexes and uses the CLI and SQL. It runs much faster, completing in under 5 minutes. I know these aren't exactly equal, but even in the slower run, creating spatial indexes didn't take long. It's the download that takes forever in Rust.

The Rust testcase enables the extensions-full feature which I'd assume links in the necessary extension. If the problem is extension-load-related then I'd expect that to help, but unfortunately it doesn't make a difference.

The Rust version also prints out the settings set by the driver. I haven't done a detailed check but the ones I eyeballed related to threads/keepalive look equivalent. Maybe other settings aren't equal and might have an impact--I just checked the ones that seemed obvious.

I notice the driver creates a WAL while the CLI run doesn't. I wonder if that might be the difference, though it seems like it shouldn't be. Can I either get the CLI to use a WAL too, or tell the driver not to? Doesn't seem like that should be the issue but it is a difference.

If I enable HTTP logging, it seems like the driver is querying every Overture file per theme--it seems to iterate upward slowly. If I enable logging in the CLI, it seems to move lots faster and appears to perform more HEAD requests. This is what I'd expect to see--it almost looks like the driver is downloading everything while the CLI is trying to use HEAD requests more frequently and downloading less.

[pmetras]

The test cases are packaged here.

The referenced link gives a 404: https://dev.thewordnerd.info/nolan/duckdb-test

[ndarilek]

The referenced link gives a 404:

Ah, for some reason my attempt to make it non-private didn't stick. Should be fixed now, thanks for letting me know.

[ndarilek]

I had a theory that maybe bundling was the issue and that the bundled build wasn't being compiled in release mode. Unfortunately that didn't seem to pan out.

The previously-linked build strips the bundling/extensions-related features, installs NixOS' duckdb library, and sets up the build environment to find it. Assuming I've done that correctly, both testcases should be using the same library with the same optimizations. There's still a pretty big gap.

[ndarilek]

I found the fix but it's unclear if it's a bug or expected behavior. Previously I injected the bbox into the query as params. Now though I'm writing them into the query directly:

        let query = format!(
            r#"create table {table} as select * from read_parquet(?) where bbox.xmin between {xmin} and {xmax} and bbox.ymin between {ymin} and {ymax};"#
        );
        conn.execute(&query, params![root,])?;

I should probably remove ROOT next but I wanted to determine if the issue was all params, those specific params, or something in the params! macro. Thankfully it was just the bbox.

This gets the Rust version executing in under 5 minutes, on par with the raw commands.

This is probably fine for my use case since the query inputs aren't directly user-generated, but it does make me a bit uncomfortable that not escaping certain query parameters can silently cause a many orders of magnitude slowdown. I'll go ahead and escape the parameters in my code for now just to get something working, but it'd be great to avoid doing that if I can.

Thanks to everyone who helped profile this.