How to assign the query cost to a variable in Postgres

Asked

Viewed 3,263 times

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?

1 answer

2


I did passing an array of queries but can also be referring to a table of queries. Dynamic SQL.

create or replace function custo_consulta(_consulta text[])
returns table (consulta text, planejamento numeric, execucao numeric) as $$
declare custo text;
begin
    foreach consulta in array _consulta loop
        execute 'explain ' || consulta into custo;
        custo := split_part(split_part(custo, '(cost=', 2), ' rows=', 1);
        execucao := split_part(custo, '..', 1);
        planejamento := split_part(custo, '..', 2);
        return next;
    end loop;
end;
$$ language plpgsql;

select *
from custo_consulta(array['select 1','select 1/1'])
order by planejamento + execucao desc;
  consulta  | planejamento | execucao 
------------+--------------+----------
 select 1   |        0.001 |    0.002
 select 1/1 |        0.001 |    0.001
  • Great guy, it’s just what I want, I was coming up with a solution using ( FORMAT JSON ) and substring() to get the value, but it got very ugly the code, I just made some changes to your Function, first $, I don’t know why I’m always wrong, I have to fix this one time, according to the actual time is the execution time of the explain and it’s variable, depending on the server load can vary quite a bit, so I switched to get the cost, I’ll put as it was in the pegunta, thanks for the help.

  • @James if you will use only the estimated cost then enough explain. The explain analyze actually executes the query and incurs any cost of the query besides causing the effects of the query like insertion, deletion, Ocks, etc... The actual time is the time of consultation. The time of explain appears in the last two lines and includes the query time.

  • You’re right, I changed to fetch the "Current team", if you only need the cost I use without analyse, also edited to use JSON format so it does not depend on the "Aggregate" at the beginning, if you need "Startup Time" or any other data I will know how to search, thanks.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.