sql - Postgres - 获取分配给变量的查询成本

Question

要获取查询的估计成本，我使用EXPLAIN SELECT column FROM table;，获取查询的当前成本 ，我正在使用 EXPLAIN ANALYZE SELECT column FROM table;，我的问题是如何自动获取查询的 cost，而不必运行 explain 手动为每个查询。

我需要这样的东西:

DECLARE cost integer;
DECLARE highercost integer;
DECLARE query text;

highercost := 0;
i := 0;
query = '';

WHILE i < array_length( queryarray ,1) LOOP

    cost := explain analyse queryarray[i];

    IF cost > highercost THEN

        highercost := cost;
        query := queryarray[i];

    END IF;

    i := i+1;

END LOOP;

想法是创建一个脚本来检查 log 中的查询并在 psql 中运行，或者将 log 查询复制到数据库中的表并使用 plain SQL 运行以验证最昂贵的那些，目前正是我所寻求的，无需担心查询的实际 cost ( "cost"X "times executed per minute")，INSERT、UPDATE、DELETE的开销其他事情。

我希望这是可能的，如果不可能，还有另一种方法可以搜索昂贵的查询而不用逐一检查吗？

编辑:

忘了说，我用的是 Postgres 9.1。

Answer 1

也许您可以创建一个类似于以下的函数:

CREATE OR REPLACE FUNCTION query_cost(
      queries text[],
      query OUT text, cost OUT float8, duration OUT float8
   ) RETURNS SETOF record LANGUAGE plpgsql STRICT AS
$$DECLARE
   i integer;
   p json;
BEGIN
   /* loop through input queries */
   FOR i IN array_lower(queries, 1)..array_upper(queries, 1) LOOP
      query := queries[i];
      /* get execution plan in JSON */
      EXECUTE 'EXPLAIN (ANALYZE, FORMAT JSON) ' || query INTO p;
      /* extract total cost and execution time */
      SELECT p->0->'Plan'->>'Total Cost',
             p->0->'Plan'->>'Actual Total Time'
         INTO cost, duration;
      /* return query, cost and duration */
      RETURN NEXT;
   END LOOP;
END;$$;

你可以这样使用它:

SELECT *
FROM query_cost(
        ARRAY[
           'SELECT 42',
           'SELECT count(*) FROM large'
        ]
     )
ORDER BY duration DESC;

┌────────────────────────────┬─────────┬──────────┐
│           query            │  cost   │ duration │
├────────────────────────────┼─────────┼──────────┤
│ SELECT count(*) FROM large │ 1693.01 │  150.171 │
│ SELECT 42                  │    0.01 │    0.002 │
└────────────────────────────┴─────────┴──────────┘
(2 rows)