I noticed that queries made by Patchwork can be pretty slow on larger databases. This PR changes two larger things that seem to cause performance issues.
I split the query in generic_list into a filtering part and an aggregation part. In the first part, only patch ids are queried. Then, another query is made to retrieve more detailed patch data. I'm not 100% sure, but it seems that MySQL and Postgres may compute all the fields (including JOINs) even for rows that don't make it into the final result, which negatively impacts performance. I added a new index. The first three fields of the index are fields that the default patch view uses to filter patches - project_id, archived and state. The fourth field is the field used for ordering the patch view - date, sorted descending (unlike in the current covering index). I'm 100% sure that project_id should be the first field in the index, and that date should be after fields most commonly used in filters. In my tests, I have noticed substantial performance improvements on large databases (>1 million patches), on a low-end machine (~2018-laptop tier, 16GB RAM, 2 cores, 2 threads, SSD) on both Postgres and MySQL. I can share more details about my tests, but to keep it short: on main, some queries would take 5+ seconds (average on MySQL was 2 seconds, on Postgres some would pretty much cause a timeout), with these patches the average is <300 ms. (I tested with default date descending ordering, on 5 scenarios: default filters, filtering by submitter, delegate, submitter and delegate, state. I did not do stress-tests, just "one-shot"). Query plans also became shorter, and in some cases were replaced by a straightforward index lookup. Franciszek Stachura (4): paginator: Remove long_page property views: Optimize patch list views: Add a new covering index for patch table views: Limit max number of items per page to 1000 .../0049_patch_patch_covering_idx_default.py | 28 +++++++++++++++++++ patchwork/models.py | 16 +++++++++++ patchwork/paginator.py | 6 +--- patchwork/settings/base.py | 2 ++ .../patchwork/partials/patch-list.html | 2 +- patchwork/views/__init__.py | 23 ++++++++++++--- 6 files changed, 67 insertions(+), 10 deletions(-) create mode 100644 patchwork/migrations/0049_patch_patch_covering_idx_default.py -- 2.55.0 _______________________________________________ Patchwork mailing list [email protected] https://lists.ozlabs.org/listinfo/patchwork
