将XML说明计划存储在PostgreSQL数据库中 [英] Store XML explain plan in PostgreSQL database

问题描述

如何在PostgreSQL数据库中存储XML解释计划(或任何其他格式)?

How can I store XML explain plan (or any other format) in PostgreSQL database?

测试数据:explain (verbose, format xml) select 1

用于存储结果的表:create table tmp.explain_plan_data (expp xml);

我的天真测试失败:

insert into tmp.explain_plan_data values (explain (verbose, format xml) select 1);

似乎explain不能在任何其他select语句中使用，以下语句也不起作用:

It seems that explain cannot be used in any other select statement, the following statement does not work either:

select * from (explain (verbose, format xml) select 1) a

我们正在使用PostreSQL 9.1.6

We are using PostreSQL 9.1.6

推荐答案

无法使用子查询捕获EXPLAIN输出，您应该使用PL/pgSQL过程:

It is not possible to capture EXPLAIN output using subqueries, you should go for a PL/pgSQL procedure:

CREATE OR REPLACE FUNCTION explain_v_xml(in_sql text)
RETURNS TABLE(explain_line xml) AS $explain_v_xml$
BEGIN
    RETURN QUERY EXECUTE 'EXPLAIN (VERBOSE, FORMAT xml) '||in_sql;
END;
$explain_v_xml$ LANGUAGE plpgsql;

现在您可以像这样查询它:

Now you can query it like this:

SELECT explain_line FROM explain_v_xml('SELECT * FROM pg_locks');

并插入到目标表中

INSERT INTO tmp.explain_plan_data SELECT explain_v_xml('SELECT 1');
SELECT * FROM tmp.explain_plan_data;

也许仅在表中解释输出不是那么方便，我宁愿添加 原始查询和timestamptz插入内容.

Perhaps explain output alone in the table is not so handy, I would rather added original query and timestamptz of the insert.

这篇关于将XML说明计划存储在PostgreSQL数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！