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

Forward-Port-Of: odoo/odoo#287180