"Detect suboptimal and regressed plans" SQL is no longer valid in 23.2

[michae2]

Michael Erickson (michae2) commented:

The SQL query used in https://www.cockroachlabs.com/docs/stable/crdb-internal#detect-suboptimal-and-regressed-plans is no longer valid in 23.2. (crdb_internal.decode_plan_gist is a set-returning function which is no longer allowed directly inside string_agg, an aggregate function.)

How about something like this instead?

SELECT
  substring(statement, 1, 60) AS statement_text,
  plan_id,
  string_agg(plan_line, e'\n  ') AS plan,
  max(timestamp_interval),
  max(num_rows_read_mean),
  max(runtime_latency_mean)
FROM (
  SELECT
    metadata->>'query' AS statement,
    statistics->'statistics'->'planGists'->>0 as plan_id,
    max(aggregated_ts) as timestamp_interval,
    max(statistics->'statistics'->'rowsRead'->'mean') AS num_rows_read_mean,
    max(statistics->'statistics'->'runLat'->'mean') AS runtime_latency_mean
  FROM
    crdb_internal.statement_statistics
  WHERE
    metadata->>'query' LIKE 'SELECT %'
  GROUP BY
    metadata->>'query', statistics->'statistics'->'planGists'->>0
  ) AS x,
  LATERAL crdb_internal.decode_plan_gist(plan_id) AS y(plan_line)
GROUP BY
  statement, plan_id;

Jira Issue: DOC-9851

[michae2]

See also https://cockroachlabs.atlassian.net/browse/DOC-9850