qsv-performance

name	qsv-performance
description	Performance guide covering index files, stats cache, and frequency cache accelerators for qsv

qsv Performance Guide

Three Accelerators

1. Index Files (.csv.idx)

Created by: qsv index Used by: count, slice, sample, split, stats, frequency, schema, and others marked with 📇

Benefit	Without Index	With Index
Row count	Scan entire file	Instant (stored in index)
Random access	Sequential scan	O(1) lookup
Multithreaded	Not possible	Enabled for many commands
Slicing	Read from start	Jump to position

Rule: Always run index first if you'll run 2+ commands on the same file.

Auto-indexing: The MCP server auto-indexes files > 10MB.

2. Stats Cache (.stats.csv + .stats.csv.data.jsonl)

Created by: qsv stats --cardinality --stats-jsonl Used by: frequency, schema, tojsonl, sqlp, joinp, pivotp, diff, sample (smart commands)

Smart Command	What It Uses from Cache
frequency	Cardinality to skip all-unique columns
schema	Data types for JSON Schema generation
sqlp	Column types for Polars optimization
joinp	Cardinality for optimal join order
pivotp	Cardinality to estimate output width
diff	Column types for comparison

Rule: Run stats --cardinality --stats-jsonl before using any smart command.

Auto-caching: The MCP server auto-adds --stats-jsonl to stats commands.

3. Polars Engine

Commands: sqlp, joinp, pivotp, count (with --polars-len), schema (with --polars)

Benefit	Standard (csv crate)	Polars Engine
Processing model	Row-by-row streaming	Vectorized columnar
Memory	Streaming (constant)	Columnar (efficient)
Parallelism	Single-threaded	Multi-threaded
Large files	Limited by memory	Larger-than-memory
SQL support	N/A	Full SQL dialect

Rule: Use Polars commands (sqlp, joinp, pivotp) for files > 100MB or complex queries.

Parquet Acceleration

For repeated SQL queries on large CSV (> 10MB), consider converting to Parquet with mcp__qsv__qsv_to_parquet. Parquet is a columnar format that speeds up repeated SQL queries in mcp__qsv__qsv_sqlp. Use read_parquet('file.parquet') as the table source. DuckDB is the preferred engine for Parquet queries; mcp__qsv__qsv_sqlp with SKIP_INPUT as the input_file value also works. Note: mcp__qsv__qsv_sqlp can query CSV of any size directly — Parquet is an optimization for repeated queries, not a requirement. Parquet works ONLY with mcp__qsv__qsv_sqlp and DuckDB — all other qsv commands require CSV/TSV/SSV input.

Memory-Aware Command Selection

Commands That Load Entire File into Memory (🤯)

dedup, reverse, sort, stats (with extended stats), table, transpose

Commands with Memory Proportional to Cardinality (😣)

frequency, join, schema, tojsonl

Streaming Commands (constant memory)

Everything else - select, search, slice, replace, count, etc.

Large File Decision Tree

File size?
├── < 10MB: Any command works fine
├── 10MB - 100MB:
│   ├── Always: index first
│   ├── Repeated SQL: consider Parquet with qsv_to_parquet
│   ├── Prefer: streaming commands
│   └── OK: memory-intensive if < available RAM
├── 100MB - 1GB:
│   ├── Always: index + stats cache first
│   ├── Repeated SQL: consider Parquet with qsv_to_parquet
│   ├── Prefer: Polars commands (sqlp, joinp, pivotp)
│   ├── Avoid: sort, reverse, table (load entire file)
│   └── Alternative: sqlp with ORDER BY LIMIT instead of sort
└── > 1GB:
    ├── Must: index + stats cache
    ├── Repeated SQL: convert to Parquet with qsv_to_parquet
    ├── Must: Polars commands only for joins/queries
    ├── Avoid: all 🤯 commands
    └── Consider: split into chunks, process, cat rows

Performance Tips

Tip	Why
Use --output file.csv	Avoids stdout buffering overhead
Use count before stats	Fast row count for progress bars
Use select early in pipeline	Reduce columns = faster processing
Use --no-headers only when needed	Header detection is cheap
Use slice --len N for previews	Don't read entire file to inspect
Prefer joinp over join	Polars engine is significantly faster
Use frequency --limit N	Don't compute all unique values
Use stats --cardinality	Enables smart optimizations downstream

Concurrent Operations

The MCP server limits concurrent qsv operations (default: 1). For multiple independent files, the agent can issue separate tool calls.

Timeout Handling

• Default timeout: 10 minutes (QSV_MCP_OPERATION_TIMEOUT_MS)
• Long operations (sort on huge files) may timeout
• If timeout occurs: try Polars alternative or split the file
• Exit code 124 indicates timeout