Perfetto and the SQL processing built into trace_processor are both fantastic, but two of the most important parts of that SQL processing are not well documented, at least for my simple brain.
The first feature is SPAN_JOIN which is described as:
Span join is a custom operator table which computes the intersection of spans of time from two tables or views.
There is a nice diagram showing how the spans are combined:
But the part of the missing documentation here is the definition a span, which is a row in a table that has both ‘ts’ and ‘dur’ columns, where ‘ts’ is a timestamp, and ‘dur’ is the size of that span in the same units as ‘ts’.
Now if you have two tables (or views on a tables) that have ‘ts’ and ‘dur’ columns you can use SPAN_JOIN to create a new view that has all the intersecting spans.
For example, let’s look at one unit test from SPAN_JOIN:
If we have table
f with the following values:
s with the following values:
Then if we
SPAN_JOIN them together via:
CREATE VIRTUAL TABLE sp USING span_join(f, s);
The we’ll get the following values for view
But there are more than slices stored in Perfetto tables, what are we to with tables that are simply timestamped measurements?
That’s where the second feature comes in, SQLite LEAD, which allows you read values from the next row in the table.
To see this in action let’s say we had this table
We can create a
dur column in a view using LEAD:
CREATE TABLE f (ts BIG INT PRIMARY KEY, f_val BIG INT); INSERT INTO f VALUES (100, 44444), (110, 55555), (160, 44444), (200, 66666); CREATE VIEW f_slices AS SELECT ts, f_val, LEAD(ts) OVER ( ORDER BY ts ) - ts as dur FROM f; SELECT * FROM f_slices;
Running the above will generate the following table for the
And now since it has both
dur columns it can be used as a input to
One thing to note here is that the very last row has
NULL for its duration,
since there is no following row to compare against. You might need to filter out
NULL values depending later calculations.
There are plenty of other options for
LEAD, but hopefully with
this intro those explanations will make more sense.