[PROPOSAL] Detecting plan changes with plan_id in pg_stat_activity

Adding a plan_id to pg_stat_activity allows users

to determine if a plan for a particular statement

has changed and if the new plan is performing better

or worse for a particular statement.

There are several ways the plan_id in pg_stat_activity

can be used:

1. In extensions that expose the plan text.

This will allow users to map a plan_id

from pg_stat_activity to the plan text.

2. In EXPLAIN output, including auto_explain.

3. In statement logging.

Computing the plan_id can be done using the same

routines for query jumbling, except plan nodes

will be jumbled. This approach was inspired by

work done in the extension pg_stat_plans,

https://github.com/2ndQuadrant/pg_stat_plans/

Attached is a POC patch that computes the plan_id

and presents the top-level plan_id in pg_stat_activity.

The patch still has work left:

- Perhaps Moving the plan jumbler outside of queryjumble.c?

- In the POC, the compute_query_id GUC determines if a

  plan_id is to be computed. Should this be a separate GUC?

-- Below is the output of sampling pg_stat_activity

-- with a pgbench workload running The patch

-- introduces the plan_id column.

select count(*),

   query,

   query_id,

   plan_id

from pg_stat_activity

where state='active'

and plan_id is not null and query_id is not null

group by  query, query_id, plan_id

order by 1 desc limit 1;

-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------

count    | 1

query    | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (7, 8, 242150, -1471, CURRENT_TIMESTAMP);

query_id | 4535829358544711074

plan_id  | -4913142083940981109

-- Also, a new view called pg_stat_statements_plan which

-- Includes all the same columns as pg_stat_statements, but

-- with statistics shown per plan.

postgres=# select substr(query, 1, 10) as query, queryid, planid, calls from pg_stat_statements_plan where queryid = 4535829358544711074;

-[ RECORD 1 ]-----------------

query   | INSERT INT

queryid | 4535829358544711074

planid  | -4913142083940981109

calls   | 4274428

-- the existing pg_stat_statements table

-- shows stats aggregated on

-- the queryid level. This is current behavior.

postgres=# select substr(query, 1, 10) as query, queryid, calls from pg_stat_statements where queryid = 4535829358544711074;

-[ RECORD 1 ]----------------

query   | INSERT INT

queryid | 4535829358544711074

calls   | 4377142

-- The “%Q” log_line_prefix flag will also include the planid as part of the output

-- the format will be "query_id/plan_id"

-- An example of using auto_explain with the ‘%Q” flag in log_line_prefix.

2022-06-14 17:08:10.485 CDT [76955] [4912312221998332774/-2294484545013135901] LOG:  duration: 0.144 ms  plan:

    Query Text: UPDATE pgbench_tellers SET tbalance = tbalance + -1952 WHERE tid = 32;

    Update on public.pgbench_tellers  (cost=0.27..8.29 rows=0 width=0)

      ->  Index Scan using pgbench_tellers_pkey on public.pgbench_tellers  (cost=0.27..8.29 rows=1 width=10)

            Output: (tbalance + '-1952'::integer), ctid

            Index Cond: (pgbench_tellers.tid = 32)

-- the output for EXPLAIN VERBOSE also shows a plan id.

postgres=# explain verbose select 1;

                QUERY PLAN

------------------------------------------

Result  (cost=0.00..0.01 rows=1 width=4)

   Output: 1

Query Identifier: -2698492627503961632

Plan Identifier: -7861780579971713347

(4 rows)

Thanks,

Sami Imseih

Amazon Web Services