mhauru
commented
1 year ago The query is already made faster as an accidental side effect of some restructuring of the SQLAlchemy query code. First thing to do here is to check if this is still necessary.
Open mhauru opened 2 years ago
mhauru
commented
1 year ago The query is already made faster as an accidental side effect of some restructuring of the SQLAlchemy query code. First thing to do here is to check if this is still necessary.
The query that collects the harvest_table and parallel_axes data is slow. It's bearable now, because there's only so much data, but there's a nested loop join that I think scans over the whole trh data table for every crop batch to find the relevant rows for it's grow/propagation period. I ran some analysis here, although I don't know exactly what to conclude from it: https://explain.dalibo.com/plan/f1e32dcfa7d873d7#plan I also have a mitigation in place where I prefilter the TRH data to only include rows since we started getting crop data, but the usefulness of that will decrease quite quickly from now on (also it's a bit ugly).
Not sure how to best improve this. I would have hoped an index on the trh table would have helped, but seems not, or maybe I did it wrong. Or maybe we need to precompute some intermediate result as a materialised view. Could even precompute the whole harvest table.