Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: legrand legrand <legrand_legrand(at)hotmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?
Date: 2020-03-08 14:26:44
Message-ID: 20200308142644.vlihk7djpwqjkp7w@nol
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, Jun 28, 2019 at 11:49:53AM -0700, Peter Geoghegan wrote:
> On Tue, Mar 19, 2019 at 12:38 PM legrand legrand
> <legrand_legrand(at)hotmail(dot)com> wrote:
> > Would it make sense to add it in auto explain ?
> > I don't know for explain itself, but maybe ...
>
> I think that it should appear in EXPLAIN. pg_stat_statements already
> cannot have a query hash of zero, so it might be okay to display it
> only when its value is non-zero.

I had forgotten about this. After looking at it, I can see a few issues.

For now post_parse_analyze_hook isn't called for the underlying statement, so
we don't have the queryid. And we can't compute the queryid for the underlying
query in the initial post_parse_analyze_hook call as we don't want the executor
to have a queryid set in that case to avoid cumulating counters for both the
explain and the query.

We could add an extra call in ExplainQuery, but this will be ignored by
pg_stat_statements unless you set pg_stat_statements.track to all. Also,
pgss_post_parse_analyze will try to record an entry with the normalized query
text if no one exists yet and if any constant where removed. The problem is
that, as I already mentioned in [1], the underlying query doesn't have
query_location or query_len valued, so the recorded query text will at least
contain the explain part of the input query.

[1] https://www.postgresql.org/message-id/CAOBaU_Y-y%2BVOhTZgDOuDk6-9V72-ZXdWccXo_kx0P4DDBEEh9A%40mail.gmail.com

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message James Coleman 2020-03-08 14:42:53 Re: Allow to_date() and to_timestamp() to accept localized names
Previous Message Dmitry Dolgov 2020-03-08 14:22:28 Re: Index Skip Scan