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

On Wed, Oct 14, 2020 at 10:40 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Wed, Oct 14, 2020 at 10:34:31PM +0800, Julien Rouhaud wrote:
> > On Wed, Oct 14, 2020 at 10:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >
> > > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > > Is there a measureable overhead when this is turned on, since it is off
> > > > by default and maybe should default to on.
> > >
> > > I don't believe that "default to on" can even be in the discussion.
> > > There is no in-core feature that would use this by default.
> >
> > If the 2nd patch is applied there would be pg_stat_activity.queryid
> > column, but I doubt that's a strong enough argument.
>
> There is that, and log_line_prefix, which I can imaging being useful.
> My point is that if the queryid is visible, there should be a reason it
> defaults to show empty.

I did some naive benchmarking. Using a custom pgbench script with this query:

SELECT *
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
ORDER BY 1 DESC
LIMIT 1;

I can see around 2% overhead (this query is reported with ~ 3ms
latency average). Adding a few joins, overhead goes down to 1%.
Adding on top of the join some WHERE and GROUP BY conditions, overhead
goes down to 0.2% (at that point average latency is around 9ms on my
laptop). So having this enabled by default is probably only going to
hit people with OLTP-style workload with a majority of queries running
in a couple of milliseconds or less, which isn't that uncommon.