alberto-art3ch commented on PR #5898: URL: https://github.com/apache/fineract/pull/5898#issuecomment-4769667311
> One thing worth noting though: they resolve the originator list with a correlated subquery per row, while I was using a CTE with a single LEFT JOIN instead. Isn't it better to do it this way? Let me know your thoughts. Thanks for raising this — the CTE + LEFT JOIN is a clean, legitimate approach. A couple of reasons we went with the correlated subquery: Context: the report SQL in this same ticket actually used the CTE pattern before, and we deliberately migrated it to the inline subquery — so part of the call was keeping the aggregation job consistent with the reports. Correctness: equivalent. A CTE pre-aggregated per loan_id matches one row (no fan-out), so with COALESCE(..., '') the result is identical. Performance — depends on workload, and this favors inline: this is an incremental, date-windowed job (usually a daily run). - The subquery only touches loans in the window and is index-backed (the unique constraint on 'loan_id, originator_id' covers the loan_id lookup). - The CTE pre-aggregates the whole mapping table regardless of window size — and on Postgres a CTE with GROUP BY is materialized, so it'd scan everything even for a handful of loans. Where your CTE wins: large backfills / high repetition of the same loan, where the subquery re-evaluates per entry while the CTE aggregates once. Maybe we can to have a kind of benchmark for this, or what you think? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
