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}*';

Resources

• Documentation: The Perfetto Standard Library documentation is in perfetto-stdlib.md. Use this file as a reference to discover available modules, find schemas (columns and types) for specific tables or views, or determine the INCLUDE PERFETTO MODULE statements required before drafting SQL query.
• Execution Tool: Queries are executed using the official trace_processor wrapper script downloaded directly from Perfetto. Output is returned in pure CSV format.

Execution Protocol

You must follow these steps sequentially, mirroring a multi-agent pipeline:

Step 0: Tool Setup

Fetch the Wrapper: You must use the top level of the current project workspace (./trace_processor).

CRITICAL GUARDRAIL: NEVER use filesystem search tools (find, find_by_name, grep, dir /s, Get-ChildItem) across the home directory or workspace to locate trace_processor — unconstrained searches across entire workspaces will stop responding or time out.

Perform a direct file check at the top level of your workspace (e.g., ls trace_processor). If missing, download https://get.perfetto.dev/trace_processor directly into the root workspace (curl -LO), make it executable on macOS/Linux (chmod +x), and ensure trace_processor is added to .gitignore. Execute queries directly via ./trace_processor (on Windows, explicitly invoke python trace_processor).

Important: The file served at this URL is a ~10KB Python wrapper script. Don't assume the download failed because it is human-readable text. This is the intended behavior. This script handles lazy-loading the precompiled binary automatically on its first run. Use it directly.