perfetto-sql

Guidelines and Hints

• Idempotency: Ensure queries are idempotent to prevent "already exists" errors during multiple executions.

  • For Perfetto objects, always use CREATE OR REPLACE: CREATE OR REPLACE PERFETTO TABLE, CREATE OR REPLACE PERFETTO VIEW, CREATE OR REPLACE PERFETTO FUNCTION, CREATE OR REPLACE PERFETTO MACRO.
  • For SQLite Virtual Tables (such as SPAN_JOIN), CREATE OR REPLACE is not supported. Explicitly drop them first: DROP TABLE IF EXISTS my_table; CREATE VIRTUAL TABLE my_table USING SPAN_JOIN(...);
  • For standard SQLite indexes, prepend DROP INDEX IF EXISTS index_name;.

• SPAN_JOIN will crash if intervals within the same input table overlap. Always use the PARTITIONED {column} (for example, PARTITIONED upid) clause to isolate intervals.

• Intermediate tables fed into a SPAN_JOIN must be materialized using CREATE PERFETTO TABLE, not CREATE VIEW.

• Trace Boundaries (dur = -1): Slices or thread states that don't finish before the trace ends are recorded with dur = -1. When calculating a bounding box (for example, ts + dur) or summing durations (SUM(dur)), handle incomplete durations using: IIF(dur = -1, trace_end() - ts, dur).

• Robust State Transitions: Avoid manual timestamp arithmetic (for example, ts + dur = next.ts) to join adjacent events. Rely on standard library modules (for example, sched.runnable, linux.perf.counters, intervals.overlap) which safely handle trace gaps and preemptions.

• Unique Identifiers: When writing SQL queries in Perfetto, you must join tables using utid (unique thread ID) or upid (unique process ID) instead of the regular tid or pid. Why it's useful : The operating system recycles TIDs and PIDs, while UTIDs and UPIDs remain unique for the lifetime of the trace, which prevents incorrect joins.

• Safe Argument Extraction: Use EXTRACT_ARG(arg_set_id, 'key') to extract dictionary or JSON-like properties from slices or tracks. Don't attempt string parsing.

• String Matching (Always use GLOB): Use GLOB instead of LIKE. LIKE causes performance bottlenecks and treats underscores (_) as wildcards, leading to bugs.

  • Exact matches: Use =.
  • Substring matches: Use GLOB with * (for example, name GLOB '*RenderThread*').
  • Case-insensitive matches: Use LOWER(name) GLOB and make sure the search string is fully lowercase (for example, LOWER(name) GLOB '*renderthread*'). Use this when dealing with inconsistent trace capitalization (for example, WakeLock versus wakelock).

• Calculating Time Overlaps: To calculate the overlap duration between two time intervals [start1, end1] and [start2, end2]:

  Precedence Rule: Always prefer using SPAN_JOIN or standard library functions (for example, intervals.overlap) to calculate overlaps between two different sets of intervals . Avoid manual arithmetic if a standard library feature or SPAN_JOIN can achieve the same result. Use the following logic if no built-in alternative exists.

  1. Condition: The intervals overlap if start1 < end2 and start2 < end1.

  2. Duration: The overlap duration is calculated as MIN(end1, end2) - MAX(start1, start2)

     Important: Incomplete Perfetto slices have a duration of -1 (dur = -1). Always calculate the effective end time using ts + IIF(dur = -1, trace_end() - ts, dur) before applying this logic.

• Query android_thread_slices_for_all_startups for app startup requests.

• Join counter_track with counter to get values of counter with a specific name.

• When querying for a CPU frequency counter, include the linux.cpu.frequency module and use the cpu_frequency_counters table.

• When looking for events around a specific timestamp, start with 100ms as the window size.

• Always prefix column names with table or view alias, that is: {alias}.{column_name}.

• To calculate the total time spent in slices matching a specific name pattern (for example, *{name_pattern}*), you must sum their durations. Why it's useful : This helps quantify the total impact of a specific function or feature on performance across multiple calls. Here is an example query (note the safe handling of incomplete slices): sql SELECT count(*) as total_count, sum(IIF(slice.dur = -1, trace_end() - slice.ts, slice.dur)) / 1000000.0 as total_dur_ms FROM slice WHERE slice.name GLOB '*{name_pattern}*';