Our customer has 1.7M packages and very slow validate: the transfer took 8.6s, and 5.35s of it was the single `UPDATE` that `_parent_store_update` runs to move `parent_path` over a subtree.

It looks for the descendants with `LIKE concat(node.parent_path, '%')`. The pattern comes from a column, so Postgres cannot use the index on `parent_path` and scans the whole table. The change asks for the same rows as a range, which the index does serve:

    AND child.parent_path >= node.parent_path
    AND child.parent_path < left(node.parent_path, -1) || '0'

`parent_path` always ends with `/` and `0` is the next character, so that closes the range on the subtree. Same rows, same order, one line of SQL.

On a table of 302000 rows, moving 62 nodes, both forms return the same 9362 rows: 3302ms before, 223ms after. On the customer database a single parent write went from 0.82s to nothing measurable.

    -- before
    Update on stock_package child (actual time=3175.525..3175.527)
      ->  Nested Loop (actual time=7.171..2987.424 rows=9362)
            Join Filter: ((child.parent_path)::text ~~ concat(node.parent_path, '%'))
            Rows Removed by Join Filter: 18714638
            ->  Seq Scan on stock_package child (rows=302000)
            ->  Materialize (rows=62 loops=302000)
    Execution Time: 3301.796 ms

    -- after
    Update on stock_package child (actual time=223.040..223.041)
      ->  Nested Loop (actual time=7.874..22.389 rows=9362)
            ->  Index Scan using stock_package_pkey on stock_package node (rows=62)
            ->  Index Scan using stock_package__parent_path_index on stock_package child
                  Index Cond: ((parent_path >= node.parent_path) AND (parent_path < left(node.parent_path, -1) || '0'))
    Execution Time: 223.041 ms


This is not about only `stock.package`. Every model on `_parent_store` pays it once the table grows, `res.partner` and `stock.location` included.




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

When you create a new company in a database that has a lot of existing
account move lines and you attempt to open a PoS register from the list
view, `_compute_company_has_template` checks `_existing_accounting` for
the new company and wil run a sequential scan on the entire account_move_line
table followed by a nested loop as the query planner assumes AMLs company_ids
will be roughly evenly distributed.

This is not the case in a new company that has no/very few AMLs.

This is because the query ran is:
`SELECT COUNT(*) FROM
    (SELECT FROM "account_move_line"
    WHERE (
    "account_move_line"."company_id" IN
        (SELECT "res_company"."id" FROM
        "res_company" WHERE
        ("res_company"."parent_path" LIKE '3/%')
    ))
LIMIT 1)`

and the values of company_id being searched for aren't known until the
subquery runs.

Running a query more like
`SELECT COUNT(*) FROM
account_move_line
WHERE company_id IN (%s)`
is much faster

Since res_company will always be a smaller table, we can do the inexpensive
search first and then pass in the values so Postgres can do a cheaper
search and return faster.

Benchmark time of `_existing_accounting`:
| Company 1 AML count | Company 2 AML count | Pre-fix | Post-fix | Multiplier |
|---|---|---|---|---|
| 10,000,000 | 0 | 700 Milliseconds | 500 Microseconds | 1,400x |
| 20,000,000 | 0 | 1.35 Seconds | 1 Millisecond | 1,350x |
| 20,000,000 | 20,000,000 | 2 Milliseconds |1.3 Milliseconds | 1.5x |
| 50,000,000 | 0 | 3.25 Seconds | 1 Millisecond | 3,250x |
| 100,000,000 | 0 | 5.3 Seconds | 1.3 Milliseconds | 4,075x |

Query Plan Before:
```
"Aggregate  (cost=0.08..0.09 rows=1 width=8) (actual time=5573.001..5573.002 rows=1.00 loops=1)"
"  Buffers: shared read=571435"
"  ->  Limit  (cost=0.00..0.08 rows=1 width=0) (actual time=5572.995..5572.997 rows=0.00 loops=1)"
"        Buffers: shared read=571435"
"        ->  Nested Loop  (cost=0.00..https://github.com/odoo/odoo/commit/1571439c0c70c1f1dc3229421e696b97ce1678f8.33 rows=20000086 width=0) (actual time=5572.988..5572.989 rows=0.00 loops=1)"
"              Join Filter: (account_move_line.company_id = res_company.id)"
"              Buffers: shared read=571435"
"              ->  Seq Scan on account_move_line  (cost=0.00..971432.72 rows=40000172 width=4) (actual time=0.432..1913.934 rows=40000000.00 loops=1)"
"                    Buffers: shared read=571431"
"              ->  Materialize  (cost=0.00..4.03 rows=1 width=4) (actual time=0.000..0.000 rows=0.00 loops=40000000)"
"                    Storage: Memory  Maximum Storage: 17kB"
"                    Buffers: shared read=4"
"                    ->  Seq Scan on res_company  (cost=0.00..4.03 rows=1 width=4) (actual time=0.785..0.785 rows=0.00 loops=1)"
"                          Filter: ((parent_path)::text ~~ '3/%'::text)"
"                          Rows Removed by Filter: 2"
"                          Buffers: shared read=4"
"Planning:"
"  Buffers: shared hit=574 read=77"
"Planning Time: 15.323 ms"
"Execution Time: 5573.060 ms"
```
Query Plan After:
```
"Aggregate  (cost=4.46..4.47 rows=1 width=8) (actual time=1.972..1.973 rows=1.00 loops=1)"
"  Buffers: shared read=3"
"  ->  Limit  (cost=0.44..4.46 rows=1 width=0) (actual time=1.967..1.968 rows=0.00 loops=1)"
"        Buffers: shared read=3"
"        ->  Index Only Scan using account_move_line__company_id_index on account_move_line  (cost=0.44..4.46 rows=1 width=0) (actual time=1.965..1.966 rows=0.00 loops=1)"
"              Index Cond: (company_id = 3)"
"              Heap Fetches: 0"
"              Index Searches: 1"
"              Buffers: shared read=3"
"Planning:"
"  Buffers: shared hit=3"
"Planning Time: 0.219 ms"
"Execution Time: 1.998 ms"
```
opw-6513885

Forward-Port-Of: odoo/odoo#285096