pg_stat_statements HLD for futur developments

Hello Kackers,

As a new user of PostgreSQL, I have started using pg_stat_statements, and
was pleased but a little surprised:

First of all, the normalized form of the query string makes it impossible to
be used in EXPLAIN commands.
Second, normalized constants and parameters values where missing to be able
to test optimizations results manually with EXPLAIN.
Third, execution plan was not available (making usage of AUTO_EXPLAIN
mandatory)

I searched a pg_stat_statements.normalized = false GUC
that isn't available yet
nb: that would be easy to implement by changing two lines in
pg_stat_statements.c
line 820 // JumbleQuery(&jstate, query);
line 1156 // if (queryId == UINT64CONST(0))

I have also looked at other developments turning arround this subject (see
the following list),
and found (so) many things, that wounder if a High Level Design was
available somewhere
that would permit to make all those branches mergeable ...

Would'nt a view like:

PK
dbid,
userid,
queryid,
+planid

Added columns
first created
last_updated
planing_time
first plan
first query parameters
...

permits to answer all current needs ( and still be compatible with an
aggregating tool) ?

additional GUCs are to be defined :
- normalized query (true,false),
- plan (none, text, json, ...),
- query parameters (none, text, json, ...),
- query_max_size
- plan_max_size
- parameter_max_size
...

I can try to maintain this design if desired.

Regards
PAscal

Discussions/custom extensions regarding p_stat_statements:

"[FEATURE PATCH] pg_stat_statements with plans"
http://www.postgresql-archive.org/FEATURE-PATCH-pg-stat-statements-with-plans-td5940964.html

"Sample values for pg_stat_statements"
http://www.postgresql-archive.org/Sample-values-for-pg-stat-statements-tc5998728.html
--> will not work for good and bad plans found before

There was an idea about collecting created and last updated times
"[PROPOSAL] timestamp informations to pg_stat_statements"
http://www.postgresql-archive.org/PROPOSAL-timestamp-informations-to-pg-stat-statements-td5912306.html
--> this is partly done here (but only for good or bad plan, one time)

"Planning counters in pg_stat_statements"
http://www.postgresql-archive.org/Planning-counters-in-pg-stat-statements-td5990933.html
--> this could be interesting for all kinds of plans

custom extension pg_stat_plan
https://github.com/2ndQuadrant/pg_stat_plans

custom extension pg_store_plans
https://github.com/ossc-db/pg_store_plans

...

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html