Friday, August 14, 2026
1 change · saas-19.4
Enhancements to existing features
Budget reports now load much faster for databases with many analytic lines and budget lines. The report matching logic was reorganized to avoid excessive comparisons, reducing a sample load time from nearly a minute to about one second.
Original PR description
**Description:** While loading the budget report, the bad queries are created by ```def _get_aal_query()``` and ```def _get_pol_query()``` function, makes the budget report unusable. **Root cause:**…
**Description:**
While loading the budget report, the bad queries are created by
```def _get_aal_query()``` and ```def _get_pol_query()``` function, makes
the budget report unusable.
**Root cause:**
Instead of doing a hash join while searching the record,
the OR statement in the Left Join in the condition
```(%(bl)s IS NULL OR %(a)s = %(bl)s)```
creates a nested for loop that compares everything single aal to bl,
this causes a significant performance issue as the number of the
number of check will be the the number aal * bl,
if a database has a 70k aal and 20k bl, both numbers are not large
but it will cause a 70k * 20k search which is more than a billion.
**Fix**:
There are some refactors made in this PR.
_First_, separate out the Q1.
In order to find the aal that has no bl connects to it.
Doing a search to find the aals that have bl and then subtract them from all aals.
_Second_, Instead of doing a nested loop for by using
```(%(bl)s IS NULL OR %(a)s = %(bl)s)```,
originally we will have do something like
```
JOIN budget_line bl
ON (bl.x_plan2_id IS NULL OR aal.x_plan2_id = bl.x_plan2_id)
AND (bl.x_plan3_id IS NULL OR aal.x_plan3_id = bl.x_plan3_id)
AND (bl.x_plan4_id IS NULL OR aal.x_plan4_id = bl.x_plan4_id)
```
Assuming each bl has three plans ```x_plan2_id```, ```x_plan3_id```, ```x_plan4_id```
Grouping the bl base on whether a specific plan is set, (i.e. shapes)
we can skip the ```IS NULL OR``` because we already know which plan
is null and do the hash join directly.
For example, the shapes will be a dictionary with a key of a tuple of booleans
based on whether a plan is set or not and the value is a list of bl_id.
```
{
(True, False, False): [1, 2],
(False, True, True): [3, 4],
(False, False, False): [5],
}
```
we can end up doing something like
```
JOIN budget_line bl
ON bl.id = ANY(ARRAY[3,4])
AND aal.x_plan3_id = bl.x_plan3_id AND aal.x_plan4_id = bl.x_plan4_id
```
which is way more faster.
---
The benchmark is made locally from this client's database which contains
69k aal, 23k bl, 6829 pol and 3 plans for aal and bl.
|Record count |Time before|Time after|
|--------------------------------------------------|-----------------|---------------|
|69k aal, 23k bl, 6829 pol, 3 plans |70.04s |4.6s |
Dalibo:
Before:
Month-over-month grand total by company:
https://explain.dalibo.com/plan/8h3d4e89aaf9f3d4
Overall grand total by company:
https://explain.dalibo.com/plan/445g1f9caf4923e2
Month-over-month grand total by plan:
https://explain.dalibo.com/plan/53a138ca50b2a7c4
Overall grand total by plan:
https://explain.dalibo.com/plan/hdbe169ddc7g5785
After:
Month-over-month grand total by company:
https://explain.dalibo.com/plan/hcc86c801e6872bf
Overall grand total by company:
https://explain.dalibo.com/plan/69b2421a3581f98h
Month-over-month grand total by plan:
https://explain.dalibo.com/plan/a88f398bbbch3148
Overall grand total by plan:
https://explain.dalibo.com/plan/1gg749ae7ab1553c
opw-6345552
Forward-Port-Of: odoo/enterprise#127732
Forward-Port-Of: odoo/enterprise#124161