pandas is the default tool most people reach for to look at a CSV in Python. It’s also built around an assumption that doesn’t hold once a file gets big: that you can afford to load the whole thing into memory before asking it anything.

The setup

Same dataset as the DuckDB benchmark: a 425 MB, 1 million row slice of the NYC taxi dataset. Same rule too: both sides do the same work, cold, every run. pandas has no “query the file in place” mode, so its side is pd.read_csv() followed by the pandas-equivalent operation. That’s not a handicap, that’s how you use pandas.

I ran this two ways, both real, both reproducible with the same script. First, tuned: pandas gets usecols with exactly the columns each query needs, the way anyone optimizing pandas code actually would. Second, naive: a plain pd.read_csv(file), which is what most pandas code actually looks like day to day, nobody hand-picks columns for a quick script. Four queries, best of 3 runs each:

Query	csvql	pandas (usecols)	Speedup	pandas (no usecols)	Speedup
COUNT(*) GROUP BY cab_type	0.071s	1.704s	24.1x	5.356s	75.8x
AVG(total_amount) GROUP BY passenger_count	0.086s	1.697s	19.8x	5.115s	59.6x
COUNT(*) WHERE trip_distance > 5	0.091s	1.751s	19.2x	5.131s	56.3x
Top 3 passenger_count by AVG(tip_amount)	0.102s	1.688s	16.6x	5.138s	50.5x

Both sides agree on the actual numbers, this isn’t a case of one engine cutting corners: cab_type groups came back green: 32447, yellow: 967553 identically on both.

Where the time actually goes

pandas’ per-query time barely moves within either column, 1.69 to 1.75 seconds tuned, 5.1 to 5.4 seconds naive. That’s not coincidence, it’s pd.read_csv() itself dominating both. The actual groupby or filter afterward is fast, DataFrames are good at that part. The cost is parsing 425 MB of CSV text into a DataFrame before any of that can start, and pandas pays that cost fresh on every single call because there’s no persistent structure sitting between your query and the file. Skipping unused columns with usecols helps a lot, about 3x here, but it’s still an order of magnitude slower than not building a DataFrame at all.

csvql pays a version of that same parsing cost, but it’s an order of magnitude cheaper, because it never builds a DataFrame. It scans the raw bytes, applies the query while scanning, and emits only the result rows. No intermediate object graph, no dtype inference across 51 columns you’re not using, no Python-object overhead per cell.

Memory tells the same story, worse

Peak memory on the cab_type query, with usecols narrowing pandas to just what it needs:

csvql    29 MB
pandas  125 MB

About 4.3x less. But that’s the generous case. Drop usecols and just call pd.read_csv(file), which is what most pandas code actually looks like day to day, and loading this file alone takes 3.5 seconds and peaks at roughly 970 MB, before you’ve asked a single question about the data. That’s not a query cost, that’s the price of admission.

Why this is the whole design, not an optimization

csvql doesn’t have an “efficient mode” you opt into. There’s no DataFrame construction step to skip, because there’s no DataFrame. Every query reads the CSV directly off disk, decides what it needs while reading, and discards the rest. For a one-off question, this is the difference between an answer in under a tenth of a second and a multi-second load you pay before you can even start.

pandas earns its dominance for a different job: once the data is loaded, transforming it, joining it against other in-memory structures, feeding it into a model, is exactly what DataFrames are for. If that’s the job, load it once and reuse it. But “I have a CSV and one question about it” is a much more common task than that framing gives it credit for, and paying a multi-second, near-gigabyte tax for a single groupby is the wrong trade for it.

Before you even get to run a query

There’s a cost before any of the numbers above: getting pandas installed in the first place. pip install pandas pulls in numpy and python-dateutil as required dependencies, not optional ones. A clean install in a fresh virtualenv took about 18 seconds on a normal connection, and the installed footprint is around 106 MB (pandas plus numpy alone). That’s before you’ve written a single line of your own code.

csvql is a single static binary with zero runtime dependencies, about 1.2 MB. brew install melihbirim/csvql/csvql and it’s done, no interpreter, no package resolver, nothing else to pull in.

None of this matters if pandas is already sitting in your environment for other reasons, it usually is. But for a throwaway script, a CI job, or a container image where every dependency is something else to build, patch, and audit, that gap compounds. A 1.2 MB binary with nothing behind it is a fundamentally smaller thing to trust than 106 MB of C-extension-backed Python packages.

Reproduce it

git clone https://github.com/melihbirim/csvql
cd csvql && zig build -Doptimize=ReleaseFast
pip install pandas
./bench/bench_taxi.sh --sample   # gets the sample dataset
./bench/bench_pandas.py

Swap in your own CSV and your own usecols. The shape of the result won’t change much: pandas’ cost is dominated by the load, csvql’s isn’t, because it doesn’t have one.