Tiago Oliveira de Freitas Tiago Oliveira de Freitas - 2 months ago 11
SQL Question

Postgres - Get the query cost assign to a variable

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.

Answer

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

CREATE or REPLACE function custo_consulta(_consulta text[])
returns table (consulta text, execucao numeric) as '
declare custo text;
begin
    foreach consulta in array _consulta loop
        execute ''explain analyze '' || consulta into custo;
        custo := split_part(split_part(custo, ''Aggregate  (cost='', 2), '' rows='', 1);
        execucao := split_part(custo, ''..'', 2);
        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 execucao desc;

Result

consulta                                                               planejamento     execucao    
---------------------------------------------------------------------  ---------------  ----------- 
SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'OPORTUNIDADE'  (null)           38252,86    
SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'REVISAO'       (null)           38426,65  

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

Comments