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.
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 1) yes. there's an index. And the explain shows it's used with the index scan.
2) The group by is needed to get the iteration data together from which the array is created.
The long story is given here: https://stackoverflow.com/q/78130781/916546 with examples how the data looks like.
I currently have a database schema like the following table: CREATE TABLE Measures( expId SERIAL, iteration INT NOT NULL, value float4 NOT NULL, PRIMARY KEY(expId, iteration) ); So...
stackoverflow.com