#139 Saves right after a large write get bad plans from stale statistics
–
- github
- github/openchronology/timeline-app
- github/openchronology/timeline-app#139
- issue
- state:closed
Found while measuring #138. ## Problem Right after a large timeline is written in one go (a fork, a duplicate, a create or an import), PostgreSQL's statistics still count that timeline's live rows (`oc_moments`, `oc_nodes`, and the others) as almost none. Autovacuum doesn't refresh them soon: on a large table, 200,000 new rows are well under its 10% threshold. Several save-path statements look rows up one key at a time with `CROSS JOIN LATERAL (SELECT … WHERE timeline_id=$1 AND id=k.id)`. The planner flattens these subqueries into joins. With the stale estimate, it then reads every live row of the timeline once (as a hash) or once per key. Measured on a fresh 200,000-moment fork, the existing lookup of saved moments in `server/incremental.mjs` (`applyIncrementally`) took: - 0.7–1.8 s for 10 keys; - 0.4–0.6 s for 5,000 keys. The same save on a timeline with current statistics takes milliseconds. `server/history.mjs` had the same problem: one 10-moment save on a fork hit the 30 s statement timeout. #138 fixed that by fencing its lookups with `OFFSET 0`, which keeps each one an index lookup per key. ## Affected statements Lateral lookups by key in `server/incremental.mjs`: - saved moments; - duration nodes; - moment times; - node paths; - removed moments; - relationships by entity. Other single-key lookups that get flattened into joins may be affected too. ## Options 1. **Fence each lookup** with `OFFSET 0` (as `liveAt` in `history.mjs` does). This is local and doesn't depend on statistics. A save touches at most a page of keys, so index lookups are always a reasonable plan. 2. **`ANALYZE` the live tables after a large whole-timeline write** in `store.replace` (for example, more than 20,000 entities written). `history.mjs` already does this for the history tables. It fixes every later plan, including the tree path query, at a cost of roughly 0.1–0.5 s per table. Both are probably worth doing: (1) for robustness, (2) so the other queries get good plans too. ## Done when - A 10-moment save made right after forking a 200,000-moment timeline takes about as long as one on a timeline with current statistics. - A benchmark or test checks this (the fork measurement from #138 could become a `bench/` script). Related: #135. https://github.com/openchronology/timeline-app/issues/139
Related
- #135 Scale saves and whole-timeline work toward very large timelines (1 billion moments)
- #138 Share saved history between timelines and their forks
- #140 Copy a fork's index in the database instead of rebuilding it in Node
- #143 Keep saves fast right after a large timeline is written
- #149 Forks read through to their source's index (#145, phase 2)
- Keep saves fast right after a large timeline is written
Open #139 Saves right after a large write get bad plans from stale statistics in the timeline