additional dimensions

[ardentperf]

The only dimensions captured right now for wait events are pid and queryid. It would be helpful to capture additional dimensions for troubleshooting issues.

roleId and databaseId should be easy to grab from PGPROC while in pgws_should_sample_proc (corresponding with usesysid and datid in pg_stat_activity). lockGroupLeader->pid would be useful for sessions executing parallel queries.

There are also a number of fields from the backend status that would be very useful as dimensions: the application name, client addr/host/port and backend/bgworker type

Finally, (backend) proc_start_timestamp would be useful because technically this is needed together with pid to uniquely identify sessions (and disambiguate a new connection that reuses a PID)

And activity_start_timestamp would be useful to identify if the same query showing up in repeated samples is a single execution, or multiple executions of the same query

oh - and of course - the actual query text would be useful in some cases! (pgstat_clip_activity(beentry->st_activity_raw))

Might be able to copy code from pg_stat_get_activity() if needed

[Medvecrab]

Greetings

Those are very interesting suggestions, but currently we don't know how to add those columns (not in the implementation way). If we try to add those to profile we would break our aggregation in profile - instead of two dimensions we would have, like, 8-10 and our profile will have several orders of magnitude more rows in profile (with most of them having count=1)

[DmitryNFomin]

Hey everyone
that's interesting coincidence, I also wanted similar enhancement
to explain my idea better I created this PR #95

if you thing it's good idea, I would add code for history and probably profile

[ardentperf]

FWIW, I'm most interested in the history - sometimes pulling it out to an external system, sometimes directly analyzing it. So I don't feel like it's a requirement to add these fields to the profile.

[ardentperf]

also note that while it takes a very different approach to implementation, the extension https://github.com/pgsentinel/pgsentinel has all of these fields (and i have used that extension successfully)

[DmitryNFomin]

I've added logic for _history view to that PR

[Medvecrab]

We will add new columns that were discussed here (maybe all of them, maybe some of them, maybe also some additional ones) to pg_wait_sampling_current/_history/_profile ourselves after we decide on a way to turn them on/off if needed (probably some kind of GUC, but the details are still being worked on)

pgsentinel is an interesting extension, thanks for telling about it!

I will keep you updated

[Medvecrab]

@ardentperf, @DmitryNFomin, you are welcome to review my PR that adds new dimensions to pg_wait_sampling's views (for some details why not all fields that were suggested here are implemented look at the PR description) - #97