django-debug-toolbar: Some SQL queries make debug toolbar rendering very slow

Hi, I recently migrated to the last version of django debug toolbar (3.1.1) and I experienced one major performance issue.

When a view makes a “huge” (in textual form) SQL request, rendering of toolbar is very, very slow. The view takes around 1s to render, and the toolbar around 58-59s on my machine.

The problem is kind of solved by commenting stack.enable_grouping(), in function parse_sql of panels/sql/utils.py (line 34).

[EXAMPLE] This is the kind of request that’s causing the problem:

SELECT ••• FROM "sample" LEFT OUTER JOIN "sample_parents" ON ("sample"."id" = "sample_parents"."from_sample_id") INNER JOIN "sample_parents" T4 ON ("sample"."id" = T4."to_sample_id") WHERE T4."from_sample_id" IN (856, 858, 860, 862, 900, 1154, 1155, 1156, [...more than 2K numbers...], 29583) GROUP BY ("sample_parents"."from_sample_id"), "sample"."id"

I know the problem is linked to sqlparse package, but disable grouping (or, make it as an option) doesn’t make a big difference in results presentation.

Thank you!

Configuration:

Python 3.7.9
Django-debug-toolbar 3.1.1
PostgreSQL 12
sqlparse 0.4.1

About this issue

Original URL
State: open
Created 4 years ago
Reactions: 1
Comments: 17 (11 by maintainers)

Most upvoted comments

This should be improved by #710

tim-schilling on Jan 18, 2024

That seems reasonable. Would you mind creating a PR?

As an aside, you can improve your queries performance (and the toolbars) if you can adjust your Sample queryset such that the

to_sample_ids = list(Sample.objects.filter(some_filtering=values))
Sample.objects.filter(from_sample__to_sample__id__in=to_sample_ids)

To:

Sample.objects.filter(from_sample__to_sample__some_filtering=values)

PostgreSQL queries that use massive IN collections perform poorly. Converting the query to use a subquery that fetches those ids will significantly improve the performance of your query.

tim-schilling on Nov 19, 2020