Odoo Changelog — 2023-11-26

Sunday, November 26, 2023

1 change · 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

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