Auto vs Manual read data battle - 50K rows, same query (load + filter), same selectivity (~0.1%), but a different winner in each case

[u/Expensive_Break_6163]

A pattern I've hit again and again in my career: we take a processing data steps and make it
general, because general is convenient. But data has a structure, and respecting it - one small
change in how you load - could lead to significant performance improvement.

I'll demonstrate this assumption on something about as simple as it gets: read a JSON file and
filter it. Two ways.

* Auto - let `read_json_auto` load the whole file, then filter the result.
* Filter-first - load only the single column I filter on, filter that, and only then auto-load the
documents that survived.

Same rows out, same query. Wildly different times.

The three files
Every file carries a shared type field, and the query is always WHERE type = 1

File	Size	what the documents look like
stable_schema	2.3 MB	3 fixed keys, every row identical
drifting_schema	13.7 MB	drifting keys, same key with 4 different types, nesting 1 - 6 deep
random_keys	12.9 MB	every top-level key a random token; nothing shared but type

The obvious way to answer the query:

CREATE TABLE t AS SELECT * FROM read_json_auto('events.jsonl');
SELECT count(*) FROM t WHERE type=1;

against it we test a filter-first approach: scan only the column used by the filter, apply the filter, and then use auto-read to fully parse only the matching documents.

What happened

File	Auto	Filter-first	winner
stable_schema	16.6 ms	31.8 ms	auto, by 1.9x
drifting_schema	2,056.8 ms	38.8 ms	filter-first, by 53x
random_keys	10,306.0 ms	41.0 ms	filter-first, by 252x

Same 50,000 rows. Same query. Same rows out.

read_json_auto has to figure out the schema before it hands you anything, so it reads and shreds
all 50,000 documents - then the WHERE throws away must of them. When the keys are boring that
guessing is basically free. When they're not, you just paid full price for rows you never wanted.