Dave Peticolas <dave@krondo.com> writes:
> Hi, I am trying to analyze a performance regression from 9.2.21 to 9.6.3.
> The query and execution plans are below with 9.6.3 first.
Hm. Neither version is exactly covering itself with glory. I'm not sure
why 9.6 doesn't pick the same plan as 9.2, but given the planner's
estimate that the report_submission/report_skilltype join will produce
only one row, the difference in the estimated costs would be negligible.
Since that join is actually producing over a million rows, your chances
of getting a good plan hinge on improving that estimate.
> I realize the query itself is probably not great and would benefit from a
> different approach, but I'd like to know if there are 9.6 settings I should
> look into to get a better plan without changing the query if possible.
Doubt it. Am I right in guessing that report_submission.id is a
declared-not-null column, so that the join
FROM "report_skilltype" LEFT OUTER JOIN "report_submission" ON ("report_skilltype"."uuid" =
"report_submission"."skill_type_id") WHERE ... "report_submission"."id" IS NULL
should be understood as an anti-join? The planner doesn't get that
at the moment, for implementation reasons that needn't concern us here.
But it would get it if you said
WHERE ... "report_submission"."skill_type_id" IS NULL
i.e. constrain the join column to be null. Any chance of whacking your
ORM upside the head to the point where it would emit that?
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general