We recently noticed that the build has been failing on-and-off since we added the share-represented query. It seems like it just takes much longer than others and so the connection would sometimes get interrupted and break everything. There might be some more we could do to prevent the connection issue, or handle errors and retry failed queries. But for now, I've just made an attempt to speed up that query. I did explain analyze and as expected the part taking all the time was the last aggregation by un-indexed column (day). So I split up the query and instead of all one CTE, I made a temporary table with the all_cases so we can add an index on those grouping column before the final aggregation query. This seems to speed it up a lot. Hopefully this does the trick!
We recently noticed that the build has been failing on-and-off since we added the share-represented query. It seems like it just takes much longer than others and so the connection would sometimes get interrupted and break everything. There might be some more we could do to prevent the connection issue, or handle errors and retry failed queries. But for now, I've just made an attempt to speed up that query. I did
explain analyzeand as expected the part taking all the time was the last aggregation by un-indexed column (day). So I split up the query and instead of all one CTE, I made a temporary table with theall_casesso we can add an index on those grouping column before the final aggregation query. This seems to speed it up a lot. Hopefully this does the trick!