Re: [HACKERS] [FEATURE PATCH] pg_stat_statements with plans (v02)

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 selection of 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 the SplitIdentifierString() 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 additional colums in the pg_stat_statements view, how often these fences are surpassed by execution times and how often the plans are updated. (The colums are: good_plan_outliers, good_plan_updates, bad_plan_outliers, bad_plan_updates and are primarily here for testing and review purposes and are not essential to this patch, they probably don't add any value for 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 can be 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 of the fences which mistakenly calculated by adding the scaled interquartile distance to the mean, instead of adding it to the 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