如何在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数据库中
发布评论