The space of queries, on a clock

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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?

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.

why fast 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.

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.

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.

why fast 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.

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?

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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.

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.

why fast 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.

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?

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.

why fast 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.