@freemo @freemo@qoto.org

The concrete query is in the Gist here: gist.github.com/smarr/5a30c34c

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

Follow

@smarr

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

@smarr can you share with me the table schema, assuming im being helpful at all. If this isnt a problem you actually need to fix let me know. But if its a problem you need a hand on id be happy to dig deeper and see if i can help.

@smarr

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.

Sign in to participate in the conversation
Qoto Mastodon

QOTO: Question Others to Teach Ourselves
An inclusive, Academic Freedom, instance
All cultures welcome.
Hate speech and harassment strictly forbidden.