[FW][PERF] stock_account: optimize compute valuation

[fw-bot]

Description

I am experiencing slow processing times for incoming stock from vendors when the historical data contains a large number of records.

Currently, my company has around 20 million stock.valuation.layer records. As a result, any transaction involving incoming stock takes approximately 5–21 seconds to complete, even for a small number of products (around 1–5 products).

Through profiling, I have identified that the root cause is the compute function _compute_value_svl. This computed field is always triggered during incoming stock transactions when calculating the average cost or fixing rounding errors.

Proposal

To improve performance, I propose implementing a Parallel Index-Only Scan. This method is effective for grouping large datasets while retrieving only fixed columns, optimizing query execution.

Benchmark

Before	After	Speedup
1.1 s	97.8 ms	11x

Before

Finalize GroupAggregate  (cost=1000.56..397852.39 rows=538 width=80) (actual time=1040.355..1114.694 rows=1 loops=1)
  Group Key: product_id
  Buffers: shared hit=227534 read=330906 dirtied=83 written=683
  ->  Gather  (cost=1000.56..397825.49 rows=1076 width=80) (actual time=1039.900..1114.679 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=227534 read=330906 dirtied=83 written=683
        ->  Partial GroupAggregate  (cost=0.56..396717.89 rows=538 width=80) (actual time=1036.758..1036.759 rows=1 loops=3)
              Group Key: product_id
              Buffers: shared hit=227534 read=330906 dirtied=83 written=683
              ->  Parallel Index Scan using stock_valuation_layer_index on stock_valuation_layer  
                    (cost=0.56..392927.16 rows=302613 width=20) (actual time=0.608..975.431 rows=247487 loops=3)
                    Index Cond: ((product_id = 140) AND (company_id = 1))
                    Buffers: shared hit=227534 read=330906 dirtied=83 written=683
Planning:
  Buffers: shared hit=9 dirtied=1
Planning Time: 0.139 ms
Execution Time: 1114.732 ms

After

Finalize GroupAggregate  (cost=1000.56..21876.83 rows=538 width=80) (actual time=91.888..97.719 rows=1 loops=1)
  Group Key: product_id
  Buffers: shared hit=12889 read=7121
  ->  Gather  (cost=1000.56..21849.93 rows=1076 width=80) (actual time=91.636..97.704 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=12889 read=7121
        ->  Partial GroupAggregate  (cost=0.56..20742.33 rows=538 width=80) (actual time=87.579..87.580 rows=1 loops=3)
              Group Key: product_id
              Buffers: shared hit=12889 read=7121
              ->  Parallel Index Only Scan using idx_svl_company_product on stock_valuation_layer  
                    (cost=0.56..16951.60 rows=302613 width=20) (actual time=0.044..46.642 rows=247487 loops=3)
                    Index Cond: ((company_id = 1) AND (product_id = 140))
                    Heap Fetches: 9188
                    Buffers: shared hit=12889 read=7121
Planning Time: 0.107 ms
Execution Time: 97.751 ms

---

I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr

Forward-Port-Of: #206587
Forward-Port-Of: #203038

[robodoo]

[fw-bot]

@paimonchan @Whenrow cherrypicking of pull request #203038 failed.

• 9c09e81 <- on this commit
• b89c3ab

stdout:

Auto-merging addons/stock_account/models/stock_valuation_layer.py
CONFLICT (content): Merge conflict in addons/stock_account/models/stock_valuation_layer.py

Either perform the forward-port manually (and push to this branch, proceeding as usual) or close this PR (maybe?).

In the former case, you may want to edit this PR message as well.

⚠️ after resolving this conflict, you will need to merge it via @robodoo.

More info at https://github.com/odoo/odoo/wiki/Mergebot#forward-port

[Whenrow]

robodoo r+

[robodoo]

@paimonchan @Whenrow 'ci/runbot' failed on this reviewed PR.