disable_parquet_prefetching: v2.0 issues 51x more HTTP requests (33 -> 1,697) and a 2s remote TOP-N becomes 20s

What happens?

Setting disable_parquet_prefetching = true causes the v2.0 line to issue roughly fifty times more HTTP range requests for the same query, and the query slows from ~2s to ~20s. On v1.4.3 the same setting costs ~2.5x more requests but no measurable wall time, so the effect is specific to the 2.0 reader rather than to the setting's intent.

Measured on one query against 26 Parquet files on S3 (7M rows; one file holds a single 6M-row row group with older data, the other 25 hold newer data - the shape a compaction pass tends to produce), 3 interleaved reps per arm, fresh process per run, from a laptop to us-east-1:

engine knob unset knob = true requests unset -> true v2.0.0-alpha41533 2-3 s 19-20 s (one 74.6 s) 33 -> 1,697 (51x) v1.4.3 2.26-3.32 s 3.09-3.15 s 53 -> 133 (2.5x)

Request counts are rows of type HTTP from duckdb_logs() for v1.4.3, and distinct signed requests in the console log for the alpha (see the separate logging issue: v2.0's HTTP rows never reach duckdb_logs()). Counts were identical on every rep on both engines.

Two further notes:

1. The alpha's DEFAULT path is the more efficient of the two (33 requests vs 53), so this is not a general async-reader problem - it is specific to disabling prefetching.

2. Over a high-latency link the query stops looking slow and starts looking hung. Cold runs against a wider real table (15 columns, one 52.7M-row row group after compaction) exceeded a 120s cap on v1.4.3, v1.5.5 and the alpha alike, while the same statement in-region completes: 0.391s -> 10.9s and 12.2s with the knob -> 0.399s with it off, issuing ~350 requests against ~27 normally. We initially mistook this for a hang in 2.0; it is not, and it is not 2.0-specific at that scale.

3. While the query is stalled the main thread burns a full core: /proc//stat shows utime 115-121s of 120s wall, with workers parked. gdb thread apply all bt on v1.5.5 shows 6 threads in futex, 4 in epoll_wait, one worker in duckdb::HTTPFSCurlClient::Get, and Thread 1 spinning in duckdb::ConcurrentQueue::DequeueFromProducer <- duckdb::Executor::ExecuteTask <- ClientContext::ExecuteTaskInternal. Same signature on alpha41533 (44 threads: 37 futex, 4 epoll, 2 poll, main thread in Executor::ExecuteTask). Backtraces available on request.

Why we set the knob at all: issue #21474 recommends SET disable_parquet_prefetching = true for filtered S3 queries where row-group statistics prune most groups. On this layout it is harmful, so at minimum that advice looks layout-dependent.

Expected: disabling prefetching should stop read coalescing, not multiply the request count fiftyfold.

To Reproduce

-- 1. generate the layout (any engine, into your own bucket) LOAD httpfs; LOAD aws; CREATE OR REPLACE SECRET s3sec (TYPE s3, PROVIDER credential_chain, REGION 'us-east-1'); SET memory_limit = '6GB'; SET preserve_insertion_order = false;

COPY (SELECT md5(((random()*57)::INT)::VARCHAR) AS id, 1700000000000000 + range::BIGINT * 10 AS ts_us, random() AS v FROM range(6000000)) TO 's3:///repro1/big/big.parquet' (FORMAT parquet, ROW_GROUP_SIZE 6000000, COMPRESSION zstd);

COPY (SELECT md5(((random()*57)::INT)::VARCHAR) AS id, 1700000000000000 + 6000000::BIGINT * 10 + range::BIGINT * 10 AS ts_us, random() AS v, (range % 25)::INT AS part FROM range(1000000)) TO 's3:///repro1/small' (FORMAT parquet, PARTITION_BY part, ROW_GROUP_SIZE 40000, COMPRESSION zstd);

-- 2. pick an id that exists in the newest files SELECT id FROM read_parquet('s3:///repro1/small/part=0/*.parquet') LIMIT 1;

-- 3. measure, ONE FRESH PROCESS PER ARM, alternating the arms -- arm A (default): SELECT count() FROM (SELECT ts_us FROM read_parquet('s3:///repro1/**/.parquet') WHERE id = '<id from step 2>' ORDER BY ts_us DESC LIMIT 200); -- arm B: same query, preceded by SET disable_parquet_prefetching = true;

Note on method: run the arms interleaved, not as a block of one arm followed by a block of the other. Over a shared network link we measured the same effect as anywhere from 3.75x to 12.2x depending on the baseline, and the baseline alone moved 3x for one binary on one machine between two sessions hours apart. The stable quantities are the request count and the absolute time of the knob arm.

OS:

Ubuntu 22.04 on WSL2, x86_64 (also reproduced on Windows 11 x86_64)

DuckDB Version:

v2.0.0-alpha41533; also v1.4.3, v1.5.5, and v2.0.0-alpha38195 on Windows

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Dan Crawford

Affiliation:

Raven Connected Inc.

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes