On 2020/05/22 15:10, Andy Fan wrote: > > > On Thu, May 21, 2020 at 3:49 PM Julien Rouhaud <rjuju123@gmail.com <mailto:rjuju123@gmail.com>> wrote: > > Le jeu. 21 mai 2020 à 09:17, Michael Paquier <michael@paquier.xyz <mailto:michael@paquier.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.fan1213@gmail.com <mailto:zhihui.fan1213@gmail.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