Re: Planning counters in pg_stat_statements (using pgss_store)

On Fri, May 22, 2020 at 9:51 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
wrote:

>
>
> On 2020/05/22 15:10, Andy Fan wrote:
> >
> >
> > On Thu, May 21, 2020 at 3:49 PM Julien Rouhaud <rjuju123(at)gmail(dot)com
> <mailto:rjuju123(at)gmail(dot)com>> wrote:
> >
> > Le jeu. 21 mai 2020 à 09:17, Michael Paquier <michael(at)paquier(dot)xyz
> <mailto:michael(at)paquier(dot)xyz>> a écrit :
> >
> > On Thu, May 21, 2020 at 08:49:53AM +0200, Julien Rouhaud wrote:
> > > On Tue, May 19, 2020 at 4:29 AM Andy Fan <
> zhihui(dot)fan1213(at)gmail(dot)com <mailto:zhihui(dot)fan1213(at)gmail(dot)com>> wrote:
> > >> Thanks for the excellent extension. I want to add 5 more
> fields to satisfy the
> > >> following requirements.
> > >>
> > >> int subplan; /* No. of subplan in this query */
> > >> int subquery; /* No. of subquery */
> > >> int joincnt; /* How many relations are joined */
> > >> bool hasagg; /* if we have agg function in this query */
> > >> bool hasgroup; /* has group clause */
> > >
> > > Most of those fields can be computed using the raw sql
> satements. Why
> > > not adding functions like query_has_agg(querytext) to get the
> > > information from pgss stored query text instead?
> >
> > Yeah I personally find concepts related only to the query string
> > itself not something that needs to be tied to pg_stat_statements.
> > ...
> >
> >
> > Indeed cte will bring additional concerns about the fields
> semantics. That's another good reason to go with external functions so you
> can add extra parameters for that if needed.
> >
> >
> > There are something more we can't get from query string easily. like:
> > 1. view involved. 2. subquery are pulled up so there is not subquery
> > indeed. 3. sublink are pull-up or become as an InitPlan rather than
> subPlan.
> > 4. joins are removed by remove_useless_joins.
>
> If we can store the plan for each statement, e.g., like pg_store_plans
> extension [1] does, rather than such partial information, which would
> be enough for your cases?
>
> That would be helpful if I can search the interested data from it. Oracle
has
v$sql_plan, where every node in the plan has its own record, so it is easy
to search.

--
Best Regards
Andy Fan