information_schema/top_costly_queries.sql - serious duplications counting / summing happens

[Shai-Di]

Hey, just stumbled upon this repository, very useful idea - however, when looking at the results of this query we see heavy duplications occuring.
A query that was triggered 1,141 times in the past 14 days, appears in the result as if it was triggered over 1 million times.
The cost for that query alone results as if it was a 100 times greater than our total project cost, which definitley made no sense.

I believe the issue comes from the cross join between src1 and src2 resulting in many duplications for each job, but I hope I'm understanding the query correctly.

[nigelainscoe]

I've noticed that too. I ran it for a seven-day interval and it claimed we have spent a quarter million dollars. In fact, we have spent about a thousand.

I removed that join in the hashedQueries CTE, i.e. changing it to

hashedQueries AS (
    SELECT
      src.query AS query,
      src.hashed AS hashed,
      SUM(src.totalBytesBilled) AS totalBytesBilled,
      COUNT(src.hashed) AS queryCount
    FROM
      jobsDeduplicated as src
    GROUP BY
      hashed, query
)

And now the numbers look more like our actual numbers.

I'll do more testing and maybe submit a PR. I like the idea of having an open-source cost-reporting library like this but only if it is correct.

[xneg]

I rushed into changing the code before I discover this issue.
Nevertheless, here is my PR #18 that fixes the problem.