timescale.info#83 · Summarize PostgreSQL durations from subtree totals, batching mixed subtrees
- timeline-app
- pull request
Follow-up to #81 (issue #54): PostgreSQL duration summaries now use the interval tree's stored subtree totals, with mixed subtrees read in batches. The result is faster than #81's index-only query in every case measured. ## Why After #81, `oc_duration_overview` found each group's first start through the start index, then **counted the group row by row** (`count(*)`, `max(last_time)`). So it read every short duration in the view. The tree nodes already store subtree totals (`subtree_count`, `min_extent`, `max_extent`, `max_first`), but nothing used them. With very long rationals this dominated: 20,000 durations near 10^1200 with a 10^-1100 step took 2 s per view. The pre-#35 tree walk did use those totals, but it was replaced because **long durations mixed in among short ones** leave few subtrees made only of short durations, so the walk visited nearly every node, one query each. Measured against #81's query, it was 2–3× slower on mixed timelines. ## Change `oc_duration_overview` now combines the two: - **Subtree totals:** it walks the tree in time order, like the browser's `durationSummaries`. When a subtree's durations are all short, all inside the window, and its starts fit within the current group, it adds the subtree's count and latest end without reading it. - **Batched mixed subtrees:** a subtree of at most 256 entries that contains long durations is read with **one range scan over its starts**, using #81's `first_key` index, and grouped in memory, instead of node by node. - **Exact batch bounds:** a subtree holds exactly the durations between its nearest ancestors on either side in (start, ID) order. The walk carries those ancestor IDs down the stack, so a range scan never picks up a tied neighbour from outside the subtree. IDs are compared only where an ancestor's start equals the subtree's first or last start. - **No schema change:** the existing columns and the #81 key index are enough. Tag views pick the new function up automatically, as before. ## Measurements 20,000 durations, minimum of 5 runs; full range (200 groups) / 1% window. Both functions ran on the same database and returned identical rows. | timeline | #81 (index, row counts) | this PR | |---|---|---| | 0 … 20,000, step 1 | 183 / 132 ms | 89 / 21 ms | | epoch seconds, 1 ns apart | 133 / 86 ms | 105 / 15 ms | | 10^400 …, step 10^398 | 109 / 93 ms | 72 / 21 ms | | 10^400 + k (clustered) | 101 / 25 ms | 72 / 29 ms | | **10^1200 offset, 10^-1100 step** | **1,984 / 641 ms** | **346 / 81 ms** | | one long duration among short | 123 / 66 ms | 70 / 12 ms | | every 10th long | 111 / 56 ms | 83 / 4 ms | | every 2nd long | 72 / 27 ms | 52 / 3 ms | For comparison, the pre-#35 walk without batching took 278–702 ms on the full view of the mixed timelines. ## Tests - `test/summaries-equivalence-postgres.mjs` adds a **tied-starts** timeline: 3,000 durations, five per start, a third of them long, with IDs out of start order. It's larger than one batch, so batch reads have tied neighbours outside their subtree. 30 random windows and thresholds are compared with the pre-#35 traversal. Removing the ID comparisons at batch bounds makes it fail (`tied 84..226 by 16/3`). - All Postgres test files CI runs pass locally against the project's pgmp image, plus `files.mjs`, `test:postgres` and `npm test` (202).