rjuju
commented
7 years ago Hi,
That's weird.
What version of powa-web are you using?
Could you compare what the grid displays with this query results? (you have to use your database name instead of DBNAME_HERE)
SELECT anon_1.queryid, powa_statements.query, sum(anon_1.calls) AS calls, sum(anon_1.runtime) AS runtime, sum(anon_1.shared_blks_read * block_size.block_size) AS shared_blks_read, sum(anon_1.shared_blks_hit * block_size.block_size) AS shared_blks_hit, sum(anon_1.shared_blks_dirtied * block_size.block_size) AS shared_blks_dirtied, sum(anon_1.shared_blks_written * block_size.block_size) AS shared_blks_written, sum(anon_1.temp_blks_read * block_size.block_size) AS temp_blks_read, sum(anon_1.temp_blks_written * block_size.block_size) AS temp_blks_written, sum(anon_1.runtime) / greatest(sum(anon_1.calls), 1) AS avg_runtime, sum(anon_1.blk_read_time) AS blks_read_time, sum(anon_1.blk_write_time) AS blks_write_time
FROM (SELECT CAST(current_setting('block_size') AS NUMERIC) AS block_size) AS block_size, (SELECT queryid, dbid, userid, datname, max(calls) - min(calls) AS calls, max(total_time) - min(total_time) AS runtime, max(shared_blks_read) - min(shared_blks_read) AS shared_blks_read, max(shared_blks_hit) - min(shared_blks_hit) AS shared_blks_hit, max(shared_blks_dirtied) - min(shared_blks_dirtied) AS shared_blks_dirtied, max(shared_blks_written) - min(shared_blks_written) AS shared_blks_written, max(temp_blks_read) - min(temp_blks_read) AS temp_blks_read, max(temp_blks_written) - min(temp_blks_written) AS temp_blks_written, max(blk_read_time) - min(blk_read_time) AS blk_read_time, max(blk_write_time) - min(blk_write_time) AS blk_write_time
FROM
powa_databases,
LATERAL
(
SELECT unnested.dbid, unnested.userid, unnested.queryid,(unnested.records).*
FROM (
SELECT psh.dbid, psh.userid, psh.queryid, psh.coalesce_range, unnest(records) AS records
FROM powa_statements_history psh
WHERE coalesce_range && tstzrange(now()-interval '1 hour', now(), '[]')
AND psh.dbid = powa_databases.oid
AND psh.queryid IN ( SELECT powa_statements.queryid FROM powa_statements WHERE powa_statements.dbid = powa_databases.oid )
) AS unnested
WHERE tstzrange(now()-interval '1 hour', now(), '[]') @> (records).ts
UNION ALL
SELECT psc.dbid, psc.userid, psc.queryid,(psc.record).*
FROM powa_statements_history_current psc
WHERE tstzrange(now()-interval '1 hour',now(),'[]') @> (record).ts
AND psc.dbid = powa_databases.oid
AND psc.queryid IN ( SELECT powa_statements.queryid FROM powa_statements WHERE powa_statements.dbid = powa_databases.oid )
) h
GROUP BY queryid, dbid, userid, datname
HAVING max(calls) - min(calls) > 0) AS anon_1 JOIN powa_statements ON powa_statements.queryid = anon_1.queryid AND powa_statements.userid = anon_1.userid AND powa_statements.dbid = anon_1.dbid
WHERE anon_1.datname = 'DBNAME_HERE' GROUP BY anon_1.queryid, powa_statements.query ORDER BY sum(anon_1.runtime) DESC
trourance
champeric
Hi, I have updated the powa archivist extension to 3.1.0 and I found this strange calculation result when I try to order by Avg Time. Most probably only the displayed value is wrong, because it puts it on top.