The concrete query is in the Gist here: https://gist.github.com/smarr/5a30c34cc38e274f634b151d9763f93c
On the partial table, an index on (runId, trialId, criterion, invocation) helps. Though, since this converts the full table, adding that index doesn't make any performance difference for the full conversion.
I added a comment on the Gist with the `EXPLAIN ANALYZE` output. It's the first time doing performance work on this type of stuff, so, many unknowns on my end....
Virtually all your time in that query is spent on the left join. Which leads me to two questions.
1) are you creating a a index on runId, trialId, criterion, invocation;
2) why are you using group by, its been a little bit since i had to write an sql statement but since you are creating a table im inclear why you have groupby in there.
@freemo@mastodon.acm.org
Sounds a lot less trivial than I first suspected considering the amount of work you did framing the question.. I am looking, the schema was very helpful thanks.
I was very good at this sort of problem back when i did it. Its been a few years, so I am a bit rusty. But I'll do my best to help.
It is late, so I dont expect a solution tonight, but if I have any insights ill let ya know. At first glance I fail to see why this is even difficult, which is likely a failing on my part.
@freemo the full schema is here:
https://github.com/smarr/ReBenchDB/blob/2414426bb93a9c170004a97207593217f02f622b/src%2Fbackend%2Fdb%2Fdb.sql#L152
ReBenchDB records benchmark results and provides customizable reporting to track and analyze run-time performance of software programs. - smarr/ReBenchDB
github.com