1498644 - When loading treeherder with 'revision' property, it's very slow

Status: NEW

(Tree Management :: Treeherder: API, defect, P3)

Description

[Cameron Dawson [:camd]]

It takes over 6000ms to load pushes with the ``revision`` property set, as opposed to 400ms to get the latest 10 pushes.  The Push.revision field needs an index.

Comment 1

[Ed Morley [:emorley]]

It's probably the LIKE that's causing the issue (see also bug 1306707)

Comment 2

[Cameron Dawson [:camd]]

Oh right, yeah.  That's true.  I think it's probably both, though.  Once we eliminate the 12 char support, then adding an index would still be good.

Comment 3

[Ed Morley [:emorley]]

Ah so part of the problem is that the queries shouldn't be using the `commit` table (which doesn't have an index), but instead the `push` table, which has a composite index on the revision column. Making this change would mean it's no longer possible to query a push by requesting an arbitrary revision partway through the push range, but I think that's not really something that's used often, or that we need to support :-)

Comment 4

[Ed Morley [:emorley]]

Whilst bug 1306707 improved the performance here a reasonable amount, looking at profiles I see a large amount of time being spent in multiple SELECTs on the commit table (particularly for merge pushes on eg mozilla-central or mozilla-beta that have hundreds or thousands of commits). It looks like a select_related might help here.

Alternatively we could further limit the number of commits we store in Treeherder per push (currently 200), given that we already limit the commits to 20 when accessed via the API:
https://github.com/mozilla/treeherder/blob/870b24438eeae9eeeaf33bd4871a485ffd299aa5/treeherder/webapp/api/serializers.py#L239-L243

Doing so might also mean we no longer need to do the "query hg.mozilla.org for more commit metadata" step when we receive a push pulse payload, since the pulse payload already contains the first N commits, which might be more than we need:
https://github.com/mozilla/treeherder/blob/870b24438eeae9eeeaf33bd4871a485ffd299aa5/treeherder/etl/push_loader.py#L235-L257