I just realized I made a whitespace error when I modified the comments.
I hope I don't make a habit of sending erroneous mails.
Please accept my apologies, as well as a guaranteed whitespace-error-free patch (updated to version 5 for clarity).
Julian
Julian Markwort schrieb am 2018-03-20:
> To anyone who followed along with this for so long, I'd like to present my newest version of this patch.
> As suggested by Arthur Zakirov, there is now only a single GUC ( pg_stat_statements.plan_track ) responsible for the
selectionof the plans that should be tracked. Possible values are: all, none, good, or bad.
> I've mostly copied functionality from pl_handler.c . This resulted in the need to include varlena.h so I could use
theSplitIdentifierString() function to parse the values, of which several (e.g. pg_stat_statements.plan_track='good,
bad')could be used.
> I've also added a new GUC:
> pg_stat_statements.plan_fence_factor
> This GUC can be used to scale the fences of the interval, outside of which a plan might be updated.
> Right now, it is set to 1.5 (common factor for the definition of outliers in boxplots) and you can see through
additionalcolums in the pg_stat_statements view, how often these fences are surpassed by execution times and how often
theplans are updated. (The colums are: good_plan_outliers, good_plan_updates, bad_plan_outliers, bad_plan_updates and
areprimarily here for testing and review purposes and are not essential to this patch, they probably don't add any
valuefor the average user)
> Similarly to the first suggestion by Arthur, I've also changed the plan_reset functionality - there is now only one
function,pg_stat_statements_plan_reset(queryid bigint), overloaded with (queryid bigint, plantype cstring) args, that
canbe used to remove both plans (when omitting the cstring) or either of them. The cstring argument accepts 'good' or
'bad'.
> I also added more comments to the estimations of the quartiles and the calculation of the fences.
> The performance impact lies now at 139312 vs 141841 tps, so roughly 1.78% slower than default pg_stat_statements.
> The fact that these results are a little worse than the previous iteration is due to some changes in the definition
ofthe fences which mistakenly calculated by adding the scaled interquartile distance to the mean, instead of adding it
tothe respective quartiles, which means that plan updates are triggered a little more often.
> For 4259631 transactions however, only 11 updates for the bad plans where triggered.
> I'm looking forward to your opinions!
> Julian