PostgreSQL 9.2 – Get Query Cost and Assign to Variable

plpgsqlpostgresqlpostgresql-9.2

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?

Best Answer

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.

Related Question