Odoo Changelog — 2023-11-26

Sunday, November 26, 2023

2 changes · 17.0

Improvements(1)

Enhancements to existing features

[FW][PERF] account_predictive_bills: Force CTE exec

Enterprise17.0account_accountantHigh impact

This update significantly improves the speed of bill prediction queries when users have large amounts of accounting data. The fix optimizes how the system processes database queries, reducing processing time from over 28 seconds to less than 60 milliseconds for large datasets. This means users with extensive transaction histories will experience much faster performance when using predictive billing features.

Original PR description

Issue:
This SQL query is slow when an user has a big quantity of account move lines and products.

Analyze:
In this query it joins the table of account_move_line with product_product to check if the product is active which is causing the slowness.
The issue is that the CTEs are inlined by the planner due to this Postgres commit
: postgres/postgres@608b167f.

The source and ranking CTE become a single SQL query and thus the tsvector Filter is run on all the account_move_line instead of the query limitation number. To solve the planner has to execute the first query (first_source) and then the second (source) on the result of first_source.

Fix:
In order to force the planner to make the limit on account_move_line a MATERIALIZED view has been used.
(https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION)

Note:
MATERIALIZED views have been implemented on PSQL 12. But all Odoo's internal servers (both Odoo.sh and SaaS) haven't any server running a previous version.

Benchmark:
| # Input data | Before PR | After PR |
|:-------------:|:----------:|:---------:|
|   3M AML               |    28.579 s            |     59.4 ms |
|   47 AML (demo data)                  |    1.262ms (planning) + 0.682 ms (execution)            |   5.289 ms + 0.994 ms            |

Related task:
opw-3539632 (Query and Planners are available in the task)

Forward-Port-Of: odoo/enterprise#51211
Forward-Port-Of: odoo/enterprise#50911

#performance#database optimization#bill prediction#query optimization

Bug Fixes(1)

Resolved issues and error corrections

[FW][FIX]l10n_au_reports: Add the expected tax number other than vat

PR #51192

Enterprise17.0l10n_au_reportsHigh impact

A recent system update changed how Australian tax identification numbers are referenced in reports, causing the system to crash when generating tax reports. This fix updates the Australian tax reporting module to correctly use the new field name (ABN instead of VAT), ensuring tax reports generate without errors.

Original PR description

with the changes to the query builder query now changes the name of `vat` in l10n_au to `abn` which causes the current state in a keyError TB.

PR where the change was introduced: https://github.com/odoo/enterprise/commit/86b2ecae3a92d24d0af2322dd9599e975c232047

opw-3574999

Forward-Port-Of: odoo/enterprise#50790

#australia#tax-reporting

Issue: This SQL query is slow when an user has a big quantity of account move lines and products. Analyze: In this query it joins the table of account_move_line with product_product to check if the product is active which is causing the slowness. The issue is that the CTEs are inlined by the planner due to this Postgres commit : postgres/postgres@608b167f. The source and ranking CTE become a single SQL query and thus the tsvector Filter is run on all the account_move_line instead of the query limitation number. To solve the planner has to execute the first query (first_source) and then the second (source) on the result of first_source. Fix: In order to force the planner to make the limit on account_move_line a MATERIALIZED view has been used. (https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION) Note: MATERIALIZED views have been implemented on PSQL 12. But all Odoo's internal servers (both Odoo.sh and SaaS) haven't any server running a previous version. Benchmark: | # Input data | Before PR | After PR | |:-------------:|:----------:|:---------:| | 3M AML | 28.579 s | 59.4 ms | | 47 AML (demo data) | 1.262ms (planning) + 0.682 ms (execution) | 5.289 ms + 0.994 ms | Related task: opw-3539632 (Query and Planners are available in the task) Forward-Port-Of: odoo/enterprise#51211 Forward-Port-Of: odoo/enterprise#50911

with the changes to the query builder query now changes the name of `vat` in l10n_au to `abn` which causes the current state in a keyError TB. PR where the change was introduced: https://github.com/odoo/enterprise/commit/86b2ecae3a92d24d0af2322dd9599e975c232047 opw-3574999 Forward-Port-Of: odoo/enterprise#50790