Search Postgresql Archives

Re: Analyzing performance regression from 9.2 to 9.6

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Dave Peticolas <dave@xxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux