0
To take the cost esteemed of the query use the EXPLAIN SELECT coluna FROM tabela;
, to take the cost current of the query use the EXPLAIN ANALYSE SELECT coluna FROM tabela;
, my doubt is how to search the cost of the query automatically, without having to run manually and pick up the eye value.
An example of how it would be:
DECLARE custo integer;
DECLARE maiorcusto integer;
DECLARE query text;
maiorcusto := 0;
i := 0;
query = '';
WHILE i < array_length( queries ,1) LOOP
custo := explain analyse queries[i];
IF custo > maiorcusto THEN
maiorcusto := custo;
query := queries[i];
END IF;
i := i+1;
END LOOP;
The idea is to create a script to find the queries in a log
and run on psql
, or copy the queries of log
to a table in the bank and run with plain sql
and check the most expensive, at the moment is just what I look for, then I worry about the real cost of the query ( "cost" X "times executed in the period of time" ), cost of INSERT
, UPDATE
and DELETE
among other things.
I hope this is possible, if it is not, there is another way to seek expensive queries without being checking one by one ?
Edited:
I forgot to tell you, I use Postgres 9.1.
SOLUTION:
Based on @Clodoaldo Neto’s reply :
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
There is only one problem, get error when using $$ and would have to duplicate the '
queries before moving to Function, but this should be corrected.
How much $$ error are you using a driver? JDBC?
– Clodoaldo Neto