{"note":"The query-complexity tree: every entry introduces exactly one atomic construct, and its SQL exhibits exactly its root path's construct set — position, parent, and `rung` (the number of constructs carried, never a difficulty) are derived from the SQL at build time, never authored. Each construct belongs to one of eight `axes` (anchor, scope, qualifier, derivation, statistic, shape, composition, presentation); a node's children read grouped by axis. Along a chain the relation, the anchor term and the time window are held constant (the control), so a child's clock is attributable to its construct: every entry's `seconds`, `read_rows` and `read_bytes` are the median of three executions through the live API on the stated date. Walk any root-to-leaf path as a chain (`?mode=chains`); `?mode=tree` nests the full taxonomy. Above the tree, `compositions` join constructs ACROSS families: each names its factor entries and its SQL fires exactly the union of their paths. Read `why_fast` before adapting an entry to a new predicate.","axes":[{"axis":"anchor","question":"How are the candidate documents found?"},{"axis":"scope","question":"Within what field or time domain?"},{"axis":"qualifier","question":"Which candidates, or which groups, pass a further condition?"},{"axis":"derivation","question":"What value is computed from a document, an array, or a row?"},{"axis":"statistic","question":"What summary is computed across documents or events?"},{"axis":"shape","question":"How are rows grouped, expanded, ordered, selected, or windowed?"},{"axis":"composition","question":"How are query blocks or relations combined?"},{"axis":"presentation","question":"How is a result encoded for a reader?"}],"examples":[{"slug":"selectivity-probe","construct":"token-anchor","parent":null,"rung":1,"axis":"anchor","title":"Count before you read","problem":"You are about to ask twenty months of Hacker News about LLMs and have no idea whether the term matches three hundred items or three hundred thousand. A retrieval query pays for ordering an unknown match set and finds out the hard way. A count settles the size of the question before anything else is spent on it.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm') LIMIT 1\n","technique":"The count-only probe runs the WHERE clause and nothing else: a closed time window on both ends and one lowercase token against the indexed search_text_lc column. 131,838 hits is the denominator every child of this node divides by. hasToken engages the token index; a bare LIKE over the same column is refused by the validator, and a token with uppercase letters or punctuation never matches. The LIMIT 1 is the bounded-statement idiom the API expects on every query.","whyFast":"6.2 M rows and 2.1 GB read for the whole window: the token index discards granules that cannot hold the term, and the survivors are read as one column, search_text_lc, in PREWHERE. No title or body is decompressed, no row is sorted, and the aggregate state is a single counter, so the time is the scan of the anchored granules and nothing more. Every entry below reports its rows read against this 6.2 M.","measured":"0.47 s (median of three) over 20 months of hackernews.items, 6.2 M rows read, 131,838 hits (2026-09-07).","seconds":0.47,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["probe","hasToken","count-first"]},{"slug":"both-words-present","construct":"all-tokens-anchor","parent":"selectivity-probe","rung":2,"axis":"anchor","title":"How much LLM talk is about running models locally","problem":"One token finds the LLM discourse; the analyst wants the slice of it about local inference, the ollama-and-a-GPU corner where cost, privacy and hardware get argued. A second token must be required, not merely allowed, and it must still come out of the index rather than a scan of every matched body.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND hasAllTokens(search_text_lc, ['local', 'llm']) LIMIT 1\n","technique":"hasAllTokens(column, [t1, t2]) is the conjunctive anchor: every listed token must be present, and each is looked up in the same token index hasToken uses. The parent's hasToken line stays and the array repeats 'llm', so the child's token set is a superset of the parent's, which is the rule the tree checks. 5,656 of the 131,838 LLM items (4.3%) also say 'local'. Tokens are order-free: this counts co-occurrence anywhere in the document, which is why the next entry exists.","whyFast":"6.1 M rows and 2.1 GB read against the parent's 6.2 M: the second token lets the index drop the few granules that hold 'llm' without 'local', and everything else is the same PREWHERE scan of search_text_lc with a single counter behind it. The median elapsed landed at 2.2 s on a loaded box against the probe's 0.47 s; rows read, not seconds, is the cost signal, and it moved by 2%.","measured":"2.24 s (median of three) over 20 months of hackernews.items, 6.1 M rows read, 5,656 hits (2026-09-07).","seconds":2.24,"readRows":6114831,"readBytes":2106322182,"relations":["hackernews.items"],"tags":["hasAllTokens","conjunction","index-anchor"]},{"slug":"the-actual-phrase","construct":"phrase-refine","parent":"both-words-present","rung":3,"axis":"qualifier","title":"Co-occurrence is not the phrase","problem":"5,656 documents contain both 'local' and 'llm'. Many of them are a local variable near an LLM, or a local election in a thread about models. The analyst wants the phrase 'local llm' as people write it, adjacent and in order. The token index stores no positions, so adjacency has to be checked on the raw text, and only after the index has done its narrowing.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND hasAllTokens(search_text_lc, ['local', 'llm'])\n  AND payload ILIKE '%local llm%' LIMIT 1\n","technique":"The residual predicate: payload ILIKE '%local llm%' runs only on rows the two token anchors already passed, and tests the thing the index cannot, order and adjacency. It has to be payload, the raw text column; the validator refuses LIKE over search_text_lc because an unanchored substring scan of the indexed column is the road to the timeout. Story payload carries the title, so titles are covered too. The phrase survives in 2,245 of the 5,656 co-occurrences: token conjunction overcounted the topic by 2.5x.","whyFast":"5.7 M rows read against the parent's 6.1 M, but 3.3 GB against 2.1 GB: the extra work is decompressing payload for granules that passed both token anchors and running a case-insensitive substring search over those bodies. That pass is bounded by the anchored set, never the window; that bound is why the residual sits behind the index. The rows-read dip is the engine's condition cache skipping granules where an earlier execution found no survivor; the first run of a residual this selective reads the parent's full set.","measured":"2.60 s (median of three) over 20 months of hackernews.items, 5.7 M rows read, 2,245 hits (2026-09-07).","seconds":2.6,"readRows":5699911,"readBytes":3327997357,"relations":["hackernews.items"],"tags":["ILIKE","phrase","residual-predicate"]},{"slug":"the-trailing-year","construct":"interval-window","parent":"selectivity-probe","rung":2,"axis":"scope","title":"A window that follows the calendar","problem":"The control window is fixed literals, which is right for a measurement that must reproduce. A saved check that an agent reruns every week means something else: the last twelve months as of today. The analyst wants to know how much of the twenty-month LLM discourse falls inside the trailing year, and wants that number to keep being true tomorrow.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND original_timestamp >= now() - INTERVAL 1 YEAR LIMIT 1\n","technique":"now() - INTERVAL 1 YEAR is a relative bound added beside the literal ones; the engine takes the intersection. This is a scope construct and it tightens the window: on 2026-09-07 the effective range is 2025-09-07 to 2026-08-31, holding 88,559 of the 131,838 hits, 67% of the discourse in the most recent 60% of the months. Rerun it next month and the range slides while the literal control stays put, so the count shrinks; that drift is the construct working, and it is why the fixed trunk uses literals. INTERVAL arithmetic composes: toStartOfMonth(now()) - INTERVAL 1 MONTH names the last full calendar month.","whyFast":"3.9 M rows and 1.3 GB read against the parent's 6.2 M and 2.1 GB: the relative bound folds to a constant before planning, so the primary key prunes the first eight months of granules exactly as a literal date would. Per surviving row the work is unchanged, one token test in PREWHERE and a counter.","measured":"1.08 s (median of three) over the trailing year inside the 20-month control on hackernews.items, 3.9 M rows read, 88,559 hits (2026-09-07).","seconds":1.08,"readRows":3893261,"readBytes":1319982165,"relations":["hackernews.items"],"tags":["INTERVAL","relative-time","recurring"]},{"slug":"about-not-mentioning","construct":"term-frequency","parent":"selectivity-probe","rung":2,"axis":"qualifier","title":"About LLMs, not just mentioning them","problem":"Membership is generous: a two-thousand-word comment that says LLM once in passing counts the same as an essay about them. Topical documents repeat their subject and drive-by mentions do not. The analyst wants the items that are about LLMs, and wants the ratio to the plain count, which is the corpus's name-drop rate for the term.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND countMatchesCaseInsensitive(search_text_lc, 'llm') >= 5 LIMIT 1\n","technique":"countMatchesCaseInsensitive(column, pattern) counts occurrences of a pattern in a string; gating it at k is Westlaw's ATLEASTn as one predicate. The pattern is a regular expression, so 'llm' also counts 'llms' and 'llm-based', which is the right behavior for aboutness. Five mentions leave 5,291 of 131,838 items, 4.0%; two leave 54,211, ten leave 387. Choose k by reading samples at each level: k=2 is a hint, k=5 is a discussion, k=10 is an essay.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent; the token index cannot see a count, so the granule set is identical and the difference is inside run-to-run variance. The added work is one regex pass over search_text_lc per anchored row, which the engine runs in the same PREWHERE step as the token test.","measured":"0.72 s (median of three) over 20 months of hackernews.items, 6.2 M rows read, 5,291 hits (2026-09-07).","seconds":0.72,"readRows":6235069,"readBytes":2148874673,"relations":["hackernews.items"],"tags":["frequency-gate","aboutness","ATLEASTn"]},{"slug":"k-of-n-vocabulary","construct":"quorum-gate","parent":"selectivity-probe","rung":2,"axis":"qualifier","title":"Three words from the agent vocabulary make a topic","problem":"The agentic-engineering conversation inside the LLM discourse has no single token. OR over its vocabulary matches everything near the topic; AND over it matches almost nothing. The professional-search middle is the quorum: at least k of n terms present. The analyst wants the LLM items where the agent stack is actually being engineered, not named.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND arrayCount(t -> has(['agent', 'tool', 'harness', 'sandbox', 'orchestration', 'subagent', 'eval'], t),\n                 arrayDistinct(tokens(search_text_lc))) >= 3 LIMIT 1\n","technique":"Sphinx's quorum operator as a residual: tokens() splits the document, arrayDistinct makes it a set, has() tests each token against the vocabulary, arrayCount tallies the hits, and the gate asks for three. The anchor stays in front so the tokenizing pass never runs on the window, only on the 131,838 anchored rows. Three of seven leaves 363 items; two leaves 2,711, four leaves 37. Drop 'agent' from the list and k=3 leaves 54, which says the vocabulary carries the topic as a set, not through one word.","whyFast":"The first execution of a fresh quorum predicate over this anchor reads the parent's full 6.2 M rows and tokenizes every anchored document once; measured on a variant vocabulary, that cold run took 4.0 s. The number recorded here is a repeated execution: 2.1 M rows and 701 MB, because ClickHouse's query condition cache remembers, per granule, that this predicate found nothing there and skips those granules on the next run. A gate this selective (363 of 131,838) leaves most granules empty, so the cache removes two-thirds of the scan. Per surviving row the work is a tokenize, a dedup and seven set tests.","measured":"0.22 s (median of three, repeated executions) over 20 months of hackernews.items, 2.1 M rows read; a first execution reads 6.2 M rows, 363 hits (2026-09-07).","seconds":0.22,"readRows":2061369,"readBytes":701237166,"relations":["hackernews.items"],"tags":["quorum","k-of-n","vocabulary-density"]},{"slug":"loudest-voices","construct":"top-k-sketch","parent":"selectivity-probe","rung":2,"axis":"statistic","title":"Who does the talking about LLMs, in one row","problem":"Before committing to a per-author histogram the analyst wants one glance: which handles dominate the LLM conversation on Hacker News? A GROUP BY over 131,838 items by author holds tens of thousands of groups to answer a question whose answer is eight names.","sql":"SELECT topK(8)(original_author) AS loudest_voices\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm') LIMIT 1\n","technique":"topK(n)(value) is an approximate heavy-hitters sketch (the SpaceSaving algorithm): one row, the n most frequent values, no GROUP BY. Under the control it returns simonw, Aurornis, ACCount37, tptacek, embedding-shape, TeMPOraL, simianwords and HarHarVeryFunny. The sketch is for orientation: two runs can swap adjacent names, and it carries no counts, so graduate to GROUP BY original_author when the numbers need to be quoted.","whyFast":"The same 6.2 M rows as the probe, plus original_author decompressed for the anchored granules, 2.3 GB against 2.1 GB. The sketch holds a fixed number of counters (a small multiple of eight) and updates one per row in a single streaming pass, so the state never grows with the number of distinct authors and nothing is sorted.","measured":"0.98 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.98,"readRows":6235069,"readBytes":2258872972,"relations":["hackernews.items"],"tags":["topK","sketch","orientation"]},{"slug":"talkers-versus-scorers","construct":"weighted-sketch","parent":"loudest-voices","rung":3,"axis":"statistic","title":"Who talks about LLMs versus who scores","problem":"topK ranks by volume, and volume rewards the most prolific commenters. The analyst wants the second list beside the first: the handles whose LLM items readers actually scored. The two lists together are the editorial judgment, and the point is the gap between them.","sql":"SELECT topK(8)(original_author) AS loudest_voices,\n       topKWeighted(8)(original_author, toUInt64(greatest(upvotes, 0))) AS most_rewarded\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm') LIMIT 1\n","technique":"topKWeighted(k)(value, weight) is topK where each row votes with a weight instead of one; greatest(upvotes, 0) floors negative scores and toUInt64 gives the sketch the integer it requires. Comments carry NULL upvotes in this export, and aggregates skip NULL arguments, so the weighted list is drawn from the 11,438 scored items while the plain list covers all 131,838. That asymmetry is the finding: the loudest voices are commenters (simonw, Aurornis, tptacek); the most rewarded are submitters (SwoopsFromAbove, gpjt, vuciv), and only embedding-shape appears on both lists.","whyFast":"The same 6.2 M rows as the parent, with upvotes decompressed beside original_author, 2.3 GB against 2.3 GB; the difference is inside run-to-run variance. Both sketches run in the one streaming pass, and the weighted one costs a multiply and the same fixed counter table per row.","measured":"0.88 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.88,"readRows":6235069,"readBytes":2290051281,"relations":["hackernews.items"],"tags":["topKWeighted","sketch","engagement"]},{"slug":"title-versus-body","construct":"significance-test","parent":"selectivity-probe","rung":2,"axis":"statistic","title":"Does putting LLM in the title change the score","problem":"Stories that name LLMs in the title look like they score about the same as stories that mention them only in the body, and eyeballed sameness has fooled everyone forever. Scores are power-law noise. The honest question is whether the two populations differ under a rank test, and that test can run inside the query.","sql":"SELECT mannWhitneyUTest(toFloat64(upvotes), if(positionCaseInsensitive(title, 'llm') > 0, 0, 1)) AS title_vs_body\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm') LIMIT 1\n","technique":"mannWhitneyUTest(value, group) returns (u_statistic, p_value) for the rank-sum test; nonparametric, so power-law scores do not break it the way a t-test's normality would. The group is any 0/1 expression: positionCaseInsensitive on the raw title splits the 7,548 title-mention items from the 3,890 body-only ones, and comments drop out on their NULL upvotes with no type filter. Result: p = 3.4e-5. The means are 9.1 and 9.0 and both medians are 2; the difference is in the upper tail, where body-only stories reach a 90th percentile of 8 points against 6. With 11,438 samples the test resolves a shift the summary statistics cannot show, which is exactly why it belongs in the query.","whyFast":"The same 6.2 M rows as the probe, with title and upvotes decompressed for the anchored granules, 2.2 GB against 2.1 GB. The test accumulates the two samples in one streaming pass and ranks them once at the end, 11,438 values, so the added work is one substring test per row and a single sort of the scored items.","measured":"0.96 s (median of three) over 20 months of hackernews.items, 6.2 M rows read, p = 3.4e-5 (2026-09-07).","seconds":0.96,"readRows":6235069,"readBytes":2214735862,"relations":["hackernews.items"],"tags":["mannWhitneyUTest","significance","p-value"]},{"slug":"shape-of-the-scores","construct":"adaptive-histogram","parent":"selectivity-probe","rung":2,"axis":"statistic","title":"The shape of LLM story scores without choosing bins","problem":"A bucketed histogram needs a bin width, and on an unfamiliar distribution the wrong guess hides the shape. The analyst wants to see how LLM story scores are distributed, from a one-point Show HN to a 1,773-point essay, before deciding where the interesting range is.","sql":"SELECT histogram(8)(toFloat64(upvotes)) AS upvote_shape\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm') LIMIT 1\n","technique":"histogram(n)(x) returns [(lo, hi, height)] tuples with data-chosen edges. Under the control the first bin, 1 to 64 points, holds 9,747 of the 11,438 scored items; 64 to 189 holds 1,568; the six bins above that hold 122 between them, up to the single story at 1,773. The power law announces itself with no binning decision. NULL upvotes on comments are skipped, so this is the story distribution without a type filter. Heights are streaming-merge estimates and arrive fractional; read the shape, then graduate to intDiv buckets once you know where to look.","whyFast":"The same 6.2 M rows as the probe with upvotes decompressed alongside, 2.2 GB against 2.1 GB. The sketch keeps a fixed set of bins and merges a point into one of them per row: one pass, constant memory, no sort of the 11,438 values.","measured":"0.73 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.73,"readRows":6235069,"readBytes":2180050866,"relations":["hackernews.items"],"tags":["histogram","adaptive","distribution"]},{"slug":"read-the-winners","construct":"row-retrieve","parent":"selectivity-probe","rung":2,"axis":"shape","title":"The twenty LLM items Hacker News scored highest","problem":"The count says 131,838 items mention LLMs. That is a size, not a reading list. The analyst's next question is what the community rewarded most inside that set: the actual titles, in score order, on one page. A count cannot show a single title; retrieval is the first step that puts text in front of a reader.","sql":"SELECT title, upvotes\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nORDER BY upvotes DESC LIMIT 20\n","technique":"This is the count-to-rows transition, the one place in the tree where the projection is replaced rather than extended: count() gives way to the raw text column and its score, and ORDER BY upvotes DESC LIMIT 20 chooses which twenty of the 131,838 to show. The control lines are byte-identical to the probe. Comments carry NULL upvotes in this export and sort last, so the page is stories; the schema warns that scores are first-fetch snapshots, so treat the order as a strong signal, not a leaderboard. A text column named at the top level of the outermost SELECT is what marks an entry as retrieval; any(title) inside an aggregate does not.","whyFast":"3.9 M rows and 1.1 GB read, against the probe's 6.2 M and 2.1 GB. The anchor still runs in PREWHERE over search_text_lc, and title is decompressed only for granules that hold survivors. ORDER BY with a LIMIT keeps a bounded top-20 while blocks stream past it instead of sorting the 131,838-row match set, so the ordering costs a comparison per row and twenty rows of state. The page is twenty titles; the work is the anchored scan plus that heap.","measured":"0.76 s (median of three) over 20 months of hackernews.items, 3.9 M rows read (2026-09-07).","seconds":0.76,"readRows":3877348,"readBytes":1105792137,"relations":["hackernews.items"],"tags":["retrieval","ORDER-BY-LIMIT","count-to-rows"]},{"slug":"best-of-each-kind","construct":"limit-by","parent":"read-the-winners","rung":3,"axis":"shape","title":"The top two LLM items of every kind","problem":"The global top twenty is all stories, because stories are where scores live. The analyst wants the best of each kind on one page: the two highest-scored stories, the two polls, the two job posts that mention LLMs. That is a different operation from a global cutoff, and most engines make you build window machinery for it.","sql":"SELECT hn_type, title, upvotes\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nORDER BY upvotes DESC LIMIT 2 BY hn_type\n","technique":"LIMIT k BY key is ClickHouse's native group-wise top-k over raw rows: order first, then keep k per key. It replaces row_number() OVER (PARTITION BY hn_type) plus a subquery with one clause. The projection adds hn_type so the reader can see which key each row belongs to; the parent's trailing LIMIT 20 is dropped because the API's parser refuses a second LIMIT after LIMIT BY, and with five hn_type values the per-key cap already bounds the page at ten rows. The result is nine: two stories, two polls, two jobs, one pollopt and two comments, whose NULL upvotes and empty titles show on the page exactly as the export holds them.","whyFast":"6.2 M rows and 2.3 GB read against the parent's 3.9 M and 1.1 GB. A global LIMIT 20 can keep a bounded heap; a per-key cut needs the ordered match set before it knows which two rows each hn_type keeps, so the anchored granules are read in full, hn_type is decompressed beside title and upvotes, and the 131,838 survivors are sorted once before the cut streams past them. State beyond the sort is two rows per key.","measured":"0.60 s (median of three) over 20 months of hackernews.items, 6.2 M rows read, 9 rows (2026-09-07).","seconds":0.6,"readRows":6235069,"readBytes":2313344338,"relations":["hackernews.items"],"tags":["LIMIT-BY","top-k-per-group","fair-page"]},{"slug":"story-or-comment","construct":"group-by","parent":"selectivity-probe","rung":2,"axis":"shape","title":"One count becomes a count per kind","problem":"The probe says 131,838 documents mention LLMs in twenty months. It cannot say whether that is a corpus of submissions or of arguments under them, and the two read differently: a story is a claim, a comment is a reaction. The first pivot any analyst makes is from a total to a total per record kind.","sql":"SELECT hn_type, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY hn_type ORDER BY n DESC LIMIT 10\n","technique":"GROUP BY turns the probe's single count into one count per distinct key; ORDER BY n DESC LIMIT 10 ranks the groups. The answer is five rows: 120,400 comments, 11,424 stories, 11 jobs, 2 polls, 1 poll option. Nine in ten LLM documents on Hacker News are reactions, not submissions. Every child under this node keeps the count and the ranking idiom and swaps only the key.","whyFast":"The same 6.2 M rows as the probe, one more column: 2.25 GB against 2.15 GB, the difference being hn_type. Per row the engine hashes a low-cardinality key into an aggregate state that holds five groups; sorting five rows is nothing. The 0.89 s against the probe's 0.47 s is inside the spread this table shows between runs of identical text.","measured":"0.89 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.89,"readRows":6235069,"readBytes":2247529446,"relations":["hackernews.items"],"tags":["GROUP-BY","distribution","record-kind"]},{"slug":"the-llm-regulars","construct":"having","parent":"story-or-comment","rung":3,"axis":"qualifier","title":"Filter the groups, not the rows","problem":"WHERE sees rows; a regular is defined on the aggregate: an author with at least thirty LLM posts in the window. That predicate exists only after grouping, and it is the cohort every subsequent regulars analysis starts from.","sql":"SELECT original_author, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY original_author\nHAVING n >= 30\nORDER BY n DESC LIMIT 10\n","technique":"HAVING runs after aggregation and sees aggregates by alias. The gate turns 35,079 authors into 553 regulars, and those 553 wrote 32,396 of the 131,838 matching items, a quarter of the LLM conversation from 1.6 percent of its voices. This is the doorway to the quantifier family: at-least-k, never, and present-in-all are all HAVING gates over grouped state, and the two children of this entry add exactly one each.","whyFast":"The same 6,235,069 rows and 2.26 GB as a per-author group-by; the gate runs over the 35,079 grouped rows after the scan, one integer comparison each, and shrinks the sort to 553 rows. The three runs spread from 1.16 to 2.08 s under production load; rows read is the stable cost and it equals the parent's.","measured":"1.21 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.21,"readRows":6235069,"readBytes":2258872972,"relations":["hackernews.items"],"tags":["HAVING","cohort-gate","regulars","quantifier"]},{"slug":"regulars-who-never-say-agent","construct":"anti-quantifier","parent":"the-llm-regulars","rung":4,"axis":"qualifier","title":"Absence as a result set","problem":"Who talks about LLMs at volume and never once says agent? Never is a claim about every row an author wrote, universally quantified absence, and no row-level filter can state it. Typed as an aggregate over the group it is one more term in the gate.","sql":"SELECT original_author, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY original_author\nHAVING n >= 30 AND max(hasToken(search_text_lc, 'agent')) = 0\nORDER BY n DESC LIMIT 10\n","technique":"max(P) = 0 in HAVING types never-P: over a group of 0/1 predicate values the maximum is zero exactly when no row satisfies P. The floor of thirty posts keeps the cohort meaningful; without it the never-set fills with one-post drive-bys. 166 of the 553 regulars never say agent, led by latexr (139 LLM posts) and crazygringo (136). Never is downward-monotone as ingest continues, so state it over a closed window, as here. max alone reads as this construct; adding min beside it would read as span-derive.","whyFast":"The same 6,235,069 rows and 2.26 GB as the parent. The addition is one token test per matched row, folded into a one-byte max per group, and one more comparison at gate time over 35,079 groups; nothing is rescanned to establish absence. The three runs spread from 0.43 to 2.22 s under production load; rows read is the stable cost and it equals the parent's.","measured":"0.73 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.73,"readRows":6235069,"readBytes":2256578489,"relations":["hackernews.items"],"tags":["never","universal-absence","max-equals-zero","HAVING"]},{"slug":"submit-and-comment","construct":"division","parent":"the-llm-regulars","rung":4,"axis":"qualifier","title":"Present in every venue: relational division","problem":"Which regulars both submit LLM stories and argue in the comments? Present-in-all is relational division, the query textbooks solve with a double NOT EXISTS. Over grouped rows it is a distinct count under a condition, compared to the size of the required set.","sql":"SELECT original_author, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY original_author\nHAVING n >= 30 AND uniqIf(hn_type, hn_type IN ('story', 'comment')) = 2\nORDER BY n DESC LIMIT 10\n","technique":"uniqIf(value, condition) counts distinct values among the rows that pass the condition; requiring it to equal the size of the required set is the universal quantifier. Two is the full set here (story and comment), so the gate keeps authors present in both. 121 of the 553 regulars qualify; simonw leads at 719, and the ranking shifts visibly against the plain regulars list (fragmede and embedding-shape rise, Aurornis and TeMPOraL drop out as comment-only voices). Loosen = k to >= k for present-in-at-least-k. count(DISTINCT if(cond, value, NULL)) is the exact spelling of the same test.","whyFast":"The same 6,235,069 rows and 2.36 GB as a per-author group-by that reads hn_type. The state is one small distinct sketch per group over at most four values, and the gate is one equality over 35,079 groups; there is no second relation and no per-author subquery. The three runs (2.34 to 2.72 s) landed in a loaded interval; rows read is the stable cost and it equals the parent's.","measured":"2.59 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":2.59,"readRows":6235069,"readBytes":2357531448,"relations":["hackernews.items"],"tags":["division","uniqIf","present-in-all","universal-quantifier"]},{"slug":"points-curve","construct":"value-buckets","parent":"story-or-comment","rung":3,"axis":"derivation","title":"Bin the score, then count the bins","problem":"How high do LLM stories climb? GROUP BY upvotes gives one group per distinct number, a list nobody can read. The shape of attention appears only when the score is folded into bins of a hundred first. Comments carry no score on this table, so the question is asked of stories.","sql":"SELECT intDiv(upvotes, 100) * 100 AS bucket, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND hn_type = 'story'\nGROUP BY bucket ORDER BY n DESC LIMIT 10\n","technique":"intDiv(x, 100) * 100 floors each score to its bin; the bin is the group key. The added predicate hn_type = 'story' is a plain filter, not a construct: without it the top group is NULL, because all 120,400 comments have no score. Of 11,424 LLM stories, 11,169 sit at 0-99 points, 149 at 100-199, 48 at 200-299, 21 at 300-399, then 16, 10, 5, 3, 2 and a single story above 1,700. Only 255 stories cross 100 points. Ordering by n reads the attention curve top-down; ordering by bucket would read it left-to-right.","whyFast":"6.20 M rows against the parent's 6.24 M; the story predicate trims 0.5 % and the rest is the same scan. Bytes rise to 2.27 GB because upvotes is now read alongside hn_type. Per row one integer division and a hash into a state holding ten groups, the whole histogram in one pass.","measured":"0.58 s (median of three) over 20 months of hackernews.items stories, 6.2 M rows read (2026-09-07).","seconds":0.58,"readRows":6200589,"readBytes":2266068063,"relations":["hackernews.items"],"tags":["histogram","intDiv","binning"]},{"slug":"name-the-eras","construct":"case-classify","parent":"story-or-comment","rung":3,"axis":"derivation","title":"Group by your own periodization","problem":"Calendar months are arbitrary where history is not. The boundaries an LLM analyst cares about are events: before ChatGPT, the year after it, the model race of 2024, and the twenty months this tree measures. A GROUP BY over eras you define needs the dimension built inline from the timestamp.","sql":"SELECT multiIf(original_timestamp < '2022-11-30', 'before-chatgpt',\n               original_timestamp < '2023-11-30', 'chatgpt-year',\n               original_timestamp < '2025-01-01', 'model-race',\n               'control-window') AS era, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2020-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY era ORDER BY n DESC LIMIT 10\n","technique":"multiIf is the inline classifier: conditions read top-down, the first true one names the row, the last argument is the default. The eras compare original_timestamp against date literals, never a calendar function, so the derived key is the classification alone. This is the one node in the slice that widens the window: the control starts in 2025 and has no pre-ChatGPT era to classify, so the start moves to 2020-01-01 with the end and the term unchanged. Four groups over 191,965 documents: 170 before ChatGPT, 23,958 in the ChatGPT year, 35,999 in the model race, 131,838 in the control window. The last row reproduces the trunk's count exactly, which is the check that the classifier and the control agree.","whyFast":"15.1 M rows and 5.27 GB, 2.4x the parent's 6.2 M rows, because the window is 80 months instead of 20; the wider anchor also reads 1.46x the documents. Per row a short chain of date comparisons and a hash into a state holding four groups. The 0.37 s median against a 2.55 s cold first run of the same text says the cost is I/O warmth, not the classifier.","measured":"0.37 s (median of three) over hackernews.items from 2020-01 to 2026-08, the window widened from the control's 2025 start so that a pre-ChatGPT era exists; 15.1 M rows read (2026-09-07).","seconds":0.37,"readRows":15083357,"readBytes":5266509865,"relations":["hackernews.items"],"tags":["multiIf","periodization","inline-dimension"]},{"slug":"which-gpt","construct":"regex-extract","parent":"story-or-comment","rung":3,"axis":"derivation","title":"The version number is inside the prose","problem":"Nobody tagged which GPT generation a comment argues about; the version lives in the text as gpt-4o, gpt 5, gpt3.5. A capture group lifts it out and makes it a group key, so the LLM discourse can be counted by the model it names.","sql":"SELECT regexpExtract(search_text_lc, 'gpt[- ]?([0-9]+(?:\\\\.[0-9]+)?o?)', 1) AS version, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY version ORDER BY n DESC LIMIT 10\n","technique":"regexpExtract(text, pattern, 1) returns the first capture group of the first match, or the empty string. The pattern accepts a hyphen, a space or nothing between gpt and the number, a dotted minor version, and a trailing o. The empty match is kept as its own group rather than filtered: it is the denominator. 129,751 of 131,838 LLM documents never name a GPT version; among the 2,087 that do, 5 leads with 445, then 4o at 367, 4 at 336, 3 at 156, 2 at 150, 3.5 at 108, 5.2 at 100, 5.5 at 92, 4.1 at 72. Forty-four distinct strings in all. The backslash before the dot is doubled in the SQL text because ClickHouse unescapes string literals once.","whyFast":"The same 6.2 M rows as the parent and fewer bytes, 2.15 GB against 2.25 GB: the key comes from search_text_lc, which the anchor already reads, so hn_type stays on disk. The work moved into the row: one regular-expression scan of every matched document, up to the first hit, then a hash into a state holding 44 groups. That per-row scan is why this is the slowest node in the slice at 1.33 s with all three runs between 1.18 and 1.34 s.","measured":"1.33 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.33,"readRows":6235069,"readBytes":2146695920,"relations":["hackernews.items"],"tags":["regexpExtract","capture-group","latent-dimension"]},{"slug":"vendor-breadth","construct":"extract-all","parent":"story-or-comment","rung":3,"axis":"derivation","title":"Count every match, then group by the count","problem":"The first match answers which model a document names. Some questions are about multiplicity: how many LLM discussions put two or more vendors side by side, and how many mention one in passing? That needs every match per document, deduplicated and counted, before the GROUP BY.","sql":"SELECT length(arrayDistinct(extractAll(search_text_lc, '\\\\b(?:gpt|claude|gemini|llama|mistral|deepseek|qwen|grok)\\\\b'))) AS families, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY families ORDER BY n DESC LIMIT 10\n","technique":"extractAll returns every match of the pattern as an array; arrayDistinct drops repeats, length turns the array into a breadth measure, and that integer is the group key. Eight model families, word-bounded so llama does not fire on llamas-of-text noise. 117,562 documents name none of them, 11,658 name exactly one, 1,937 name two, 490 three, 151 four, then 29, 8, 2 and a single document that names all eight. Cross-vendor comparison is 2,618 of 131,838 LLM documents, one in fifty.","whyFast":"The same 6.2 M rows and the same 2.15 GB as regex-extract: the key is again derived from the anchored text column alone. Per row the regex now runs to the end of the document instead of stopping at the first hit, then a small array is deduplicated and measured. Nine groups held. At 0.62 s against regex-extract's 1.33 s, the difference is inside the run-to-run spread of this table, not evidence that scanning to the end is free.","measured":"0.62 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.62,"readRows":6235069,"readBytes":2146695920,"relations":["hackernews.items"],"tags":["extractAll","multiplicity","breadth"]},{"slug":"where-links-point","construct":"domain-extract","parent":"story-or-comment","rung":3,"axis":"derivation","title":"Follow the outbound links to their hosts","problem":"What a community discusses and what it links are different maps. Submission URLs carry the second one: which sources feed the LLM conversation. Raw URLs are too granular to count, so the host has to be derived from each one before the GROUP BY.","sql":"SELECT domain(outbound_url) AS site, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND outbound_url != ''\nGROUP BY site ORDER BY n DESC LIMIT 10\n","technique":"domain() parses the host out of a URL; the host is the group key. The plain predicate outbound_url != '' restricts the count to the 9,390 linked items, 9,382 of them stories. Top of the evidence diet: github.com 3,014, arxiv.org 442, twitter.com 109, www.youtube.com 101, medium.com 91. A third of every LLM link on Hacker News points at a repository. The www prefix survives because domain() keeps it; cutToFirstSignificantSubdomain would fold it.","whyFast":"6.22 M rows against the parent's 6.24 M; the link predicate trims 0.2 % and the rest is the same scan. 2.23 GB, outbound_url read in place of hn_type. Per row one URL parse, and the aggregate state holds 3,967 distinct hosts, the first node in the slice where the group count is in the thousands; ten of them are sorted out at the end.","measured":"1.00 s (median of three) over 20 months of hackernews.items with a link, 6.2 M rows read (2026-09-07).","seconds":1.0,"readRows":6221789,"readBytes":2233873166,"relations":["hackernews.items"],"tags":["domain","outbound-links","sources"]},{"slug":"what-kind-of-source","construct":"top-level-domain","parent":"story-or-comment","rung":3,"axis":"derivation","title":"The suffix is a register signal","problem":"The host answers which sites; the suffix answers what kind of source a conversation cites. An .ai-heavy link profile and an .org-heavy one are different registers of evidence, and the suffix is a far smaller key than the host.","sql":"SELECT topLevelDomain(outbound_url) AS tld, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND outbound_url != ''\nGROUP BY tld ORDER BY n DESC LIMIT 10\n","technique":"topLevelDomain() strips a URL to its suffix, and the suffix is the group key over the same 9,390 linked items. com 5,865, org 763, ai 571, io 474, dev 309. Six in ten LLM links are .com; .ai alone outnumbers .io, and the .org share is largely arxiv. This node sits beside domain-extract, not under it: both derive a key from outbound_url and the detector keys on the function name, so the SQL contains topLevelDomain and never the bare lowercase domain call.","whyFast":"Identical rows and bytes to domain-extract, 6.22 M rows and 2.23 GB: the same predicate, the same URL column. The aggregate state shrinks from 3,967 hosts to 184 suffixes. The 1.46 s median with runs of 0.66, 1.81 and 1.46 s is the box's spread; the smaller state is not visible at this scale.","measured":"1.46 s (median of three) over 20 months of hackernews.items with a link, 6.2 M rows read (2026-09-07).","seconds":1.46,"readRows":6221789,"readBytes":2233873166,"relations":["hackernews.items"],"tags":["topLevelDomain","links","register"]},{"slug":"the-reposts","construct":"sim-hash","parent":"story-or-comment","rung":3,"axis":"derivation","title":"Fold near-identical stories by fingerprint","problem":"Reposts and mirrors inflate a topic's apparent size. Exact-string GROUP BY misses them, one edited character breaks the group, and pairwise comparison across 11,424 stories is not a query anyone runs. A locality-sensitive fingerprint as the group key finds the clusters in one pass.","sql":"SELECT ngramSimHash(search_text_lc) AS fingerprint, count() AS copies, any(title) AS specimen\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND hn_type = 'story'\nGROUP BY fingerprint ORDER BY copies DESC LIMIT 10\n","technique":"ngramSimHash folds a text into a 64-bit fingerprint on which near-identical texts collide, so a plain GROUP BY becomes a near-duplicate detector. count() is named copies; any(title) picks one specimen per cluster and stays inside an aggregate, so the projection is not row retrieval. The story predicate keeps the specimens readable. 11,424 LLM stories fold to 11,036 fingerprints: 304 clusters hold more than one copy, 388 stories are surplus. The largest cluster is six submissions of 'Stealing Reasoning Traces from Proprietary LLM APIs'; three clusters of five follow.","whyFast":"6.20 M rows against the parent's 6.24 M, the story predicate trimming 0.5 %; 2.27 GB with title read for the specimen. Per row the engine hashes the document's n-grams into one 64-bit value and hashes that into a state holding 11,036 groups, plus one title kept per group. No pair of stories is ever compared; that is why this runs in 0.84 s on the same scan as the parent.","measured":"0.84 s (median of three) over 20 months of hackernews.items stories, 6.2 M rows read (2026-09-07).","seconds":0.84,"readRows":6202285,"readBytes":2267590928,"relations":["hackernews.items"],"tags":["ngramSimHash","near-duplicates","fingerprint"]},{"slug":"month-by-month","construct":"time-bucket","parent":"story-or-comment","rung":3,"axis":"derivation","title":"The same count, laid along the calendar","problem":"Grouping by kind says what the LLM discourse is made of; it says nothing about when. An analyst wants the curve: did interest climb, plateau, or spike with a release? That needs a group key derived from the timestamp, one bucket per month.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"toStartOfMonth folds every timestamp to the first of its month, and that derived value is the GROUP BY key; ORDER BY m ASC reads as a series. Twenty closed months: 3,942 in January 2025, 9,907 at the March 2026 peak, 8,546 in August 2026. The window is closed on both ends on purpose; a partial month reads as a collapse next to finished ones. Any calendar function (toStartOfWeek, toYear) is the same move at a different grain.","whyFast":"The same 6.2 M rows and, to the byte, the same 2.15 GB as the probe: the key is computed from original_timestamp, a column the WHERE already reads, so the month costs no extra I/O. Per row one date truncation and one hash into a state holding twenty groups. The 0.50 s is the probe's time; a second look-run of the identical text took 2.0 s on the same rows, which is the variance of a shared box, not the work.","measured":"0.50 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.5,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["time-series","toStartOfMonth","trend"]},{"slug":"tone-of-llm-talk","construct":"lexicon-score","parent":"month-by-month","rung":4,"axis":"derivation","title":"Net tone of LLM threads, month by month","problem":"Volume climbed from 3,942 to 8,546 items a month. Did the mood move with it? You want a per-month tone score computed from the text of every matching document with a small fixed lexicon, not the count of one word.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n,\n  round(avg(arraySum(t -> transform(t,\n    ['impressive', 'useful', 'helpful', 'excellent', 'amazing', 'love', 'great', 'brilliant',\n     'hype', 'garbage', 'useless', 'terrible', 'slop', 'hallucinations', 'worthless', 'disappointing'],\n    [1, 1, 1, 1, 1, 1, 1, 1, -1, -1, -1, -1, -1, -1, -1, -1], 0), tokens(search_text_lc))), 3) AS tone\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"tokens(search_text_lc) splits the indexed text into words; transform(t, words, scores, 0) maps each word to +1, -1 or 0; arraySum with a lambda totals one document; avg per month gives the mean net score. Sixteen words: eight approving (impressive, useful, helpful, excellent, amazing, love, great, brilliant) and eight dismissive (hype, garbage, useless, terrible, slop, hallucinations, worthless, disappointing). Use arraySum or arrayMap, never arrayJoin, which expands rows and fires array-expand. Tone stays net-positive across the window and halves: 0.137 in January 2025, 0.063 in July 2026, while volume doubles.","whyFast":"The same 6.2 M rows as the parent and the same 2.1 GB: search_text_lc is already read to evaluate hasToken, so tokenising it is CPU over bytes already in memory, not I/O. Per matching document, one tokenisation and one transform lookup per token, across 131,838 documents into twenty groups. Elapsed 0.78 s against the parent's 0.48 s and 0.94 s in adjacent triples is inside variance.","measured":"0.78 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.78,"readRows":6235069,"readBytes":2146695920,"relations":["hackernews.items"],"tags":["derivation","lexicon","sentiment"]},{"slug":"no-missing-months","construct":"gap-fill","parent":"month-by-month","rung":4,"axis":"shape","title":"A calendar axis that cannot skip a month","problem":"GROUP BY emits only the months that have rows. When a term goes quiet for a month, or the archive has a hole, the series silently shortens and every downstream chart or month-over-month delta reports the wrong neighbours. You want the twenty-month axis guaranteed, with an explicit zero wherever nothing matched.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m ORDER BY m ASC WITH FILL FROM toDate('2025-01-01') TO toDate('2026-09-01') STEP INTERVAL 1 MONTH LIMIT 20\n","technique":"ORDER BY m ASC WITH FILL FROM a TO b STEP INTERVAL 1 MONTH inserts a row for every step between the bounds that the aggregation did not produce, with n defaulted to 0. The bare WITH FILL form only spans between the first and last observed rows, so a silent first or last month would stay absent; FROM and TO pin the axis to the control window, and TO is exclusive, matching the < '2026-09-01' bound. The trunk series is dense, so the fill inserts nothing here: what the query buys is the guarantee. The detector's named-CTE scan skips the WITH in WITH FILL.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. Filling walks the twenty sorted rows after aggregation and compares each neighbour pair with the step; on a dense series that is twenty comparisons and no inserts. This triple measured 2.34 s where the parent measured 0.48 s minutes earlier on identical scan work: production load, not the fill.","measured":"2.34 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":2.34,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["shape","with-fill","time-series"]},{"slug":"share-of-the-window","construct":"window","parent":"month-by-month","rung":4,"axis":"shape","title":"Each month's share of the twenty","problem":"Raw monthly counts do not say how concentrated the discourse is. You want each month as a percentage of the whole window, so March 2026's 9,907 reads as 7.5 % of two years of LLM talk and February 2025's 3,889 as 2.9 %, without a second query for the total.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n, round(100 * n / sum(n) OVER (), 1) AS share_pct\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"sum(n) OVER () is a window with an empty specification: it sums the aggregated n across every result row and repeats that total on each row, so 100 * n / sum(n) OVER () is the month's share. ClickHouse evaluates window functions after GROUP BY inside the same SELECT, so no subquery is needed and the detector's FROM ( scan stays quiet. The window runs before LIMIT, so the denominator is all twenty months.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. The window pass touches twenty aggregated rows: one sum and twenty divisions. Elapsed 0.34 s against the parent's 0.48 s in adjacent triples is inside run-to-run variance.","measured":"0.34 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.34,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["shape","window","share"]},{"slug":"cumulative-llm-talk","construct":"running-total","parent":"share-of-the-window","rung":5,"axis":"shape","title":"The running total, month by month","problem":"How much of the two-year conversation had happened by a given month? The share column says what each month contributed; you want the accumulation, to see when the window crossed half its volume and how steep the tail is.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n, round(100 * n / sum(n) OVER (), 1) AS share_pct,\n  sum(n) OVER (ORDER BY m ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"sum(n) OVER (ORDER BY m ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sums n over every row from the first month through the current one, in month order. The explicit ROWS frame is what makes it a prefix sum, and it is what the detector keys on. The parent's share column is retained unchanged, so an unframed window and a framed window sit in the same SELECT. The final row equals the window total, 131,838.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. The framed window orders twenty rows by m and carries one running accumulator; the share window is unchanged. Elapsed 1.29 s here against 0.34 s for the parent in this session's triples is run-to-run variance on identical scan work, not the frame.","measured":"1.29 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.29,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["shape","window","cumulative"]},{"slug":"month-over-month-swing","construct":"neighbor-delta","parent":"share-of-the-window","rung":5,"axis":"shape","title":"Month-over-month change in one column","problem":"Which months broke from their neighbours? The share column places each month against the whole; you want the local move, the +1,087 into January 2026 and the -2,278 off the March peak, without self-joining the series on m.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n, round(100 * n / sum(n) OVER (), 1) AS share_pct,\n  n - lagInFrame(n, 1) OVER (ORDER BY m ASC) AS delta\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"lagInFrame(n, 1) OVER (ORDER BY m ASC) returns the previous row's n inside the default frame, which runs from the partition start to the current row, so n - lagInFrame(n, 1) is the month-over-month delta. No explicit ROWS BETWEEN is needed, and the text must not contain UNBOUNDED PRECEDING, which is the running-total detector. With no prior row lagInFrame returns 0, so the first month's delta equals its n; a third argument to lagInFrame sets a different default. The parent's share column is retained.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. The lag window orders twenty rows and reads one row back per row. Elapsed 0.24 s, the lowest in the branch, on scan work identical to the parent's 0.34 s and 0.48 s: variance, not the window.","measured":"0.24 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.24,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["shape","window","delta"]},{"slug":"average-month-and-peak","construct":"subquery","parent":"month-by-month","rung":4,"axis":"composition","title":"Average month and peak month, one row","problem":"Twenty rows is a series; sometimes you want two numbers. What is the typical monthly volume of LLM talk on HN, and what is its ceiling? Summarising a grouped result is an aggregate over aggregates, which SQL only allows through a nested query.","sql":"SELECT round(avg(n), 1) AS avg_per_month, max(n) AS peak_month\nFROM (\n  SELECT toStartOfMonth(original_timestamp) AS m, count() AS n\n  FROM hackernews.items\n  WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n    AND hasToken(search_text_lc, 'llm')\n  GROUP BY m ORDER BY m ASC LIMIT 20\n) LIMIT 1\n","technique":"FROM ( ) wraps the parent verbatim, ORDER BY and LIMIT included, and the outer SELECT aggregates its twenty rows: avg(n) for the typical month, max(n) for the peak. The detector fires subquery on FROM (. max( on its own stays clear of span-derive, which needs min( beside it, and argMax( would fire group-first. One row: 6,591.9 items in the average month, 9,907 at the March 2026 peak.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. The outer aggregate consumes twenty rows: one average, one maximum, one row out. Elapsed 1.17 s against the parent's 0.48 s and 0.94 s in adjacent triples is inside variance for identical scan work.","measured":"1.17 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.17,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["composition","subquery","summary"]},{"slug":"llm-talk-growth-rate","construct":"trend-fit","parent":"average-month-and-peak","rung":5,"axis":"statistic","title":"The linear growth rate of LLM talk","problem":"Average and peak say where the series sits, not how fast it climbs. You want a slope in items per month per month, fitted to all twenty points, with its intercept, so the trend is one number you can state and extrapolate.","sql":"SELECT round(avg(n), 1) AS avg_per_month, max(n) AS peak_month,\n  simpleLinearRegression(toFloat64(dateDiff('month', toDate('2025-01-01'), m)), toFloat64(n)) AS fit,\n  round(tupleElement(fit, 1), 1) AS slope_per_month, round(tupleElement(fit, 2), 1) AS intercept\nFROM (\n  SELECT toStartOfMonth(original_timestamp) AS m, count() AS n\n  FROM hackernews.items\n  WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n    AND hasToken(search_text_lc, 'llm')\n  GROUP BY m ORDER BY m ASC LIMIT 20\n) LIMIT 1\n","technique":"simpleLinearRegression(x, y) fits y = k * x + b across the group and returns the (k, b) tuple. x is the month index, dateDiff('month', toDate('2025-01-01'), m), so k reads as items per month per month; tupleElement(fit, 1) and tupleElement(fit, 2) surface k and b as rounded columns. Write tupleElement, not fit.1: the API's SQL parser refuses the dotted tuple syntax. avg_per_month and peak_month stay in the outer SELECT. Slope 261.5, intercept 4,107.9: the fit puts August 2026 at 9,076 against an observed 8,546.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. The regression is a running-sums state over twenty (x, y) pairs: five accumulators and one solve. Elapsed 0.27 s against the subquery parent's 1.17 s in this session is variance on identical scan work.","measured":"0.27 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.27,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["statistic","regression","trend"]},{"slug":"who-kept-talking","construct":"cohort-retention","parent":"average-month-and-peak","rung":5,"axis":"statistic","title":"Authors from 2025 still talking in 2026","problem":"Volume doubled. Is it the same people talking more, or new people arriving? Take everyone who wrote an LLM-matching item in 2025 and ask how many wrote another in the first eight months of 2026. Monthly counts cannot see identity; a per-author pass can.","sql":"SELECT sum(r[1]) AS active_2025, sum(r[2]) AS still_active_2026,\n  round(100 * sum(r[2]) / sum(r[1]), 1) AS retained_pct\nFROM (\n  SELECT original_author,\n    retention(toYear(original_timestamp) = 2025, toYear(original_timestamp) = 2026) AS r\n  FROM hackernews.items\n  WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n    AND hasToken(search_text_lc, 'llm')\n  GROUP BY original_author\n) LIMIT 1\n","technique":"retention(cond1, cond2) aggregates each author's rows into flags: r[1] is 1 if any row met cond1, r[2] is 1 only if cond1 and cond2 both held. The conditions are calendar tests, toYear(original_timestamp) = 2025 and = 2026, which is where time-bucket fires. The inner query keeps the control lines and groups by original_author; the outer sums the flags across authors. The 2026 leg covers January through August, so the 39.0 % is an eight-month return rate against a twelve-month base: 20,542 authors in 2025, 8,007 of them back in 2026.","whyFast":"The same 6.2 M rows as the parent, plus the author column: 2.26 GB against 2.1 GB. Aggregation holds one two-flag state per author, about 35 K states instead of twenty month buckets, and the outer pass sums them into three numbers. Elapsed 0.26 s against the subquery parent's 1.17 s is variance on the same scan.","measured":"0.26 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.26,"readRows":6235069,"readBytes":2256679791,"relations":["hackernews.items"],"tags":["statistic","retention","cohort"]},{"slug":"copilot-then-cursor","construct":"event-funnel","parent":"average-month-and-peak","rung":5,"axis":"statistic","title":"Copilot first, Cursor within ninety days","problem":"Did HN's LLM commenters move from Copilot to Cursor? Not mentioned both: mentioned Copilot, then in a later item within ninety days mentioned Cursor. That is an ordered, time-bounded chain per author, which no count of tokens can express.","sql":"SELECT y, lvl, count() AS authors\nFROM (\n  SELECT toYear(original_timestamp) AS y, original_author,\n    windowFunnel(7776000, 'strict_increase')(toDateTime(original_timestamp),\n      hasToken(search_text_lc, 'copilot'), hasToken(search_text_lc, 'cursor')) AS lvl\n  FROM hackernews.items\n  WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n    AND hasToken(search_text_lc, 'llm')\n  GROUP BY y, original_author\n)\nGROUP BY y, lvl ORDER BY y ASC, lvl ASC LIMIT 20\n","technique":"windowFunnel(window, 'strict_increase')(time, cond1, cond2) walks each author's items in time order and returns the deepest step reached with every step inside window seconds of the first: 0 never mentioned Copilot, 1 Copilot only, 2 Copilot then Cursor within 90 days (7,776,000 s). strict_increase demands a strictly later timestamp between steps, so one item naming both tools cannot complete the chain; the default mode counts those and reports 102 authors at level 2 for 2025 instead of 22. The inner query is bucketed by toYear(original_timestamp), so the funnel runs per author-year and 2025 sits beside 2026 (a chain across New Year is not counted). The added tokens copilot and cursor join the control's llm rather than replacing it. 2025: 507 authors reached Copilot, 22 went on to Cursor; 2026: 353 and 7.","whyFast":"The same 6.2 M rows as the parent, plus the author column: 2.26 GB. Per row, two more hasToken tests on text already in memory; per author-year, a small time-sorted event list, about 43 K groups against the parent's twenty; then a rollup of 43 K rows into six. Elapsed 0.95 s against the subquery parent's 1.17 s is inside variance.","measured":"0.95 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.95,"readRows":6235069,"readBytes":2256578489,"relations":["hackernews.items"],"tags":["statistic","funnel","sequence"]},{"slug":"copilot-before-cursor-ever","construct":"sequence-match","parent":"average-month-and-peak","rung":5,"axis":"statistic","title":"Copilot before Cursor, any gap, same year","problem":"The ninety-day funnel is strict about time. Sometimes the question is only order: did an author mention Copilot and, at any later point in the year, mention Cursor? That drops the window and keeps the sequence.","sql":"SELECT y, matched, count() AS authors\nFROM (\n  SELECT toYear(original_timestamp) AS y, original_author,\n    sequenceMatch('(?1).*(?2)')(toDateTime(original_timestamp),\n      hasToken(search_text_lc, 'copilot'), hasToken(search_text_lc, 'cursor')) AS matched\n  FROM hackernews.items\n  WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n    AND hasToken(search_text_lc, 'llm')\n  GROUP BY y, original_author\n)\nGROUP BY y, matched ORDER BY y ASC, matched ASC LIMIT 20\n","technique":"sequenceMatch(pattern)(time, cond1, cond2) tests each author's time-ordered events against a pattern: (?1).*(?2) is a cond1 event followed, after any number of other events, by a cond2 event, two distinct items with no time bound. It returns 1 or 0, so the outer counts authors per year and outcome. The inner shape is the funnel's, bucketed by toYear(original_timestamp) and grouped by author, with sequenceMatch in place of windowFunnel. Unbounded order finds 35 authors in 2025 and 10 in 2026, a superset of the 90-day funnel's 22 and 7.","whyFast":"The same 6.2 M rows and 2.26 GB as the funnel: the author column read once more, the text already in memory for the two extra hasToken tests. About 43 K author-year event lists are checked against a two-step pattern, then rolled up into four rows. Elapsed 0.55 s against the funnel's 0.95 s is inside variance.","measured":"0.55 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.55,"readRows":6235069,"readBytes":2256578489,"relations":["hackernews.items"],"tags":["statistic","sequence","pattern"]},{"slug":"hn-versus-reddit-volume","construct":"union-all","parent":"average-month-and-peak","rung":5,"axis":"composition","title":"Hacker News and Reddit in one query","problem":"Is the LLM curve a Hacker News story or the internet's? The average and peak over HN alone have nothing to stand against. You want the same two numbers for a second venue, computed in one statement, each row tagged with where it came from.","sql":"SELECT venue, round(avg(n), 1) AS avg_per_month, max(n) AS peak_month\nFROM (\n  (SELECT 'hackernews' AS venue, toStartOfMonth(original_timestamp) AS m, count() AS n\n   FROM hackernews.items\n   WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n     AND hasToken(search_text_lc, 'llm')\n   GROUP BY m ORDER BY m ASC LIMIT 20)\n  UNION ALL\n  (SELECT 'reddit' AS venue, toStartOfMonth(created_utc) AS m, count() AS n\n   FROM reddit.posts\n   WHERE created_utc >= '2025-01-01' AND created_utc < '2026-09-01'\n     AND hasToken(search_text_lc, 'llm')\n   GROUP BY m ORDER BY m ASC LIMIT 20)\n)\nGROUP BY venue ORDER BY venue ASC LIMIT 2\n","technique":"UNION ALL stacks two SELECTs with matching columns into one input, and a venue literal in each branch labels its rows. The Reddit branch mirrors the HN branch column for column: the same twenty months on created_utc, the same term. The outer rollup keeps the parent's avg_per_month and peak_month and adds GROUP BY venue. Every set-operation branch on this API carries its own literal LIMIT and the complete set query needs an outer LIMIT, which ClickHouse accepts only through a FROM ( ) wrapper, so a union always sits under subquery. The branch reads reddit.posts; the comments archive reads 2.6 B rows for the same window. Reddit's August 2026 is partial in the mirror (2,063 posts against 20,650 in July), which pulls its average down.","whyFast":"277.6 M rows and 101 GB against the parent's 6.2 M rows and 2.1 GB. The Reddit branch is the cost: 271 M rows of reddit.posts, where llm is a common token and the token index prunes little, so the scan covers most of two years of posts. The HN branch is unchanged. Two branch aggregations of twenty groups each, then a rollup of forty rows into two.","measured":"20.6 s (median of three) over 20 months of hackernews.items and reddit.posts, 277.6 M rows read (2026-09-07).","seconds":20.6,"readRows":277552583,"readBytes":101210864949,"relations":["hackernews.items","reddit.posts"],"tags":["composition","union","cross-venue"]},{"slug":"monthly-volume-bars","construct":"inline-bars","parent":"month-by-month","rung":4,"axis":"presentation","title":"Twenty months of LLM talk, drawn inline","problem":"You want the shape of the series at a glance, in the result grid, without exporting to a chart. The parent returns the twenty counts as digits; seeing the climb from 3,942 to 9,907 means reading every number. A bar column makes the peak and the dips visible in the row itself.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n, bar(n, 0, 10000, 20) AS trend\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"bar(x, min, max, width) renders x on a min-to-max scale as a run of block glyphs width cells long, with eighth-block glyphs for the remainder. It composes onto the parent's SELECT as one more column over the alias n. The ceiling 10,000 comes from the measured peak (9,907), so the tallest month fills 19.8 of 20 cells; choose the ceiling from the data, because a ceiling below the peak clips silently to a full bar.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. The bars are twenty short strings built from the aggregated values after the scan. Elapsed 0.65 s against the parent's 0.48 s and 0.94 s in adjacent triples is inside run-to-run variance.","measured":"0.65 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.65,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["presentation","bar","time-series"]},{"slug":"every-word-counted","construct":"array-expand","parent":"story-or-comment","rung":3,"axis":"derivation","title":"One row per token, then count the tokens","problem":"Every key so far was one value per document. The vocabulary of the LLM discourse is many values per document, and counting it means expanding each row into one row per token before the GROUP BY. This is the most expensive shape in the slice, and the top of the list is stopwords, which is the lesson.","sql":"SELECT arrayJoin(tokens(search_text_lc)) AS t, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND length(t) > 3\nGROUP BY t ORDER BY n DESC LIMIT 10\n","technique":"tokens() splits the text into an array; arrayJoin expands the array so each element becomes its own row, and the element is the group key. The plain WHERE length(t) > 3 refers to the expanded alias and drops short tokens after the expansion. 14.4 M tokens in 131,838 documents, 8.3 M of them longer than three characters, land in 141,072 groups. The ranking is that, with, this, have, they: the corpus's connective tissue outranks its subject. Reading the vocabulary needs a filter before the expansion, which is the child.","whyFast":"The same 6.2 M rows and 2.15 GB as the anchored text scan; the key is derived from search_text_lc alone. The work is in the expansion: 8.3 M token rows hashed into a state holding 141,072 groups, the largest state in the slice by three orders of magnitude, then ten sorted out. 0.45 s median with runs of 0.80, 0.36 and 0.45 s: the table's spread swallows the expansion at this document count.","measured":"0.45 s (median of three) over 20 months of hackernews.items, 6.2 M rows read and 8.3 M token rows grouped (2026-09-07).","seconds":0.45,"readRows":6235069,"readBytes":2146695920,"relations":["hackernews.items"],"tags":["arrayJoin","tokens","vocabulary"]},{"slug":"the-long-words","construct":"array-filter","parent":"every-word-counted","rung":4,"axis":"derivation","title":"Filter the array before expanding it","problem":"Expanding every token buries the vocabulary under stopwords and hands the aggregate 8.3 M rows. The jargon an analyst wants is the long words. A lambda over the array keeps only those before arrayJoin fans them out, so the expansion produces 37 times fewer rows.","sql":"SELECT arrayJoin(arrayFilter(x -> length(x) >= 12, tokens(search_text_lc))) AS t, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND length(t) > 3\nGROUP BY t ORDER BY n DESC LIMIT 10\n","technique":"arrayFilter(lambda, array) keeps the elements for which the lambda is true and composes inside the same expression as tokens() and arrayJoin. The lambda variable is x so it does not shadow the outer alias t. The parent's WHERE length(t) > 3 is kept for the additive edge and is now redundant: every survivor is twelve characters or longer. The top of the LLM discourse by long words: understanding 5,486, architecture 4,124, intelligence 3,972, deterministic 3,492, conversation 3,027. Any predicate works in the lambda, a prefix test, a membership check, a length gate.","whyFast":"The same 6.2 M rows and 2.15 GB as the parent. Per row the lambda runs over every token before expansion, so the expansion emits 222,307 rows instead of 8.3 M, and the aggregate state holds 27,793 groups instead of 141,072. That is where the work went, and it is exactly the work the measurement cannot show: 0.45 s here against 0.45 s for the parent, three runs each inside the same 0.3 to 0.8 s band. At this document count the expansion was never the bottleneck; the filter is the correct shape for the corpus ten times larger.","measured":"0.45 s (median of three) over 20 months of hackernews.items, 6.2 M rows read and 222 K token rows grouped (2026-09-07).","seconds":0.45,"readRows":6235069,"readBytes":2146695920,"relations":["hackernews.items"],"tags":["arrayFilter","lambda","vocabulary"]},{"slug":"voices-behind-the-volume","construct":"distinct-count","parent":"story-or-comment","rung":3,"axis":"statistic","title":"Voices, not volume, per item type","problem":"120,400 LLM comments could be forty people or thirty thousand. The group-by count cannot tell breadth from persistence: one regular posting daily reads exactly like a crowd posting once. How many distinct people stand behind each type's count?","sql":"SELECT hn_type, count() AS n, uniq(original_author) AS voices\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY hn_type ORDER BY n DESC LIMIT 10\n","technique":"uniq() is the approximate distinct count, one column beside the exact count in the same grouped pass. The n/voices ratio is the free second finding: 120,400 comments from 29,887 voices is four per voice; 11,424 stories from 8,217 submitters is 1.4. uniq is a HyperLogLog-class sketch (error under 1 percent at these sizes); write uniqExact when the number must be exact and the group's distinct set fits in memory.","whyFast":"The same 6,235,069 rows as the parent, plus one column read (original_author, 2.36 GB against 2.25 GB). The distinct count never materializes the distinct set: each group holds a fixed-size sketch, updated per row with a hash, so five groups cost five sketches however many authors appear. The elapsed difference against the parent is inside run-to-run variance.","measured":"0.91 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.91,"readRows":6235069,"readBytes":2357531448,"relations":["hackernews.items"],"tags":["uniq","distinct","breadth","denominator"]},{"slug":"who-spoke-first-in-each-thread","construct":"group-first","parent":"story-or-comment","rung":3,"axis":"statistic","title":"The first voice in every thread","problem":"Grouped by parent, the count ranks the threads where LLM talk landed. It cannot say who opened each of them: the first reply is the row with the minimal timestamp inside its group, and a count has no row. Which threads drew the most LLM replies, and who fired the first shot in each?","sql":"SELECT parent_hn_id, count() AS n, argMin(original_author, original_timestamp) AS first_voice\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY parent_hn_id ORDER BY n DESC LIMIT 10\n","technique":"argMin(x, y) returns the x of the row where y is minimal, per group: group-wise first as a plain aggregate, no window, no self-join. The key is the parent pointer, so a group is a reply set under one item. The NULL group is the 11,438 items with no parent (11,424 stories plus jobs and polls), and it leads the ranking; the threads below it are the monthly hiring posts, where LLM turns up in 55 to 121 direct replies each. argMax with the same shape gives the last voice; the min+max pair of timestamps is the span-derive construct, so argMin stands alone here.","whyFast":"The same 6,235,069 rows as the parent, one extra column read (2.31 GB). Per group the state is one (author, timestamp) pair, replaced only when a smaller timestamp arrives; the group count is higher than the parent's (one group per parent item, tens of thousands, against five types) and that larger group count is the only added cost. The elapsed difference is inside run-to-run variance.","measured":"0.69 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.69,"readRows":6235069,"readBytes":2314988593,"relations":["hackernews.items"],"tags":["argMin","group-wise-first","threads","priority"]},{"slug":"how-long-is-an-llm-comment","construct":"quantile","parent":"story-or-comment","rung":3,"axis":"statistic","title":"The length distribution, in three numbers","problem":"Averages hide the shape: a mean word count says nothing about whether LLM comments are short quips with a long essay tail or uniformly mid-length. The median, the 90th and the 99th percentile per item type are the shape, and a count cannot produce them.","sql":"SELECT hn_type, count() AS n, quantiles(0.5, 0.9, 0.99)(word_count) AS words_p50_p90_p99\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY hn_type ORDER BY n DESC LIMIT 10\n","technique":"quantiles(levels...)(column) returns one array of all requested levels from a single sample per group; three separate quantile() calls would hold three samples. The result is the shape: LLM comments run 71 words at the median, 210 at p90, 474 at p99, while stories sit at 14 words (a title) with a p90 of 320 (the Ask HN posts that carry text). Jobs return NULLs because word_count is not populated for them. quantiles is reservoir-sampled and approximate; quantilesExact holds every value and is the choice when the group is small.","whyFast":"The same 6,235,069 rows as the parent, one Int32 column added (2.28 GB). Each group holds one bounded reservoir sample (8,192 values) rather than the group's full value list, so the state is constant in group size and the three levels are read from the same sample at the end. The elapsed difference is inside run-to-run variance.","measured":"0.56 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.56,"readRows":6235069,"readBytes":2278705639,"relations":["hackernews.items"],"tags":["quantiles","percentile","distribution","word_count"]},{"slug":"votes-track-comments-not-length","construct":"correlation","parent":"story-or-comment","rung":3,"axis":"statistic","title":"What upvotes move with, and what they ignore","problem":"Do LLM stories that draw more comments also draw more votes, and does a longer post earn more votes? Both are questions about how two columns co-vary across a group's rows, which no count and no single-column statistic can answer.","sql":"SELECT hn_type, count() AS n, round(corr(upvotes, comment_count), 3) AS votes_vs_comments, round(corr(upvotes, word_count), 3) AS votes_vs_words\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY hn_type ORDER BY n DESC LIMIT 10\n","technique":"corr(x, y) is the Pearson coefficient per group, accumulated in one pass from running sums. Two coefficients in one query make the finding: for 11,424 LLM stories, upvotes and comment counts move together at 0.848, while upvotes and word count are uncorrelated at 0.005. The length of a submission buys nothing; the conversation it starts is what the vote count tracks. Rows for comments return NULL because comments carry no upvote snapshot; corr skips NULL pairs and returns NULL when a group has none. Both columns are first-fetch snapshots, so read this over a closed window, never over the live tail.","whyFast":"The same 6,235,069 rows as the parent, three Int32 columns added (2.34 GB). Each coefficient is five running sums per group (sum x, sum y, sum xy, sum x squared, sum y squared) and a count, so two coefficients across five groups are sixty numbers of state. The three runs spread from 0.89 to 1.58 s under production load; rows read is the stable cost and it equals the parent's.","measured":"1.15 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.15,"readRows":6235069,"readBytes":2341049595,"relations":["hackernews.items"],"tags":["corr","pearson","engagement","upvotes"]},{"slug":"how-concentrated-is-the-conversation","construct":"entropy","parent":"story-or-comment","rung":3,"axis":"statistic","title":"One number for how many people really talk","problem":"A distinct count says 29,887 people commented; it cannot say whether the conversation is spread across them or dominated by a few hundred. Shannon entropy over the author column is the concentration in one number, comparable across item types.","sql":"SELECT hn_type, count() AS n, round(entropy(original_author), 2) AS author_entropy_bits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY hn_type ORDER BY n DESC LIMIT 10\n","technique":"entropy(column) is the Shannon entropy of the column's value distribution within the group, in bits. Read it through its exponent: 13.51 bits for comments means the conversation behaves like about 11,600 equally active voices, out of 29,887 actual; stories at 12.62 bits behave like about 6,300 submitters out of 8,217. Comments are the more concentrated room relative to their headcount. Pair it with uniq to see both the headcount and the effective headcount; entropy alone is a ratio without a base.","whyFast":"The same 6,235,069 rows and the same 2.36 GB as the distinct-count sibling (the author column is the one addition). entropy holds a hash map of value counts per group and reduces it at the end, so its state grows with the group's distinct authors (tens of thousands of entries for comments), which is the one aggregate in this fan whose memory is not constant. The elapsed difference against the parent is inside run-to-run variance.","measured":"0.66 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.66,"readRows":6235069,"readBytes":2357531448,"relations":["hackernews.items"],"tags":["entropy","concentration","effective-voices","shannon"]},{"slug":"three-lines-per-voice","construct":"group-collect","parent":"story-or-comment","rung":3,"axis":"statistic","title":"Three sample lines from every top voice","problem":"The ranking says who talks about LLMs most. It does not say what they sound like. Three opening fragments per author, carried alongside the count, turn a leaderboard into something a reader can judge, and no scalar aggregate can carry text out of a group.","sql":"SELECT original_author, count() AS n, groupArray(3)(left(payload, 80)) AS samples\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY original_author ORDER BY n DESC LIMIT 10\n","technique":"groupArray(k)(expr) collects up to k values per group into an array, in whatever order rows arrive; left(payload, 80) keeps each element to one line. The result reads as a voice sample: simonw's three lines are tooling and benchmarks, ACCount37's are architecture arguments. The order is not chronological and not by merit; the sorted-collect child adds the order. The text column is read inside a function, so this stays an aggregate over groups, not row retrieval.","whyFast":"The same 6,235,069 rows as the parent, but the payload column is now read: 3.80 GB against 2.26 GB, the largest byte step in this fan. Per group the state is a bounded array of three 80-character strings; groupArray with a limit stops appending once it holds k, so a 719-post author costs the same as a 3-post one. Elapsed tracks the extra 1.5 GB of text decompression.","measured":"1.05 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.05,"readRows":6235069,"readBytes":3800634872,"relations":["hackernews.items"],"tags":["groupArray","samples","collect","voice"]},{"slug":"opening-lines","construct":"sorted-collect","parent":"three-lines-per-voice","rung":4,"axis":"statistic","title":"How each voice entered the conversation","problem":"Three arbitrary samples show a voice; its first two posts of the window show its starting position, the stance it held before the year unfolded. Ordering inside a group is what groupArray lacks: you want the top k by a key, per group, without sorting every row of every group.","sql":"SELECT original_author, count() AS n, groupArray(3)(left(payload, 80)) AS samples, groupArraySorted(2)((toDate(original_timestamp), left(payload, 80))) AS opening_lines\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY original_author ORDER BY n DESC LIMIT 10\n","technique":"groupArraySorted(k)(value) keeps the k smallest values per group; make the value a tuple and the first element is the sort key, the rest the payload. (date, line) with k = 2 yields each author's two earliest LLM posts of the window, date first. simonw opens on 2025-01-01 with a Karpathy reference; the unsorted samples column stays beside it for contrast. Negate a numeric key for top-k by size: (-upvotes, title) returns the two most upvoted. The key here is toDate, not toStartOfMonth, so the query stays in this fan and does not become a time-bucket.","whyFast":"The same 6,235,069 rows and the same 3.80 GB as the parent: no new column is read, since the timestamp is already loaded for the window predicate. The addition is a two-element bounded heap per group, one comparison per row against the current second-smallest and an insertion only when the row wins, so a 719-post group sorts nothing. The elapsed difference is inside run-to-run variance.","measured":"1.15 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.15,"readRows":6235069,"readBytes":3800634872,"relations":["hackernews.items"],"tags":["groupArraySorted","top-k-per-group","earliest","tuple-key"]},{"slug":"the-agent-share","construct":"conditional-agg","parent":"story-or-comment","rung":3,"axis":"statistic","title":"How much of each voice's LLM talk is agent talk","problem":"The loudest LLM voices are known; what share of each one's posts also says agent is a second count under a stricter predicate, taken inside the same groups. A second query with a second anchor would count the agent posts but lose the per-author denominator sitting in this one.","sql":"SELECT original_author, count() AS n, countIf(hasToken(search_text_lc, 'agent')) AS also_agent, round(100 * also_agent / n, 1) AS agent_pct\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY original_author ORDER BY n DESC LIMIT 10\n","technique":"countIf(predicate) counts the rows where the predicate holds, in the same grouped pass as count(), so numerator and denominator come from one scan and the percentage is a projection over aliases. The predicate is a second hasToken evaluated per row (the term is added, never swapped for the anchor). Among the ten most prolific LLM voices the agent share runs from 2.1 percent (ACCount37) to 8.8 percent (HarHarVeryFunny); simonw's 719 posts carry 59 agent mentions, 8.2 percent. avgIf and sumIf are the same idea for means and totals.","whyFast":"The same 6,235,069 rows and 2.26 GB as a per-author group-by; the addition is one token test per matched row (131,838 evaluations of hasToken over the already-loaded search_text_lc), and one extra counter per group. No second scan: the anchor prunes once and both counts fall out of the same pass. The elapsed difference is inside run-to-run variance.","measured":"0.34 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.34,"readRows":6235069,"readBytes":2256578489,"relations":["hackernews.items"],"tags":["countIf","share","second-predicate","co-mention"]},{"slug":"tenure-of-the-regulars","construct":"span-derive","parent":"story-or-comment","rung":3,"axis":"statistic","title":"Who has been in this conversation the whole time","problem":"A post count ranks the regulars but cannot separate a voice present across the whole window from one that arrived last quarter and posted in bursts. First post, last post and the days between are per-author spans, and every veteran-versus-newcomer question is built on them.","sql":"SELECT original_author, count() AS n, min(original_timestamp) AS first_post, max(original_timestamp) AS last_post, dateDiff('day', min(original_timestamp), max(original_timestamp)) AS tenure_days\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY original_author ORDER BY n DESC LIMIT 10\n","technique":"min and max of the timestamp per group derive the interval; dateDiff names its length in days. The ten most prolific LLM voices span nearly the whole 608-day window: simonw's first in-window LLM post lands at 00:04 on 2025-01-01 and his last on 2026-08-31, 607 days apart; ACCount37 is the newcomer at 370 days from an August 2025 start. With two spans in hand, before/after and overlap questions become endpoint comparisons. The min+max pair is what the tree reads as this construct; argMin alone belongs to group-first.","whyFast":"The same 6,235,069 rows and 2.26 GB as a per-author group-by. Span state is two timestamps per group, each updated by one comparison per row, and dateDiff runs once per group at output; the 35,079 author groups are the same groups the count already held. The elapsed difference is inside run-to-run variance.","measured":"0.79 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.79,"readRows":6235069,"readBytes":2256679791,"relations":["hackernews.items"],"tags":["min","max","dateDiff","tenure","spans"]},{"slug":"the-long-tail-of-voices","construct":"named-cte","parent":"story-or-comment","rung":3,"axis":"composition","title":"Name the per-author table, then histogram it","problem":"How many people said LLM once, twice, ten times? That is a distribution over the per-author counts, an aggregate of an aggregate. Naming the first aggregation makes the second legible: the reader sees per_author defined once, then a plain GROUP BY over it.","sql":"WITH per_author AS (\n  SELECT original_author, count() AS n\n  FROM hackernews.items\n  WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n    AND hasToken(search_text_lc, 'llm')\n  GROUP BY original_author\n)\nSELECT n AS posts, count() AS authors\nFROM per_author\nGROUP BY posts ORDER BY posts ASC LIMIT 10\n","technique":"WITH name AS (query) declares a block up front and the statement below reads FROM it by name; the parent's grouped query is the block, unchanged except that ORDER BY and LIMIT move to the consumer. The histogram is the long tail in numbers: 19,057 of 35,079 authors said LLM exactly once, 5,702 twice, 277 exactly ten times. A CTE referenced by name is not a parenthesized derived table, so the statement stays in this fan; write FROM (SELECT ...) and it becomes the subquery construct.","whyFast":"The same 6,235,069 rows and 2.26 GB as the parent: the name costs nothing, the pruned scan happens once inside the block, and the outer GROUP BY runs over the 35,079 rows the block emits. The three runs spread from 0.59 to 1.98 s under production load; rows read is the stable cost and it equals the parent's.","measured":"1.02 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":1.02,"readRows":6235069,"readBytes":2258872972,"relations":["hackernews.items"],"tags":["CTE","WITH","aggregate-of-aggregate","long-tail"]},{"slug":"what-the-agent-crowd-writes","construct":"in-subquery","parent":"story-or-comment","rung":3,"axis":"composition","title":"One cohort's share of the whole conversation","problem":"The people who say agent inside the LLM conversation are a cohort you cannot type out: 5,496 handles, defined by a predicate. What share of all LLM items, by type, do those people produce? Membership in a computed set is the filter, and it takes a subquery in the WHERE clause.","sql":"SELECT hn_type, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND original_author IN (\n    SELECT DISTINCT original_author\n    FROM hackernews.items\n    WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n      AND hasToken(search_text_lc, 'llm')\n      AND hasToken(search_text_lc, 'agent')\n  )\nGROUP BY hn_type ORDER BY n DESC LIMIT 10\n","technique":"column IN (SELECT ...) is the semijoin: the inner query defines the cohort by any predicate the corpus can express, the outer histogram counts what that cohort does. Both blocks carry the control lines, and the inner adds one token. The agent crowd is 5,496 of 35,079 LLM voices and writes 47,086 of the 120,400 LLM comments (39 percent) and 3,420 of 11,424 stories (30 percent): a sixth of the people, two fifths of the talk. Compare each row to the parent's count for the share.","whyFast":"Twice the parent's scan: 12,429,738 rows and 4.60 GB, because the inner query is a second pass over the same index-pruned window (the agent token narrows its output, not its read). The cohort materializes once as a hash set of 5,496 strings; the outer pass tests each matched row against it, one hash probe per row, so the join between the two blocks costs nothing measurable beyond the second scan. The three runs spread from 0.7 to 1.5 s under production load; the 12.4 M rows read is the stable cost.","measured":"1.00 s (median of three) over 20 months of hackernews.items, 12.4 M rows read across both blocks (2026-09-07).","seconds":1.0,"readRows":12429738,"readBytes":4601250107,"relations":["hackernews.items"],"tags":["IN-subquery","semijoin","cohort","audience"]},{"slug":"which-threads-host-the-llm-talk","construct":"join","parent":"story-or-comment","rung":3,"axis":"composition","title":"Comments carry the term; the join finds the room","problem":"Grouping matching comments by parent id ranks the threads but shows only numbers. The title lives on a different row, the story, and reaching it means stitching each matching comment to the item it answers. Which stories hosted the most direct LLM replies?","sql":"SELECT s.hn_id AS story_id, any(s.title) AS story, count() AS n\nFROM hackernews.items AS c\nINNER JOIN hackernews.items AS s ON c.parent_hn_id = s.hn_id AND s.hn_type = 'story' AND s.hn_id >= 42500000\nWHERE c.original_timestamp >= '2025-01-01' AND c.original_timestamp < '2026-09-01'\n  AND hasToken(c.search_text_lc, 'llm')\nGROUP BY s.hn_id ORDER BY n DESC LIMIT 10\n","technique":"A self-join over the thread key: the control lines stay on the comment alias c, each hit joins to its parent through parent_hn_id, and s.hn_type = 'story' keeps the parents that are stories, so the count is direct replies to the story. parent_hn_id is the link because story_hn_id is NULL on most archived comments (92 percent of 2026's) and would drop them. The result names the rooms: the monthly Who wants to be hired threads lead (121 LLM replies in August 2026), with My AI skeptic friends are all nuts at 94 as the one non-recurring story in the top ten. any(s.title) reads the title as an aggregate; naming s.title bare in the projection would make this row retrieval.","whyFast":"12,591,971 rows and 2.40 GB: the parent's 6.2 M matched-side scan plus 6.4 M rows for the build side. The build side is bounded in the ON clause by the primary key (s.hn_id >= 42500000, the ids from late December 2024 on), which prunes the story lookup to the window's own items; without that bound the same join reads 51.5 M rows, the whole relation as build side. The probe side then resolves each matching comment by one hash lookup on the parent id.","measured":"1.33 s (median of three) over 20 months of hackernews.items, 12.6 M rows read across both sides (2026-09-07).","seconds":1.33,"readRows":12591971,"readBytes":2404319094,"relations":["hackernews.items"],"tags":["join","self-join","threads","parent_hn_id"]},{"slug":"stories-nobody-answered","construct":"anti-join","parent":"which-threads-host-the-llm-talk","rung":4,"axis":"composition","title":"Anti-join finds what never happened","problem":"Which LLM stories drew no reply at all, and who keeps submitting them? Absence is invisible to WHERE: there is no comment row to filter. LEFT ANTI JOIN keeps exactly the left rows that found no match on the right.","sql":"SELECT s.original_author AS author, count() AS n\nFROM hackernews.items AS s\nLEFT ANTI JOIN hackernews.items AS c ON c.parent_hn_id = s.hn_id AND c.hn_id >= 42500000\nWHERE s.original_timestamp >= '2025-01-01' AND s.original_timestamp < '2026-09-01'\n  AND hasToken(s.search_text_lc, 'llm') AND s.hn_type = 'story'\nGROUP BY author ORDER BY n DESC LIMIT 10\n","technique":"The parent's join is turned around: stories are the anchored side under the control lines, comments are the build side, and LEFT ANTI keeps stories with no comment pointing at them. Grouped by submitter it ranks shouting into the void: PaulHoule leads with 66 unanswered LLM stories, matt_d with 44. The denominator is stark: 6,849 of the 11,424 LLM stories in the window drew no direct reply, 60 percent, mostly duplicates of whichever submission won the thread. The same shape answers any absence question over two row sets.","whyFast":"12,579,539 rows and 2.51 GB, the same order as the join parent: the build side is every comment from the window, bounded by primary key in the ON clause (c.hn_id >= 42500000), hashed on its parent id once; each of the 11,424 anchored stories then costs one hash probe, and the probe's miss is the result. No NOT IN scan, no per-story subquery. Elapsed came in under the parent's: the anchored side hands 11,424 stories to the probe, not 131,838 comments, and the build side is read for its parent id alone.","measured":"0.47 s (median of three) over 20 months of hackernews.items, 12.6 M rows read across both sides (2026-09-07).","seconds":0.47,"readRows":12579539,"readBytes":2508305844,"relations":["hackernews.items"],"tags":["ANTI JOIN","absence","set-subtraction","unanswered"]},{"slug":"days-since-their-last-llm-post","construct":"asof-join","parent":"which-threads-host-the-llm-talk","rung":4,"axis":"composition","title":"The nearest earlier row, per row","problem":"How long does a voice go quiet between LLM posts? For every item you want exactly one partner, the same author's most recent previous LLM item, and an equality join returns all of them. ASOF JOIN picks the one nearest under an inequality, per row.","sql":"SELECT a.hn_type AS kind, count() AS n, round(avg(dateDiff('day', b.original_timestamp, a.original_timestamp)), 1) AS days_since_previous\nFROM hackernews.items AS a\nASOF JOIN hackernews.items AS b\nMATCH_CONDITION (a.original_timestamp > b.original_timestamp)\nON a.original_author = b.original_author AND hasToken(b.search_text_lc, 'llm') AND b.original_timestamp >= '2025-01-01' AND b.original_timestamp < '2026-09-01'\nWHERE a.original_timestamp >= '2025-01-01' AND a.original_timestamp < '2026-09-01'\n  AND hasToken(a.search_text_lc, 'llm')\nGROUP BY a.hn_type ORDER BY n DESC LIMIT 10\n","technique":"ASOF JOIN matches each left row to the single right row that is nearest under MATCH_CONDITION within the ON equality group: here, the same author's latest earlier item. The served spelling is MATCH_CONDITION plus ON (translated server-side); GROUP BY needs the qualified column. The build side's predicates belong in the ON clause: written in WHERE they apply after the match, so each item pairs with its author's previous post of any kind and survives only if that happened to be an LLM post, which returned 12,570 comments at 4.3 days apart, wrong on both counts. Filtered in ON, 91,839 comments align to a previous LLM comment 35.7 days earlier on average, and 4,652 stories to one 54.1 days earlier. Items with no predecessor in the window drop out, as an inner join should.","whyFast":"51,532,931 rows and 19.2 GB: the build side reads the whole relation, because ON-clause predicates on the ASOF build side filter after the read rather than pruning it (a primary-key bound in the same clause changed nothing). The build then holds, per author, a timestamp-sorted list of that author's in-window LLM items, and each of the 131,838 probe rows costs one binary search in its author's list. The three runs spread from 7.6 to 10.4 s under production load; the 51.5 M rows read is the stable cost, four times the parent's.","measured":"7.87 s (median of three) over 20 months of hackernews.items, 51.5 M rows read across both sides (2026-09-07).","seconds":7.87,"readRows":51532931,"readBytes":19228256784,"relations":["hackernews.items"],"tags":["ASOF","MATCH_CONDITION","temporal-align","cadence"]},{"slug":"walk-the-inevitabilism-thread","construct":"recursive","parent":"which-threads-host-the-llm-talk","rung":4,"axis":"composition","title":"Recursion walks what a join cannot reach","problem":"A join reaches one hop; a comment tree is as deep as the argument went. The shape of the window's largest LLM thread, how many replies at each depth and how deep the deepest chain goes, is transitive closure over the parent pointer, and no fixed number of joins expresses it.","sql":"WITH RECURSIVE thread AS (\n  SELECT hn_id, parent_hn_id, 0 AS depth\n  FROM hackernews.items\n  WHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n    AND hasToken(search_text_lc, 'llm') AND hn_id = 44567857\n  UNION ALL\n  SELECT c.hn_id, c.parent_hn_id, t.depth + 1 AS depth\n  FROM hackernews.items AS c\n  INNER JOIN thread AS t ON c.parent_hn_id = t.hn_id\n  WHERE t.depth < 20 AND c.original_timestamp >= '2025-07-15' AND c.original_timestamp < '2025-08-01'\n)\nSELECT depth, count() AS n\nFROM thread\nGROUP BY depth ORDER BY depth ASC LIMIT 20\n","technique":"WITH RECURSIVE iterates the join until the frontier is empty: the seed row in, the children of the current frontier each round, depth carried as a counter. The seed is the window's most-commented LLM story, LLM Inevitabilism (hn_id 44567857, 2025-07-15, 1,628 comments). The load-bearing lines are inside the recursive arm: a depth cap and a date window around the story's own fortnight bound every round, because story_hn_id is NULL on most archived replies and cannot scope the walk. The closure holds 1,598 comments: 124 top-level replies swelling to 290 at depth 3 and thinning to a single comment at depth 16. The UNION ALL inside WITH RECURSIVE is part of this construct, not the union-all entry.","whyFast":"3,132,860 rows and 62 MB, a quarter of the parent join's rows and a fortieth of its bytes: every round is a key join over the seventeen-day slice the recursive arm names, only two Int64 columns and a timestamp are read, and the seed itself is a primary-key lookup. Sixteen rounds; the three runs spread from 2.4 to 3.3 s under production load, and the 3.1 M rows read is the stable cost. Drop the date bound and each round rescans the relation; the bound is the difference between a thread walk and seventeen full passes.","measured":"3.25 s (median of three) for a 1,598-comment, depth-16 thread in hackernews.items, 3.1 M rows read (2026-09-07).","seconds":3.25,"readRows":3132860,"readBytes":62331917,"relations":["hackernews.items"],"tags":["WITH-RECURSIVE","transitive-closure","thread-shape","bounded-recursion"]},{"slug":"substring-door-into-reddit","construct":"ngram-door","parent":null,"rung":1,"axis":"anchor","title":"Substring search across Reddit through the trigram index","problem":"Some needles are not tokens: a phrase inside a URL, a hyphenated compound, a fragment of a word. Reddit comments carry a trigram index on lower(body), and an analyst wants the 2025 to 2026 comments containing 'prompt injection' as a raw substring, first page, with no token grammar in the way.","sql":"SELECT author, score, subreddit, left(body, 120) AS excerpt\nFROM reddit.comments\nWHERE created_utc >= '2025-01-01' AND created_utc < '2026-09-01'\n  AND lower(body) LIKE '%prompt injection%' LIMIT 20\n","technique":"Write LIKE over the relation's indexed expression exactly, lower(body) on reddit.comments: the ngrams(3) inverted index intersects the needle's trigram posting lists and skips the rest. The expression must match the index verbatim; body ILIKE and positionCaseInsensitive read every row. The door lives on this relation, which is why the family's relation changes here. The projection keeps body inside left(): a top-level body column would fire row retrieval. No ORDER BY, because a global sort reads every candidate while a streamed LIMIT stops at the first twenty matches.","whyFast":"5.6 M rows read and 868 MB for twenty rows out of twenty months of Reddit comments. The date scope prunes to the 2025 and 2026 partitions, the trigram index admits only granules whose posting lists intersect on the needle's trigrams, and LIMIT 20 without a sort stops the readers as soon as twenty rows have streamed. Rows read moves run to run for this shape (5.6 M to 14.8 M observed) because which readers deliver the first twenty is not deterministic; the mechanism is stable, the exact count is not.","measured":"1.22 s (median of three) over 20 months of reddit.comments, 5.6 M rows read (2026-09-07).","seconds":1.22,"readRows":5570560,"readBytes":867758749,"relations":["reddit.comments"],"tags":["substring","trigram-index","reddit"]},{"slug":"prompt-injection-on-its-own-terms","construct":"lex-grammar","parent":null,"rung":1,"axis":"anchor","title":"Prompt injection, counted without the SQL-injection analogy","problem":"Prompt injection is the security story of the agent era, and an analyst wants its footprint on Hacker News across the twenty months from January 2025. A token anchor cannot ask for the two-word phrase in order, and it cannot subtract the threads that reach the topic only through the SQL-injection analogy. One search line does both.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_lex('\"prompt injection\" -sql') LIMIT 1\n","technique":"scry_lex('<line>') compiles a full search-grammar line into the relation's index-engaging predicate: bare words AND together, double quotes bind a phrase in order, a leading minus excludes, parentheses and OR group. It composes as an ordinary boolean beside the date scope, inside countIf, under GROUP BY. One registered relation per statement, at most eight calls. The detector fires on the scry_lex( call itself; the operators inside the string are what each child adds, so this base line is kept verbatim across the family.","whyFast":"5.2 M rows read against the trunk probe's 6.2 M: the phrase's tokens are rarer than llm, so the token index admits fewer granules. 2.9 GB against the probe's 2.1 GB, 565 bytes per row against 345, because the phrase order and the -sql exclusion are confirmed on the text of every admitted row rather than decided from posting lists alone. 2,620 documents pass; the exclusion removes 94 of the 2,714 that carry the phrase.","measured":"0.53 s (median of three) over 20 months of hackernews.items, 5.2 M rows read (2026-09-07).","seconds":0.53,"readRows":5160215,"readBytes":2913292320,"relations":["hackernews.items"],"tags":["search-grammar","phrase","negation"]},{"slug":"jailbreak-every-inflection","construct":"lex-wildcard","parent":"prompt-injection-on-its-own-terms","rung":2,"axis":"anchor","title":"Prompt-injection threads that also discuss jailbreaking","problem":"Jailbreak, jailbreaks, jailbreaking, jailbroken: the concept lives in four inflections and an exact token catches one. The analyst wants the prompt-injection cohort that also discusses jailbreaking in any form, without enumerating the forms by hand.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_lex('\"prompt injection\" -sql jailbreak*') LIMIT 1\n","technique":"A trailing * expands the stem server-side against the corpus vocabulary into the suffix forms that actually occur, ORed together, and the expansion ANDs with the rest of the line. Anchor discipline holds: a stem that expands to hundreds of common words is a broad query wearing narrow syntax, so probe the count before retrieving rows. 100 of the parent's 2,620 documents carry a jailbreak form.","whyFast":"671 K rows read against the parent's 5.2 M, 345 MB against 2.9 GB. The expanded forms enter the AND as index tokens, and the rarest token in an AND governs which granules the index admits; rows read fall by 87 percent while the work per admitted row stays the same text confirmation.","measured":"0.48 s (median of three) over 20 months of hackernews.items, 671 K rows read (2026-09-07).","seconds":0.48,"readRows":670706,"readBytes":345284911,"relations":["hackernews.items"],"tags":["search-grammar","wildcard","morphology"]},{"slug":"anthropic-within-one-edit","construct":"lex-fuzzy","parent":"prompt-injection-on-its-own-terms","rung":2,"axis":"anchor","title":"Which prompt-injection threads name Anthropic, typos included","problem":"Vendor names get misspelled and possessivised: Anthropic's, Antropic, Anthropics. An exact token misses every variant, and an analyst asking which vendor prompt-injection threads name wants the whole mass, not the correctly spelled part of it.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_lex('\"prompt injection\" -sql anthropic~1') LIMIT 1\n","technique":"word~1 resolves against the corpus vocabulary into the forms that occur within one edit of the word and searches them as an OR inside the line. The exact count beside the fuzzy count is the variant mass exact queries have been silently missing. 117 of the parent's 2,620 documents name the vendor within one edit.","whyFast":"726 K rows read against the parent's 5.2 M, 387 MB against 2.9 GB. The resolved forms enter the AND as index tokens with far shorter posting lists than the phrase's, so the index admits a seventh of the granules; the edit-distance work happens once against the vocabulary, never per row.","measured":"0.66 s (median of three) over 20 months of hackernews.items, 726 K rows read (2026-09-07).","seconds":0.66,"readRows":726190,"readBytes":387475300,"relations":["hackernews.items"],"tags":["search-grammar","fuzzy","spelling"]},{"slug":"system-prompt-interrupted","construct":"lex-slop","parent":"prompt-injection-on-its-own-terms","rung":2,"axis":"anchor","title":"'System prompt' with up to three words in between","problem":"'system prompt', 'system's hidden prompt', 'system and user prompts': one concept that discourse keeps interrupting. An exact phrase counts the tidy form only. The analyst wants the prompt-injection cohort that discusses the system prompt in any of its interrupted forms.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_lex('\"prompt injection\" -sql \"system prompt\"~3') LIMIT 1\n","technique":"\"phrase\"~N keeps the words in order and allows up to N intervening words between neighbours, N at most 50. Slop widens recall along the phrase axis the way the wildcard widens it along morphology. The tilde follows a closing double quote, which is what separates slop from the fuzzy operator's word~N in both the grammar and the detector. 89 of the parent's 2,620 documents carry the phrase within three words.","whyFast":"629 K rows read against the parent's 5.2 M and 266 MB against 2.9 GB: the slopped phrase's words join the AND as index terms, so the index admits only granules holding system and prompt beside the parent's tokens. The order-and-distance check runs on the admitted rows only.","measured":"0.82 s (median of three) over 20 months of hackernews.items, 629 K rows read (2026-09-07).","seconds":0.82,"readRows":629216,"readBytes":266139252,"relations":["hackernews.items"],"tags":["search-grammar","proximity","phrase"]},{"slug":"data-near-exfiltration","construct":"lex-near","parent":"prompt-injection-on-its-own-terms","rung":2,"axis":"anchor","title":"Prompt injection where data sits near exfiltration or leak","problem":"The claim that matters in a prompt-injection thread is that data gets out: 'exfiltrate data', 'data exfiltration', 'leak your data'. Both orders, three surface forms. A phrase fixes the order and a bare AND accepts documents where the words are paragraphs apart. The analyst wants the two ideas discussed together.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_lex('\"prompt injection\" -sql data NEAR (exfiltrate OR exfiltration OR leak)') LIMIT 1\n","technique":"a NEAR b matches both orders within a character window (default 100, NEAR/50 tightens it, at most 1,000), and each side may be a word, a phrase or an OR group. The document-AND count beside the NEAR count separates 'mentions both' from 'discusses together', which is the difference between a keyword hit and a claim. 108 of the parent's 2,620 documents pass.","whyFast":"769 K rows read against the parent's 5.2 M, 351 MB against 2.9 GB: the NEAR operands enter the AND as index tokens and prune before any distance is measured. The window test is a position scan per admitted row, which makes this the slowest lex operator here at 1.9 s over a seventh of the parent's rows.","measured":"1.94 s (median of three) over 20 months of hackernews.items, 769 K rows read (2026-09-07).","seconds":1.94,"readRows":768842,"readBytes":351157197,"relations":["hackernews.items"],"tags":["search-grammar","proximity","either-order"]},{"slug":"injection-in-the-headline","construct":"lex-field-pin","parent":"prompt-injection-on-its-own-terms","rung":2,"axis":"scope","title":"Prompt injection in titles versus in discussion","problem":"A phrase found in 2,620 documents may be a headline topic or a comment-thread aside, and the two move independently. The parent counts presence anywhere in the text. Pinning the same line to title separates what gets submitted from what gets discussed.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_lex('\"prompt injection\" -sql', title) LIMIT 1\n","technique":"The second argument to scry_lex pins the text expression the grammar compiles against, here title. Everything in the line is unchanged: the phrase, the exclusion, the count. The detector recognises the pin as a comma after the line's closing quote. The pinned count beside the parent's (288 of 2,620, eleven percent) is the submit-to-discuss ratio for the topic.","whyFast":"2.0 M rows read against the parent's 5.2 M, and 28.7 MB against 2.9 GB. Title is empty on 92 percent of rows, since comments carry none, and the column is a fraction of the full text's width, so the same phrase test runs over far fewer rows and reads a hundredth of the bytes. Scope, not the operator, is what moved the cost.","measured":"0.39 s (median of three) over 20 months of hackernews.items, 2.0 M rows read (2026-09-07).","seconds":0.39,"readRows":1968900,"readBytes":28713682,"relations":["hackernews.items"],"tags":["search-grammar","scope","title"]},{"slug":"injections-that-cite-a-cve","construct":"lex-regex","parent":"prompt-injection-on-its-own-terms","rung":2,"axis":"qualifier","title":"Prompt-injection discussions that cite a CVE identifier","problem":"A prompt-injection thread that cites a CVE is one that reached the vulnerability process, not just the demo. CVE identifiers are a pattern, not a vocabulary: CVE-2025-32711 and its thousands of siblings. Only a regex names the class, and the parent has no way to say it.","sql":"SELECT count() AS hits\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_lex('\"prompt injection\" -sql /CVE-[0-9]{4}-[0-9]+/') LIMIT 1\n","technique":"/pattern/ is RE2 over the text, and the grammar requires a positive literal beside it: the phrase bounds the candidates and the pattern evaluates as the residual, which is why this node is a qualifier rather than an anchor. A bare /CVE-[0-9]{4}-[0-9]+/ is refused outright, anchor discipline enforced by the parser. Lookaround and backreferences are rejected rather than approximated. 13 of the parent's 2,620 documents cite a CVE.","whyFast":"91 K rows read against the parent's 5.2 M and 43 MB against 2.9 GB. The numbers show the pattern pruned rather than scanned: its constant prefix is used as an index term, so RE2 runs only on granules that hold both the phrase and a CVE-shaped literal, and the query finishes in a tenth of a second.","measured":"0.10 s (median of three) over 20 months of hackernews.items, 91 K rows read (2026-09-07).","seconds":0.1,"readRows":90960,"readBytes":43321931,"relations":["hackernews.items"],"tags":["search-grammar","regex","residual"]},{"slug":"burnout-on-hacker-news","construct":"recipe","parent":null,"rung":1,"axis":"anchor","title":"How much of Hacker News talks burnout, by a shared instrument","problem":"Burnout is a construct, not a keyword: tired, exhausted, quitting, can't keep going, and a dozen phrasings an analyst would each have to remember. A hand-written token list is unmeasured and private. A published instrument is curated, measured and shared, and the twenty-month count is one call.","sql":"SELECT count() AS burnout_docs\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_recipe('burnout') LIMIT 1\n","technique":"scry_recipe('<slug>') expands a published term instrument (tokens, phrases, regex members) into an anchored membership predicate. Read a recipe's measurements.doc_precision before trusting matches; the concept's definition improves in one place and every consumer inherits the fix. The count keeps its name, burnout_docs, through the whole family: the children add a value beside it or compose the slug, never replace it.","whyFast":"6.2 M rows read and 2.1 GB, the same rows as the trunk probe, at 13 s against its 0.5 s. The instrument's members are ordinary words, so the token index admits nearly every granule, and the phrase and regex members are then confirmed against the text of each admitted row. Rows read is the stable signal; the three runs spanned 7.3 to 14.5 s under production load.","measured":"13.12 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":13.12,"readRows":6184765,"readBytes":2131751057,"relations":["hackernews.items"],"tags":["recipe","instrument","burnout"]},{"slug":"unhedged-burnout","construct":"recipe-algebra","parent":"burnout-on-hacker-news","rung":2,"axis":"anchor","title":"Burnout stated flatly: the cohort minus hedging","problem":"'I might be a little burned out, maybe' and 'I am done' are both burnout documents. The analyst wants the ones that state it without hedging, and has two published instruments, one per construct. Composing them should not require publishing a third.","sql":"SELECT count() AS burnout_docs\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_recipe('burnout - hedging') LIMIT 1\n","technique":"The recipe argument takes one whitespace-spaced operator per call: 'a - b' difference, 'a & b' intersection, 'a ^ b' exclusive-or. The expansion keeps a positive index-engaging leaf in front by construction, so the subtraction rides as a residual. Overlap is a property to measure, not assume: 4,391 of the 8,510 burnout documents survive the subtraction, so hedging touches nearly half the cohort. A composition worth reusing gets published as its own recipe.","whyFast":"6.2 M rows and 2.1 GB, the same as the parent: the positive leaf admits the same granules, and the subtracted instrument is one extra membership test on the admitted rows. 15.0 s against 13.1 s is inside variance; this child's runs spanned 6.8 to 15.2 s.","measured":"14.98 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":14.98,"readRows":6182221,"readBytes":2130587797,"relations":["hackernews.items"],"tags":["recipe","algebra","hedging"]},{"slug":"burnout-intensity","construct":"recipe-score","parent":"burnout-on-hacker-news","rung":2,"axis":"derivation","title":"How intensely the burnout cohort scores on its own instrument","problem":"Membership is binary; intensity is not. A thread that brushes burnout once and a thread that is nothing but burnout both count as one document. The analyst wants the cohort's average weighted score beside its size, from the same instrument that defined it.","sql":"SELECT count() AS burnout_docs,\n       round(avg(scry_recipe_score('burnout', search_text_lc)), 4) AS avg_score\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_recipe('burnout') LIMIT 1\n","technique":"scry_recipe_score('<slug>', text) grades one row by the weighted mean of its recipe tokens. scry_recipe gates the cohort and the score measures each member, and the two never need to agree: WHERE one instrument, ORDER BY another. Score takes one positive slug, no algebra; publish a composition as its own recipe to score it. Aggregated per cohort, the score is a register dial comparable across communities and eras.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. The score is computed only on the 8,510 rows the recipe admits, one weighted token pass each, and the average holds a single accumulator. The elapsed difference, 4.0 s against the parent's 13.1 s, is inside run-to-run variance: the parent's runs spanned 7.3 to 14.5 s and this child's 3.0 to 6.9 s.","measured":"3.97 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":3.97,"readRows":6184765,"readBytes":2131657049,"relations":["hackernews.items"],"tags":["recipe","score","intensity"]},{"slug":"burnout-per-thousand-characters","construct":"recipe-density","parent":"burnout-on-hacker-news","rung":2,"axis":"derivation","title":"Burnout term density, normalized for document length","problem":"A long essay collects as many burnout terms as a short comment and more, so a raw score rewards length. The analyst comparing a one-line comment against a two-thousand-word post wants occurrences per thousand characters, which neither the count nor the score provides.","sql":"SELECT count() AS burnout_docs,\n       round(avg(scry_recipe_density('burnout', search_text_lc)), 3) AS avg_density\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND scry_recipe('burnout') LIMIT 1\n","technique":"scry_recipe_density('<slug>', text) measures weighted recipe-term occurrences per 1,000 characters across token, phrase and regex members: the length-normalized plane of the instrument, accepting recipes of at most 128 terms. Use density when cohorts differ in document length and score when they do not. The count is retained and the score is not part of this node, so the two derivations sit side by side, each one operator from the count.","whyFast":"The same 6.2 M rows and 2.1 GB as the parent. Density runs one pass over the text of the 8,510 admitted rows, counting every member kind against the character length, and the average holds a single accumulator. 6.4 s against the parent's 13.1 s is inside run-to-run variance; this child's runs spanned 3.5 to 6.9 s.","measured":"6.40 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":6.4,"readRows":6184765,"readBytes":2131657049,"relations":["hackernews.items"],"tags":["recipe","density","length-normalized"]},{"slug":"fabricated-citations-neighbours","construct":"vector-anchor","parent":null,"rung":1,"axis":"anchor","title":"Forum posts nearest to 'LLMs fabricate citations'","problem":"Fabricated citations are discussed under many vocabularies: hallucinated references, invented sources, made-up DOIs. No token list covers them. The analyst wants the forum passages closest in meaning to one sentence, ranked by distance, which no lexical anchor can produce.","sql":"SELECT post_key, chunk_index, token_count,\n       scry_vector_topk_distance(embedding_voyage4, @llm_fabricated_citations) AS distance\nFROM embeddings.forum_posts\nWHERE model_name = 'voyage-4-lite'\nORDER BY distance ASC LIMIT 20\n","technique":"Mint the handle first: POST /v1/scry/embed with JSON {text, name} under the same bearer key. This entry embedded the sentence 'Large language models confidently fabricate citations and invent sources that do not exist, and readers cannot tell the hallucinated references from real ones.' as @llm_fabricated_citations. Then scry_vector_topk_distance(embedding_voyage4, @name) AS distance, ORDER BY distance ASC, LIMIT at most 100, from one ANN relation with no JOIN. The vector column is valid only inside this projection, never as a readable column. WHERE predicates post-filter a global window of about 400 candidates rather than scoping the search, so put selectivity into the sentence itself. Hydrate text in a second statement: forums.posts WHERE post_key IN (...).","whyFast":"259 K rows read and 16 MB. The nearest-neighbour search runs in the Lance ANN lane and returns a candidate window; ClickHouse then reads only the metadata rows needed to hydrate and filter those candidates, never the 15.5 M-row relation. Twenty rows sorted. The three runs spanned 0.3 to 3.5 s and read 259 K to 2.0 M rows for the identical statement, so carry the median and treat the spread as the lane's variance; embedding the sentence is a separate, one-time call.","measured":"0.61 s (median of three) over embeddings.forum_posts, 259 K rows read (2026-09-07).","seconds":0.61,"readRows":259136,"readBytes":16024788,"relations":["embeddings.forum_posts"],"tags":["vector","ann","semantic"]}],"compositions":[{"slug":"injection-by-record-kind","factors":["prompt-injection-on-its-own-terms","story-or-comment"],"title":"Prompt-injection talk, counted per record kind","problem":"The search line counts 2,620 documents that discuss prompt injection without the SQL-injection analogy, and the group-by splits the LLM discourse into stories and comments. Neither says where inside the LLM discourse the injection conversation happens: is it submitted as stories or argued out in comments? That needs the grammar predicate evaluated inside the aggregation, under the trunk's anchor.","sql":"SELECT hn_type, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND scry_lex('\"prompt injection\" -sql')\nGROUP BY hn_type ORDER BY n DESC LIMIT 10\n","technique":"scry_lex is a boolean, so it takes its place beside the token anchor in the trunk's WHERE and the group-by's SELECT, GROUP BY and ORDER idiom carry over unchanged; the trunk control lines are verbatim. The answer is two rows: 491 comments and 128 stories, 619 documents in all, no job or poll. Against the trunk's 120,400 comments and 11,424 stories, one LLM story in 89 is about prompt injection and one LLM comment in 245: the topic is submitted nearly three times as often as it is argued. The detector fires lex-grammar on the scry_lex( call and token-anchor on hasToken(, so the fired set is the exact union of the two factors.","whyFast":"2.7 M rows and 1.50 GB read, against 6.2 M for the group-by and 5.2 M for the search line alone: both predicates engage the token index, so a granule survives only if it can hold llm and the phrase's tokens, and the conjunction admits fewer granules than either term. Per admitted row the phrase order and the -sql exclusion are confirmed on the text, 562 bytes per row, the same per-row price the search line pays on its own. Two groups held. The median of 3.38 s came from a triple that spanned 0.57 to 3.96 s on identical text; rows read is the number to carry.","measured":"3.38 s (median of three) over 20 months of hackernews.items, 2.7 M rows read (2026-09-07).","seconds":3.38,"readRows":2671219,"readBytes":1502117479,"relations":["hackernews.items"],"tags":["search-grammar","GROUP-BY","composition","record-kind"]},{"slug":"burnout-inside-llm-talk","factors":["burnout-on-hacker-news","month-by-month"],"title":"A published instrument, laid along the calendar","problem":"The burnout recipe counts documents once over the whole window; the month bucket draws the LLM curve with no notion of burnout. An analyst asking whether LLM work is wearing people down needs the instrument evaluated inside the LLM discourse, month by month, on one calendar axis. The recipe alone has no time axis and the time bucket alone has no instrument.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND scry_recipe('burnout')\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"scry_recipe('burnout') is a boolean, so it sits beside the token anchor in the trunk's WHERE while the time bucket's projection, GROUP BY m and ORDER BY m ASC carry over verbatim. The recipe's own entry names its count burnout_docs; here the count keeps the time bucket's n because the series is the shape being read. 285 LLM documents match the instrument over twenty months: 4 in January 2025, 19 in June 2025, then 27 and 29 in February and March 2026 and 29 again in July 2026. 2025's twelve months hold 119, 2026's eight months hold 166: the monthly rate doubled from about 10 to about 21 while the LLM discourse itself grew by about 1.6 times. The definition of burnout improves in one place and this series inherits the fix.","whyFast":"1.8 M rows and 623 MB read, against 6.2 M rows and 13 s for the recipe on its own. The instrument's members are ordinary words, so alone it admits nearly every granule; anchored on llm the token index admits only granules that can hold the anchor, and the phrase and regex members are confirmed on the text of those rows only. 346 bytes per row, the probe's per-row price. Twenty groups held. The triple spanned 0.46 to 3.50 s; the median is 1.43 s.","measured":"1.43 s (median of three) over 20 months of hackernews.items, 1.8 M rows read (2026-09-07).","seconds":1.43,"readRows":1802299,"readBytes":623100019,"relations":["hackernews.items"],"tags":["recipe","time-series","composition","burnout"]},{"slug":"posted-twice","factors":["the-reposts","which-threads-host-the-llm-talk"],"title":"Reposts as pairs: same account, days apart, who scored","problem":"The fingerprint group-by finds 304 clusters of near-identical LLM stories and shows one specimen per cluster. It cannot say whether the copies came from the same account, how many days apart they landed, or whether the later copy outscored the earlier one, because those are facts about a pair, and a pair lives on two rows. A self-join on the fingerprint puts the two copies side by side.","sql":"SELECT ngramSimHash(a.search_text_lc) AS fingerprint, any(a.title) AS story, count() AS pairs,\n       sum(a.original_author = b.original_author) AS same_author,\n       sum(b.upvotes > a.upvotes) AS repost_scored_higher,\n       max(dateDiff('day', a.original_timestamp, b.original_timestamp)) AS widest_gap_days\nFROM hackernews.items AS a\nINNER JOIN hackernews.items AS b\n  ON ngramSimHash(a.search_text_lc) = ngramSimHash(b.search_text_lc)\n  AND b.hn_type = 'story' AND b.hn_id >= 42500000 AND b.original_timestamp < '2026-09-01'\n  AND hasToken(b.search_text_lc, 'llm')\nWHERE a.original_timestamp >= '2025-01-01' AND a.original_timestamp < '2026-09-01'\n  AND hasToken(a.search_text_lc, 'llm')\n  AND a.hn_type = 'story' AND a.hn_id < b.hn_id\nGROUP BY fingerprint ORDER BY pairs DESC LIMIT 10\n","technique":"The fingerprint entry's key becomes the join key: ngramSimHash on both aliases, equal in the ON clause, so ClickHouse computes the hash per side and joins on the 64-bit value. The join entry's idiom carries over whole: the control lines on the probe alias a, the build side bounded in ON by the primary key (b.hn_id >= 42500000), by hn_type and by the anchor, plus the window's end so both copies sit inside it. a.hn_id < b.hn_id in WHERE keeps each pair once, earlier copy first. A cluster of k copies yields k(k-1)/2 pairs, so 15 pairs is six submissions of 'Stealing Reasoning Traces from Proprietary LLM APIs' by six different accounts inside nine days, and no later copy outscored an earlier one. 'Mastering LLM Techniques: Inference Optimization' is five copies by one account over sixty days, and in six of its ten pairs the later copy scored higher. 'LLM Visualization' returned across 164 days from different accounts. Across the window the join emits 503 pairs in 304 clusters: 112 pairs share an account and in 147 the later copy scored higher than the earlier. Clusters tied at six pairs fall below LIMIT 10 in an order that varies between runs. sum over a boolean counts the pairs where it holds without a countIf, and max alone leaves span-derive quiet.","whyFast":"12.5 M rows and 4.91 GB read: the probe side is the fingerprint entry's 6.2 M rows, and the build side is another 6.3 M because the ON bounds prune it to the window's own LLM stories before anything is hashed. The bytes double against the join entry's 2.40 GB because both sides now decompress search_text_lc to compute the fingerprint, where that entry's build side read only ids, kinds and titles. Per row one ngramSimHash on each side; the hash table holds 11,485 story fingerprints (the window's LLM stories plus the last days of December 2024 that the id bound admits); the join emits 503 pairs and the group-by folds them into 304 clusters. No pair of texts is ever compared, only fingerprints, which is why the median is 0.41 s across a triple of 0.40 to 0.50 s.","measured":"0.41 s (median of three) over 20 months of hackernews.items, 12.5 M rows read across both sides (2026-09-07).","seconds":0.41,"readRows":12485658,"readBytes":4909818750,"relations":["hackernews.items"],"tags":["ngramSimHash","self-join","composition","reposts"]},{"slug":"when-the-agent-vocabulary-arrived","factors":["k-of-n-vocabulary","month-by-month"],"title":"The agent-engineering quorum, month by month","problem":"The quorum gate finds 363 LLM documents that carry three or more words of the agent-engineering vocabulary, one number for the whole window. The month bucket draws the LLM curve but cannot tell agent engineering from any other LLM talk. The question an analyst has is when that vocabulary arrived: a steady presence, or a phase change.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\n  AND arrayCount(t -> has(['agent', 'tool', 'harness', 'sandbox', 'orchestration', 'subagent', 'eval'], t),\n                 arrayDistinct(tokens(search_text_lc))) >= 3\nGROUP BY m ORDER BY m ASC LIMIT 20\n","technique":"The quorum predicate is copied from its entry word for word, seven terms and k = 3, and appended to the trunk's WHERE after the anchor; the time bucket's projection, GROUP BY m and ORDER BY m ASC carry over verbatim. The twenty months sum to the quorum entry's 363, which is the check that nothing moved. The series is a phase change: 2 documents in January 2025, single digits through September, 12 in October 2025, 27 in January 2026, 43 in February, 45 in August 2026. 2026's eight months hold 290 of the 363, four in five, while the LLM discourse as a whole only grew by about 1.6 times between the years. The agent stack was being engineered in public from the turn of 2026.","whyFast":"6.2 M rows and 2.15 GB read, the trunk probe's scan to the byte. The quorum entry records 2.1 M rows for a repeated execution because the query condition cache skips granules where the gate found nothing; this triple read the full anchored scan on every run, so the number here is the first-execution price of the same predicate. Per anchored row, 131,838 of them, the work is a tokenize, a dedup and seven set tests; twenty groups held. The triple spanned 0.32 to 1.00 s.","measured":"0.86 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.86,"readRows":6235069,"readBytes":2148861953,"relations":["hackernews.items"],"tags":["quorum","time-series","composition","agents"]},{"slug":"front-page-odds-by-vendor-breadth","factors":["vendor-breadth","the-agent-share"],"title":"Does naming more model families reach the front page?","problem":"The breadth key counts how many LLM documents name one, two or five model families; the conditional count measures a share under a second predicate inside each group. Neither alone can answer whether cross-vendor comparison pays: what fraction of the stories at each breadth crossed 100 points? That is a share per derived key, two counts taken in the same grouped pass.","sql":"SELECT length(arrayDistinct(extractAll(search_text_lc, '\\\\b(?:gpt|claude|gemini|llama|mistral|deepseek|qwen|grok)\\\\b'))) AS families, count() AS n,\n       countIf(hn_type = 'story') AS stories, countIf(upvotes >= 100) AS front_page, round(100 * front_page / stories, 1) AS front_page_pct\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY families ORDER BY n DESC LIMIT 10\n","technique":"The breadth entry's key, pattern and GROUP BY are verbatim; the conditional-agg entry contributes two countIf columns and the percentage projected over their aliases. The denominator is stories, not n, because comments carry no score in this export and would drag every share toward zero; upvotes >= 100 is false on NULL, so the numerator needs no kind test. Nine rows. Stories naming no family reach 100 points 2.1 percent of the time (197 of 9,304); one family, 2.9 percent (44 of 1,527); two, 1.9 percent (7 of 376); three, 4.6 percent (7 of 153). Four families or more: 64 stories, none crossed 100. Breadth up to three helps at the margin; past three the comparison pieces stop landing.","whyFast":"The same 6.2 M rows as the breadth entry and 2.28 GB against its 2.15 GB: the two countIf predicates read hn_type and upvotes, two narrow columns the parent left on disk. Per anchored row the regex still runs to the end of the text, then one string compare and one integer compare; nine groups held, three counters each. The triple spanned 0.33 to 0.78 s, inside the spread the breadth entry itself shows.","measured":"0.65 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.65,"readRows":6235069,"readBytes":2276379024,"relations":["hackernews.items"],"tags":["extractAll","countIf","composition","front-page"]},{"slug":"the-heavy-months-ranked","factors":["share-of-the-window","the-llm-regulars"],"title":"The heavy months, ranked, with their tier share","problem":"The share window says what each of the twenty months contributed to the whole; the HAVING gate keeps the groups that pass an aggregate condition. An analyst wants the heavy tier on its own: which months carried at least 7,000 LLM documents, in rank order, and how the tier splits among them. A gate needs the window to rank the survivors, and the window needs the gate to define the tier.","sql":"SELECT toStartOfMonth(original_timestamp) AS m, count() AS n, round(100 * n / sum(n) OVER (), 1) AS share_pct,\n       rank() OVER (ORDER BY n DESC) AS rk\nFROM hackernews.items\nWHERE original_timestamp >= '2025-01-01' AND original_timestamp < '2026-09-01'\n  AND hasToken(search_text_lc, 'llm')\nGROUP BY m\nHAVING n >= 7000\nORDER BY rk ASC LIMIT 20\n","technique":"The window entry's projection is verbatim and gains a second window, rank() OVER (ORDER BY n DESC); the HAVING entry's gate moves from authors to months, n >= 7000. Order of evaluation is the point: ClickHouse runs HAVING before window functions, so sum(n) OVER () now sums the eight survivors, 67,339 documents, and share_pct reads as share of the tier: March 2026 is 14.7 percent of the tier where the window entry showed it as 7.5 percent of the twenty months. The gate admits exactly the eight months of 2026 and no month of 2025; those eight hold 51.1 percent of the window's 131,838 documents. rank() over n leaves the tier in order 9,907, 8,908, 8,546, 8,407, 8,342, 7,921, 7,679, 7,629. A gate on a windowed value would need QUALIFY, which the detector does not count; HAVING stays on the aggregate.","whyFast":"The same 6.2 M rows and 2.15 GB as the window entry and the time bucket. The gate runs over twenty aggregated rows, one comparison each, and the two windows then touch eight rows: one sum, eight divisions, one sort by n. The triple spanned 0.25 to 0.63 s against the window entry's 0.34 s, run-to-run variance on identical scan work.","measured":"0.57 s (median of three) over 20 months of hackernews.items, 6.2 M rows read (2026-09-07).","seconds":0.57,"readRows":6235069,"readBytes":2148875521,"relations":["hackernews.items"],"tags":["window","HAVING","composition","rank"]}]}