[Proposal] Improve greenplum diagnostics

[leborchuk]

Proposers

No response

Proposal Status

Under Discussion

Abstract

It's hard to understand what's going on in database - which query is executed, how many of them were processed. What is current execution plan. How many rows was processed on segment during query execution?

Motivation

We could easily add more observability to the database. Let's do it!

Implementation

1. pg_stat_activity show us the top query. Users execute complex PL/pgSQL functions so the whole execution time we see MY_IPORTANT_FUNC() in query field pg_stat_activity. We could add to pg_stat_activity

• executing_query - current executing query
• executing_query_start_time - current executing query start time
• executing_explain - current executing query explain plan
• query_explain - top query explain plan
  (explain or not could be set using GUC)

2. Do not aggregate in pg_stat_stamenets query with duration > N minutes (N controlled by GUC) - like in auto_explain but no need to read logs

3. Add explain plan and plan identificator to pg_stat_stamenets. There are a plenty of implementations - my variant as https://github.com/postgredients/pg_stat_query_plans

4. Start print out again "Rown in" and "Rows out" in explain (analyze, verbose). It was deleted in my-ship-it/gpdb@5ec5c30 and need set gp_enable_explain_allstat=on. No one know how to use it ) Lets print Rows in/Rows out as it was in GP5

Rollout/Adoption Plan

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!