Poor performance of '/api/events'

[Stephen Finucane]

On Fri, 2017-11-24 at 11:12 +1100, Daniel Axtens wrote:
> Stephen Finucane <stephen at that.guru> writes:
> 
> > I've noticed some poor performance for
> > 'patchwork.ozlabs.org/api/events' when
> > accessed via a browser or fetched via cURL. I haven't seen similar
> > issues on
> > any other endpoint and I also can't see anything funky going on
> > with regards to
> > number of database queries for this endpoint when running Patchwork
> > locally.
> > 
> > Has anyone else seen this issue? I'm away for the next two weeks
> > but if someone
> > has a chance to look at this in the interim I'd be very
> > appreciative.
> 
> Wheeee that does a *massive* database query:
> 	
> SELECT ... FROM "patchwork_event" INNER JOIN "patchwork_project" ON
> ("patchwork_event"."project_id" = "patchwork_project"."id") LEFT
> OUTER JOIN "patchwork_patch" ON ("patchwork_event"."patch_id" =
> "patchwork_patch"."submission_ptr_id") LEFT OUTER JOIN
> "patchwork_submission" ON ("patchwork_patch"."submission_ptr_id" =
> "patchwork_submission"."id") LEFT OUTER JOIN "patchwork_series" ON
> ("patchwork_event"."series_id" = "patchwork_series"."id") LEFT OUTER
> JOIN "patchwork_coverletter" ON ("patchwork_event"."cover_id" =
> "patchwork_coverletter"."submission_ptr_id") LEFT OUTER JOIN
> "patchwork_submission" T7 ON
> ("patchwork_coverletter"."submission_ptr_id" = T7."id") LEFT OUTER
> JOIN "patchwork_state" ON ("patchwork_event"."previous_state_id" =
> "patchwork_state"."id") LEFT OUTER JOIN "patchwork_state" T9 ON
> ("patchwork_event"."current_state_id" = T9."id") LEFT OUTER JOIN
> "auth_user" ON ("patchwork_event"."previous_delegate_id" =
> "auth_user"."id") LEFT OUTER JOIN "auth_user" T11 ON
> ("patchwork_event"."current_delegate_id" = T11."id") LEFT OUTER JOIN
> "patchwork_check" ON ("patchwork_event"."created_check_id" =
> "patchwork_check"."id") ORDER BY "patchwork_event"."date" DESC LIMIT
> 30
> 
> There is no universe in which a query with 11 JOINs will be fast.
> 
> I will see if that can be re-engineered to be faster and how breaking
> that would be.
> 
> Regards,
> Daniel

I've just posted a potential solution to this [1]. In addition, I've
opened a bug to track progress [2].

Stephen

[1] https://lists.ozlabs.org/pipermail/patchwork/2018-January/004754.ht
ml
[2] https://github.com/getpatchwork/patchwork/issues/153