On Tue, May 19, 2020 at 4:29 AM Andy Fan <zhihui.fan1...@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 */ > > > 1. Usually I want to check total_exec_time / rows to see if the query is > missing > index, however aggregation/groupby case makes this rule doesn't work. so > hasagg/hasgroup should be a good rule to filter out these queries. > > 2. subplan is also a important clue to find out the query to turning. when we > check the slow queries with pg_stat_statements, such information maybe > helpful as well. > > 3. As for subquery / joincnt, actually it is just helpful for optimizer > developer to understand the query character is running most, it doesn't > help > much for user. > > > The attached is a PoC, that is far from perfect since 1). It maintain a > per-backend global variable query_character which is only used in > pg_stat_statements extension. 2). The 5 fields is impossible to change no > matter how many times it runs, so it can't be treat as Counter in nature. > However I don't think the above 2 will cause big issues. > > I added the columns to V1_8 rather than adding a new version. this can be > changed at final patch. > > Any suggestions?
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?
