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

From: Julian Markwort <julian(dot)markwort(at)uni-muenster(dot)de>
To: Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andres Freund <andres(at)anarazel(dot)de>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, David Steele <david(at)pgmasters(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, <marius(dot)timmer(at)uni-muenster(dot)de>, <arne(dot)scheffer(at)uni-muenster(dot)de>
Subject: Re: [HACKERS] [FEATURE PATCH] pg_stat_statements with plans (v02)
Date: 2018-03-20 21:25:01
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:
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!

Attachment Content-Type Size
pgss_plans_v04.patch text/x-patch 34.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Merlin Moncure 2018-03-20 21:28:04 Re: INOUT parameters in procedures
Previous Message Merlin Moncure 2018-03-20 21:19:10 Re: INOUT parameters in procedures