Queryset explain can be truncated.

[Gordon Wrigley]

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.

[Mariusz Felisiak]

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

[Gordon Wrigley]

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.

[Mariusz Felisiak]

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

[Adam Johnson]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

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@…>

[Gordon Wrigley]

Thank you.