Re: Planning counters in pg_stat_statements (using pgss_store)

On Tue, May 19, 2020 at 4:29 AM Andy Fan <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 */
>
>
> 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?