Wednesday, January 24, 2024
1 change · 17.0
Enhancements to existing features
P&L reports that group by analytic accounts or plans are now significantly faster. The update adds a database index to the temporary table used for analytic reporting, reducing report generation time by up to 90% (from 21 seconds to 2.1 seconds for large datasets). This improvement makes financial reporting more responsive for users working with detailed analytic data.
Original PR description
## Description Opening a P&L report and doing some grouping by an analytic account or an analytic plan can be extremely slow. This is due to the fact that, in the goal of reusing the generic report…
## Description Opening a P&L report and doing some grouping by an analytic account or an analytic plan can be extremely slow. This is due to the fact that, in the goal of reusing the generic report engine, which is based on `account.move.line`, a temporary table is created that shadows said table, but overwrite the data with content from `account.analytic.line` instead. The temporary table has absolutely no indexes, therefor forcing a Seq.Scan for all queries done on the table. As the `account.analytic.line` model can grow quite large in general, sequential scans on that table quickly becomes slow. ## Fix We introduce 1 composite index on the temporary table to speed up the subsequent queries on it. This slows down the initial creation of the temporary table, at the benefit of significantly speeding up all report sections queries, which are done for each `account_type`. Since the temporary table has no traffic on it, the exclusive lock on the table for the index creation is immediately acquired, so the time taken to build the index is proportional to the size of the table. The index is dropped when the temporary table is dropped. ## Benchmark Durations of the request `get_report_informations` for a P&L report of the last fiscal year, all journals, and grouping by 1 analytic plan of a database with a bit above 200k AAL: | AAL Volume | Before | After | |------------|--------|-------| | 50k | 4.75s | 1s | | 100k | 9s | 1.4s | | 200k | 21s | 2.1s | (in the last line, the 2.1s -> 1.2s is for the index creation + analyze) Scaling is also better when it comes to grouping by more analytic accounts or plans. ## Reference opw-3659497 Community PR: https://github.com/odoo/odoo/pull/148490 Forward-Port-Of: odoo/enterprise#53817