How to assign query cost to a variable in Postgres

Question

How to assign query cost to a variable in Postgres

Navigation

#1 by (2 votes)

0

To get the estimated cost of the query I use EXPLAIN SELECT coluna FROM tabela; , to get the current cost of the query I use EXPLAIN ANALYSE SELECT coluna FROM tabela; , my question is how to fetch the cost of the query automatically, without having to execute manually and get the value of eye.

Here's an example of a function:

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 log and execute in psql , or copy the queries of log to a table in the database and execute with plain sql and check the most expensive ones in moment is just what I'm looking for, then I worry about the actual cost of the query ( "cost" X "times executed in the time period" ), , INSERT and UPDATE among other things.

I hope this is possible, if it is not, is there any other way to look for costly queries without checking one by one?

Edited:

I forgot to inform, use Postgres 9.1.

SOLUTION:

Based on the answer from @Clodoaldo Neto:

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

You only have one more problem, you get an error using $$ and you would have to double the DELETE in queries before moving to function, but this should be fixed.

sql postgresql

asked by anonymous 26.09.2016 / 22:49

1 answer

How to put button next to two text fields Soon you need to stay on top of the menu but menu is not clickable

score 2 · Accepted Answer

I made passing an array of queries but could also be querying 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