alphagov/whitehall — pull request #585 by tekin (2013-06-04 10:47:23+00:00)
Title: Front end performance

Some tweaks to improve front-end performance.

I've mainly targeted the low-hanging fruit, focusing on the half dozen slowest requests. For the most part, I've done two things:

* Added indexes where it will improve query performance
* Reduced the number of n+1 queries with smarter eager loading

There were also a couple of places where complex joins were resulting in efficient queries. Where possible, I've replaced these with something simpler.

My unscientific measurements of request time improvements:

GET /government/announcements
Before: 46 queries ~ 10 seconds
After: 24 queries ~ 1 second

GET /government/publications
Before: 81 queries ~ 8 seconds
After: 30 queries ~ 1.5 seconds

GET /government/statistics
Before: 74 queries ~ 4.7 seconds
After: 25 queries ~ 2 seconds

GET /government/organisations
Before: 506 queries ~ 3.8 seconds
After: 59 queries ~ 1.5 seconds

GET /government/ministers
Before: 120 queries ~ 2.8 seconds
After: 16 queries ~ 1.6 seconds

GET /government/consultations
Before: 78 queries ~ 3.7 seconds
After: 64 queries ~ 2 seconds

GET /government/policies
Before: 127 queries ~ 1.2 seconds
After: 59 queries ~ 1.5 seconds *

We could further to reduce the number of queries (specifically for `/government/policies` and `/government/consultations`) by refactoring `Whitehall::DocumentFilter::Rummager`. Currently, it loads editions using the base Edition class, meaning we cannot eager-load sub-class specific associations (e.g. Policy topics and Consultation responses). Having said that, eager-loading is subject to diminishing returns as more associations are joined into the single query so this may not give us a significant performance boost. It may make more sense to spend the time employing a digest-based caching strategy on those page elements that largely remain static so as to avoid the queries altogether.

* I suspect this request has been made slower because the `EXISTS` form for the organisations query is not as quick as the JOIN form when there are only a few editions of that particular type. In this case, there are only a couple of hundred editions of type "Policy". Also we cannot eager load Policy topics for the reasons stated above…