sql - 获取postgresql EXPLAIN ANALYZE作为列

因此，如果我在postgresql上执行EXPLAIN ANALIZE查询，就会得到以下结果：
查询计划

Seq Scan on yellow_tripdata_staging (cost=0.00..3686590.00 rows=67978653 width=198) (actual time=0.009..307340.447 rows=92987719 loops=1)
Filter: ((passenger_count)::text = '1'::text)
Rows Removed by Filter: 38177324
Planning time: 0.306 ms
Execution time: 319613.033 ms

查询计划是列名，其他的是行，有没有办法在每一行都是列的情况下获取这些数据？
类似于：
过滤|删除的行|计划时间|执行时间|
材料| 38177324 |.0306 ms | 319613.03 ms|

最佳答案

create or replace function get_explain_as_json(text, out json) language plpgsql as $$
begin
  execute 'explain (analyse, verbose, format json) ' || $1 into $2;
  return;
end $$;

select
  j,
  j->0->>'Planning Time' as plan_time,
  j->0->>'Execution Time' as exec_time,
  j->0->'Plan'->>'Actual Rows' as total_rows
  -- And so on...
from get_explain_as_json('select 1 where 2 = 3') as j;

┌─────────────────────────────────────┬───────────┬───────────┬────────────┐
│                  j                  │ plan_time │ exec_time │ total_rows │
╞═════════════════════════════════════╪═══════════╪═══════════╪════════════╡
│ [                                  ↵│ 0.042     │ 0.026     │ 0          │
│   {                                ↵│           │           │            │
│     "Plan": {                      ↵│           │           │            │
│       "Node Type": "Result",       ↵│           │           │            │
│       "Parallel Aware": false,     ↵│           │           │            │
│       "Startup Cost": 0.00,        ↵│           │           │            │
│       "Total Cost": 0.01,          ↵│           │           │            │
│       "Plan Rows": 1,              ↵│           │           │            │
│       "Plan Width": 4,             ↵│           │           │            │
│       "Actual Startup Time": 0.002,↵│           │           │            │
│       "Actual Total Time": 0.002,  ↵│           │           │            │
│       "Actual Rows": 0,            ↵│           │           │            │
│       "Actual Loops": 1,           ↵│           │           │            │
│       "Output": ["1"],             ↵│           │           │            │
│       "One-Time Filter": "false"   ↵│           │           │            │
│     },                             ↵│           │           │            │
│     "Planning Time": 0.042,        ↵│           │           │            │
│     "Triggers": [                  ↵│           │           │            │
│     ],                             ↵│           │           │            │
│     "Execution Time": 0.026        ↵│           │           │            │
│   }                                ↵│           │           │            │
│ ]                                   │           │           │            │
└─────────────────────────────────────┴───────────┴───────────┴────────────┘

Links:
EXPLAIN
JSON operators

Update

To get the set of plans for the set of queries, one of the several possible solutions:

with queries(q) as (values(array[
  $$
    select 1 where 2 = 3
  $$,
  $$
    select 2 where 4 = 4
  $$
]::text[]))
select ... from queries, unnest(q) as q, get_explain_as_json(q) as j;

或者将其包装到函数中：

create or replace function get_explain_as_json(text[]) returns setof json language plpgsql as $$
declare
  q text;
  j json;
begin
  for q in select unnest($1) loop
    execute 'explain (analyse, verbose, format json) ' || q into j;
    return next j;
  end loop;
  return;
end $$;

with queries(q) as (values(array[
  $$
    select 1 where 2 = 3
  $$,
  $$
    select 2 where 4 = 4
  $$
]::text[]))
select ... from queries, get_explain_as_json(q) as j;

或者使用variadic参数（几乎与前面相同）：

create or replace function get_explain_as_json(variadic text[]) returns setof json language plpgsql as $$
declare
  q text;
  j json;
begin
  for q in select unnest($1) loop
    execute 'explain (analyse, verbose, format json) ' || q into j;
    return next j;
  end loop;
  return;
end $$;

select ...
from get_explain_as_json($$select 1 where 2 = 3$$, $$select 2 where 4 = 4$$) as j;

关于sql - 获取postgresql EXPLAIN ANALYZE作为列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45740226/