[metrics]: Added index for android_monitor_contention table

There are some absurd traces with ~50k monitor contentions. Added
an index on blocking_method, blocking_utid, and ts to improve
performance on all the threads blocked on the the same method.

On the problematic trace it took query performance from >10mins to ~5s

Test: tools/diff_test_trace_processor.py out/android/trace_processor_shell --name-filter '.*monitor.*'
Bug: 2647440
Change-Id: I34c0759c69970235714c64f6cb6081f14bc82807

1 files changed, 4 insertions, 1 deletions

diff --git a/src/trace_processor/perfetto_sql/stdlib/android/monitor_contention.sql b/src/trace_processor/perfetto_sql/stdlib/android/monitor_contention.sql
index 9fae0c7e7..41c0097d4 100644
--- a/src/trace_processor/perfetto_sql/stdlib/android/monitor_contention.sql
+++ b/src/trace_processor/perfetto_sql/stdlib/android/monitor_contention.sql
@@ -247,6 +247,9 @@ SELECT parent.id AS parent_id, child.* FROM android_monitor_contention child
 LEFT JOIN android_monitor_contention parent ON child.blocked_utid = parent.blocking_utid
     AND parent.ts BETWEEN child.ts AND child.ts + child.dur;
 
+CREATE INDEX internal_android_monitor_contention_chain_idx
+  ON android_monitor_contention_chain (blocking_method, blocking_utid, ts);
+
 -- First blocked node on a lock, i.e nodes with |waiter_count| = 0. The |dur| here is adjusted
 -- to only account for the time between the first thread waiting and the first thread to acquire
 -- the lock. That way, the thread state span joins below only compute the thread states where
@@ -256,7 +259,7 @@ CREATE VIEW internal_first_blocked_contention
   AS
 SELECT start.id, start.blocking_utid, start.ts, MIN(end.ts + end.dur) - start.ts AS dur
 FROM android_monitor_contention_chain start
-JOIN android_monitor_contention_chain END
+JOIN android_monitor_contention_chain end
   ON
     start.blocking_utid = end.blocking_utid
     AND start.blocking_method = end.blocking_method