1INCLUDE PERFETTO MODULE chrome.page_loads; 2INCLUDE PERFETTO MODULE sched.thread_level_parallelism; 3 4DROP VIEW IF EXISTS interesting_interval_with_overlaps; 5 6CREATE VIEW interesting_interval_with_overlaps 7AS 8WITH 9 offset(i, len) AS (VALUES(0, 1), (1, 2), (2, 3), (3, 5)), 10 INTERVAL AS ( 11 SELECT 12 id AS page_load_id, 13 navigation_start_ts AS ts, 14 navigation_start_ts, 15 0 AS lcp_offset, 16 lcp AS dur, 17 0 AS i, 18 0 AS len 19 FROM chrome_page_loads 20 WHERE lcp IS NOT NULL 21 UNION ALL 22 SELECT 23 page_load_id, 24 ts + dur AS ts, 25 navigation_start_ts, 26 lcp_offset + offset.len AS lcp_offset, 27 offset.len * 1000 * 1000 * 1000 AS dur, 28 i + 1, 29 offset.len 30 FROM INTERVAL, offset 31 USING (i) 32 ), 33 data AS ( 34 SELECT 35 ROW_NUMBER() OVER (ORDER BY page_load_id, len) AS interval_id, 36 *, 37 ts + dur AS END 38 FROM INTERVAL 39 ), 40 overlap AS ( 41 SELECT 42 *, 43 ( 44 SELECT MIN(other.interval_id) 45 FROM data AS other 46 WHERE 47 interval.interval_id 48 <> other.interval_id 49 AND other.ts < interval.end 50 AND interval.ts < other.end 51 ) AS first_overlap_interval_id 52 FROM data AS INTERVAL 53 ORDER BY 1, 2 ASC 54 ) 55SELECT 56 interval_id, 57 page_load_id, 58 IIF(len <> 0, PRINTF('lcp+%02d-lcp+%02d', lcp_offset - len, lcp_offset), 'NAV_START-lcp') 59 AS interval_name, 60 first_overlap_interval_id, 61 ts, 62 navigation_start_ts, 63 dur, 64 END 65FROM overlap; 66 67DROP VIEW IF EXISTS interesting_interval; 68 69CREATE VIEW interesting_interval 70AS 71SELECT 72 * 73FROM interesting_interval_with_overlaps 74WHERE page_load_id = (SELECT MAX(page_load_id) FROM interesting_interval_with_overlaps); 75 76DROP VIEW IF EXISTS interesting_slice_span_in; 77 78CREATE VIEW interesting_slice_span_in 79AS 80SELECT *, dur AS original_dur 81FROM slice; 82 83DROP TABLE IF EXISTS interesting_slice_span_internal; 84 85CREATE VIRTUAL TABLE interesting_slice_span_internal 86USING 87 SPAN_JOIN(interesting_slice_span_in PARTITIONED track_id, interesting_interval); 88 89DROP VIEW IF EXISTS interesting_slice_span; 90 91CREATE VIEW interesting_slice_span 92AS 93SELECT 94 interval_id, 95 ts, 96 dur AS span_dur, 97 original_dur, 98 name, 99 arg_set_id, 100 IIF(original_dur = 0, 1, 1.0 * dur / original_dur) AS span_ratio, 101 IIF(original_dur = 0, 0.0, 1.0 * thread_dur * dur / original_dur) AS thread_span_dur, 102 track_id 103FROM interesting_slice_span_internal; 104 105DROP VIEW IF EXISTS interesting_slice_start_in; 106 107CREATE VIEW interesting_slice_start_in 108AS 109SELECT ts, 0 AS dur, dur AS original_dur, name, arg_set_id, track_id 110FROM slice; 111 112DROP TABLE IF EXISTS interesting_slice_start_span; 113 114CREATE VIRTUAL TABLE interesting_slice_start_span 115USING 116 SPAN_JOIN(interesting_slice_start_in, interesting_interval); 117 118DROP VIEW IF EXISTS interesting_slice_start; 119 120CREATE VIEW interesting_slice_start 121AS 122SELECT interval_id, ts, original_dur, name, arg_set_id, track_id 123FROM interesting_slice_start_span; 124 125DROP VIEW IF EXISTS interesting_slice_end_in; 126 127CREATE VIEW interesting_slice_end_in 128AS 129SELECT ts + dur AS ts, 0 AS dur, ts AS original_ts, dur AS original_dur, name, arg_set_id, track_id 130FROM slice 131ORDER BY ts ASC; 132 133DROP TABLE IF EXISTS interesting_slice_end_span; 134 135CREATE VIRTUAL TABLE interesting_slice_end_span 136USING 137 SPAN_JOIN(interesting_slice_end_in, interesting_interval); 138 139DROP VIEW IF EXISTS interesting_slice_end; 140 141CREATE VIEW interesting_slice_end 142AS 143SELECT interval_id, ts AS end_ts, original_ts, original_dur, name, arg_set_id, track_id 144FROM interesting_slice_end_span; 145