Area selection aggregation recomputes the same base data several times
- Dominant language
- C++
- Stars
- 6.5k
- Forks
- 868
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 218
Description
Not an emergency, just tracking it. From a Ctrl-A (all tracks) area selection on
the Android example trace: one selection costs ~11.5s of TP time, much of it
spent materializing and interval-intersecting the same rows more than once.
Each aggregation tab is an independent `Aggregator` that builds its own
extraction + `_interval_intersect_single` temp tables. Nothing is shared, so
tabs reading the same base data pay for it repeatedly:
- **Thread state (~1.1s wasted):** `thread_state_aggregation` and
`thread_state_by_cpu_aggregation` each extract and interval-intersect the same
utid set. The by-CPU columns are a subset of the main table.
- **Sched (~0.2s):** `cpu_aggregation` and `cpu_by_process_aggregation` do the
same over the same ucpu set; by-process columns are a subset of by-thread.
- **Slice (~1s):** `slice_aggregation`'s self-time path and the flamegraph path
each windowed-extract the same slices. These are genuinely different consumers.
The slowest single query is separate: the slices pivot at 2.6s,
`SELECT name, sum(dur), count(1) FROM _viz_slices_for_ui_table ... GROUP BY name`.
`_viz_slices_for_ui_table` (`stdlib/viz/slices.sql`) is a view built from unions
of thread/process-track joins plus `slice JOIN track` with track anti-joins, so
`GROUP BY name` re-derives the whole tree every time and `ts+dur > START` isn't
sargable, so nothing prunes by time first.
Top offenders from the debug SQL performance view (IN-lists elided):
| runtime_ms | query |
| --- | --- |
| 2668 | `SELECT name, sum(dur), count(1) FROM _viz_slices_for_ui_table ... GROUP BY name` |
| 1920 | `create or replace perfetto table thread_state_aggregation as ...` |
| 829 | `create or replace perfetto table thread_state_by_cpu_aggregation as ...` |
| 822 | `__temp_dj6be... AS _interval_intersect_single!(..., __temp_597irv...)` (slice self-time) |
| 755 | `__temp_0jnl... AS _interval_intersect_single!(..., __temp_eo1rx...)` (slice flamegraph) |
| 590 | `__temp_o78tb... AS thread_state WHERE utid IN (...) ORDER BY id` (by_cpu extract) |
| 590 | `__temp_k36c3... AS thread_state WHERE utid IN (...) ORDER BY id` (thread_state extract) |
| 501 | `__temp_hulo4... AS _interval_intersect_single!(..., __temp_k36c3...)` |
| 494 | `__temp_tg24f... AS _interval_intersect_single!(..., __temp_o78tb...)` |
| 490 | `create or replace perfetto table cpu_aggregation as ...` |
| 417 | `create or replace perfetto table cpu_by_process_aggregation as ...` |
| 358 | `__temp_597irv... AS slice WHERE track_id IN (...) ORDER BY id` |
| 249 | `__temp_eo1rx... AS slice WHERE track_id IN (...) ORDER BY id` |
Contributor guide
Research direction
Start with the named aggregation entry points: thread_state_aggregation, thread_state_by_cpu_aggregation, cpu_aggregation, cpu_by_process_aggregation, and the slice self-time and flamegraph paths. Read stdlib/viz/slices.sql and use the debug SQL performance view to reproduce the Ctrl-A Android trace costs. Done means the repeated base-data work and slow slices pivot are addressed, with the listed runtimes rechecked.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, performance
- Issue type
- Refactor
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100