On Fri, 2018-08-10 at 18:01 +1000, Stewart Smith wrote: > Django actively fights constructing a query that isn't insane. > > So, let's go and just execute a raw one. This is all very standard > SQL so should execute everywhere without a problem. > > With the dataset of patchwork.ozlabs.org, looking at the /project/ > page for qemu-devel would take 13 queries and 1500ms, > with this patch it's down to 11 queries in ~250ms. > For the dataset of the netdev list, it's down to 440ms from 1500ms. > > Signed-off-by: Stewart Smith <stew...@linux.ibm.com>
I'm generally reluctant to dive into SQL unless it's absolutely required. This is simple enough and the gains are significant so Reviewed-by: Stephen Finucane <step...@that.guru> Is this a potential bug report for Django? Stephen > --- > patchwork/views/project.py | 23 ++++++++++++++++++++--- > 1 file changed, 20 insertions(+), 3 deletions(-) > > diff --git a/patchwork/views/project.py b/patchwork/views/project.py > index 484455c02d9d..2a75242a06af 100644 > --- a/patchwork/views/project.py > +++ b/patchwork/views/project.py > @@ -27,6 +27,8 @@ from patchwork.compat import reverse > from patchwork.models import Patch > from patchwork.models import Project > > +from django.db import connection > + > > def project_list(request): > projects = Project.objects.all() > @@ -44,14 +46,29 @@ def project_list(request): > > def project_detail(request, project_id): > project = get_object_or_404(Project, linkname=project_id) > - patches = Patch.objects.filter(project=project) > + > + # So, we revert to raw sql because if we do what you'd think would > + # be the correct thing in Django-ese, it ends up doing a *pointless* > + # join with patchwork_submissions that ends up ruining the query. > + # So, we do not do this, as this is wrong: > + #patches = > Patch.objects.filter(patch_project_id=project.id).only('archived') > + #patches = patches.annotate(c=Count('archived')) > + # and instead do this, because it's simple and fast > + > + n_patches = {} > + with connection.cursor() as cursor: > + c = cursor.execute('SELECT archived,COUNT(submission_ptr_id) as c > FROM patchwork_patch WHERE patch_project_id=%s GROUP BY archived', > + [project.id]) > + > + for r in cursor: > + n_patches[r[0]] = r[1] > > context = { > 'project': project, > 'maintainers': User.objects.filter( > profile__maintainer_projects=project), > - 'n_patches': patches.filter(archived=False).count(), > - 'n_archived_patches': patches.filter(archived=True).count(), > + 'n_patches': n_patches[False], > + 'n_archived_patches': n_patches[True], > 'enable_xmlrpc': settings.ENABLE_XMLRPC, > } > return render(request, 'patchwork/project.html', context) _______________________________________________ Patchwork mailing list Patchwork@lists.ozlabs.org https://lists.ozlabs.org/listinfo/patchwork