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
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?