Postgres - Get the query cost assign to a variable

Question

To get the estimated cost of the query, i using EXPLAIN SELECT column FROM table;, to get the current cost of the query, i am using EXPLAIN ANALYSE SELECT column FROM table;, my question is how to get the cost of the query automatically, without having to run the explain for each query manually.

I need something like:

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;

The idea is to create a script to check the querys in a log and run in psql, or copy the log querys to a table in the database and run with plain SQL to verify the most expensive ones, at the moment is just what i seek, no need to worry about the real cost of the query ( "cost" X "times executed per minute"), cost of INSERT, UPDATE, and DELETE among other things.

I hope this is possible, if not, there is another way to search for expensive query without checking one by one?

EDIT:

Forget to mention, i using Postgres 9.1.

Take a look at pg_stat_statements

Nick Barnes
– Nick Barnes

2016-09-27 21:34:32 +00:00
Commented Sep 27, 2016 at 21:34 — Nick Barnes
– Nick Barnes, Commented Sep 27, 2016 at 21:34

Laurenz Albe · Accepted Answer · 2016-09-28 10:21:58Z

7

Maybe you can create a function similar to the following:

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;$$;

You can use it like this:

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)

edited Sep 28, 2016 at 10:21

answered Sep 28, 2016 at 9:43

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Laurenz Albe Over a year ago

Fixed the function to use EXPLAIN (ANALYZE, FORMAT JSON).

Tiago Oliveira de Freitas Over a year ago

@ Laurenz Albe Forget to mention, i using Postgres 9.1. - ERROR: type "json" does not exist

Laurenz Albe Over a year ago

Then you'll have to use something akin to my previous version. PostgreSQL 9.1 is out of support as of now.

Community · Accepted Answer · 2017-04-13 12:59:31Z

0

Based in this answer [https://pt.stackoverflow.com/questions/155113][1] :

CREATE or REPLACE function custo_consulta(_consulta text[])
returns table (consulta text, custo_execucao numeric, tempo_execucao numeric ) as '
declare custo text;
begin
    foreach consulta in array _consulta loop
        execute ''EXPLAIN ( FORMAT JSON, ANALYZE ) '' || consulta INTO custo;
        custo_execucao := split_part(split_part(custo, ''"Total Cost": '', 2), '','', 1);
        tempo_execucao := split_part(split_part(custo, ''"Actual Total Time": '', 2), '','', 1);
        return next;
    end loop;
end;
' language plpgsql;

SELECT *
FROM custo_consulta(array['SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''OPORTUNIDADE''', 'SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''REVISAO'''])
ORDER BY custo_execucao desc;

Result

 consulta                                                               custo_execucao     tempo_execucao    
 SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'REVISAO'       38426,44           128,267           
 SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'OPORTUNIDADE'  38252,65           123,996

Just another problem, i am receiving an error when using $$, but this will be solved.

edited Apr 13, 2017 at 12:59

CommunityBot

11 silver badge

answered Sep 29, 2016 at 20:32

Tiago Oliveira de Freitas

5462 gold badges7 silver badges17 bronze badges

2 Comments

Laurenz Albe Over a year ago

This will only work for queries where the final step is an aggregate, but maybe that's all you need.

Tiago Oliveira de Freitas Over a year ago

@Laurenz Albe True, not realized this, I will try with JSON FORMAT

Collectives™ on Stack Overflow

Postgres - Get the query cost assign to a variable

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related