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

On Thu, 2018-03-22 at 11:16 -0700, legrand legrand wrote:
> Reading other pg_stat_statements threads on this forum, there are
> also activ
> developments to add:
> - planing duration,
> - first date,
> - last_update date,

As I see it, planning duration, first date, and last update date would
be columns added to the pg_stat_statements view, i.e. they are tracked
for each kind of a (jumbled) query -- just as the good and bad plans,
their associated execution times and timestamps are.

> - parameters for normalized queries,

I've reviewed Vik Fearing's patch for this and have not heard back from
him. Also, as you have already explained in your summary post, these
parameters only aid in the examination of current plans and offers no
information regarding plans used in the past.

> I was wondering about how would your dev behave with all those new
> features.
> It seems to me that bad and good plans will not have any of thoses
> informations.

A patch that adds planning durations and timestamps associated with queries wouldn't interefere with my plans patch.
However, we could think about capturing the planning durations for the plans recorded by my patch.

The patch for tracking first-time parameters for normalized queries has
a different use case, compared to this patch. It shouldn't interfere
with my patch, anyway.

> Last question, didn't you think about a model to store all the
> different
> plans using a planid like
>
> queryid, planid, query, ...
> aaa plan1 ...
> aaa plan2 ...
> aaa plan3 ...
> ...
>
> I can not imagine that there would be so many of them ;o)

This wasn't obvious to me during development, as each entry (with a certain queryid) is directly connected to two plans.
But with future development in mind it probably makes sense to separate the plans from the rest of pg_stat_statements.

This would also allow us to keep old plans, while only storing new ones that are not equivalent, essentially providing a history of the plans used.
Keep in mind that this check for equivalence would require further development and we'd have to make sure we're not consuming too much memory (however much that is) when storing possibly infinite amounts of plans.

I've created a draft patch that provides access to plans in a view called pg_stat_statements_plans.
There is no column that indicates whether the plan is "good" or "bad", because that is evident from the execution time of both plans and because that would require some kind of plan_type for all plans that might be stored in future versions.

Please take it for a spin and tell me, whether the layout and handling of the view make sense to you.

Julian