Opened 9 months ago

Closed 8 months ago

Last modified 8 months ago

#35294 closed Bug (fixed)

Queryset explain can be truncated.

Reported by: Gordon Wrigley Owned by: Adam Johnson
Component: Database layer (models, ORM) Version: 4.2
Severity: Normal Keywords: explain
Cc: Gordon Wrigley Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Django 4.2.10
Python 3.10.13
Postgres 15.6
Psycopg2 2.9.9

I have some very complex querysets I'm trying to optimize and what I've run into is explain output is being truncated at 100 lines. As far as I can tell this is because of this function on django.db.models.sql.compiler.SQLCompiler.

    def explain_query(self):
        result = list(self.execute_sql())
        # Some backends return 1 item tuples with strings, and others return
        # tuples with integers and strings. Flatten them out into strings.
        format_ = self.query.explain_info.format
        output_formatter = json.dumps if format_ and format_.lower() == "json" else str
        for row in result[0]:
            if not isinstance(row, str):
                yield " ".join(output_formatter(c) for c in row)
            else:
                yield row

Where result[0] is ignoring additional results. Monkey patching it to

    def explain_query(self):
        results = list(self.execute_sql())
        # Some backends return 1 item tuples with strings, and others return
        # tuples with integers and strings. Flatten them out into strings.
        format_ = self.query.explain_info.format
        output_formatter = json.dumps if format_ and format_.lower() == "json" else str
        for result in results:
            for row in result:
                if not isinstance(row, str):
                    yield " ".join(output_formatter(c) for c in row)
                else:
                    yield row

Gets me the full explain output.

Change History (10)

comment:1 by Gordon Wrigley, 9 months ago

Type: UncategorizedBug

comment:2 by Mariusz Felisiak, 9 months ago

Interesting, do you observe the same with psycopg 3+?

comment:3 by Gordon Wrigley, 9 months ago

Yes, although I don't know if I did it right. I'm not clear where the system is getting psycopg2 from. I installed 3 and nothing changed. But I don't know if at that point it was using 2 or 3.

Last edited 9 months ago by Gordon Wrigley (previous) (diff)

comment:4 by Mariusz Felisiak, 9 months ago

Summary: Queryset explain truncatedQueryset explain can be truncated.
Triage Stage: UnreviewedAccepted

Seems valid. Would like like to prepare a patch? (a regression test can be tricky.)

comment:5 by Adam Johnson, 9 months ago

Has patch: set
Owner: changed from nobody to Adam Johnson
Status: newassigned

comment:6 by Adam Johnson, 9 months ago

I made a PR but the test is ugly and a bit hacky to force a 100+ line explain plan by joining 100 1-row generate_series(). Happy to hear any alternatives.

comment:7 by Mariusz Felisiak, 9 months ago

Patch needs improvement: set

comment:8 by Mariusz Felisiak, 8 months ago

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 8 months ago

Resolution: fixed
Status: assignedclosed

In cbf1e87:

Fixed #35294 -- Fixed TEXT format of QuerySet.explain() for long plans.

co-authored-by: Gordon <gordon.wrigley@…>
co-authored-by: Simon Charette <charette.s@…>

comment:10 by Gordon Wrigley, 8 months ago

Thank you.

Note: See TracTickets for help on using tickets.
Back to Top