[PERF] osv: inline `ids` for `child_of`/`parent_of` operator

[pivi-odoo]

Description

Currently, when resolving hierarchy operators child_of/parent_of, if a prefix is present, the returned domain for some of the branching is:

domain = [('id', 'in', <list_of_ids>)]
return [(left, 'in', comodel_sudo._search(domain))]

which would generate WHERE clauses of the type:

model.fkey IN (SELECT id FROM comodel WHERE id IN (<list_of_ids>))

This might be problematic in the context when Postgres's planner cannot pull-up the subquery into the outer query, leading to separate planning of the outer and subquery. Combined with a lack of correlation between the outer and inner plan, Postgres can default to a hardcoded estimation of 50% of rows returned from the subquery, leading to potentially sub-optimal plans.

Proposal

The idea is to inline the result of the subquery (which is the same <list_of_ids> as a param) directly into the outer query at domain resolution, leading to WHERE clause like:

model.fkey IN (<list_of_ids>)

This will allow Postgres to directly consults it's statistics for the distributions of values for the fkey, also avoiding the subquery 'optimisation barrier', leading to better plans. This is feasible only because the generation of the subquery is done in sudo context with active_test=False, so no ir.rule nor active clause are applied to the query, always leading to a subquery of the form mentioned in the beginning.

Exemple Use Case

The web_search_read on sale.order with a lookup string from the search view looks roughly like:

SELECT *
FROM sale_order
WHERE ((name ILIKE '%somename%'
    OR client_order_ref ILIKE '%somename%')
    OR partner_id IN (SELECT id
                      FROM res_partner
                      WHERE id IN (1, 2, 3, 4))
    )
  AND company_id IN (1)
ORDER BY date_order DESC, id DESC
LIMIT 80;

This makes a subplan scanning the Pkey of res_partner, then the result is passed as an additional filter condition on an IndexScan fitting the ORDER BY clause of the query followed by a LIMIT. This is particularly slow, as the subquery returns a bad estimation of 50% of res_partner table, completely overestimating the results. After the patch, the query becomes:

SELECT *
FROM sale_order
WHERE ((name ILIKE '%somename%'
    OR client_order_ref ILIKE '%somename%')
    OR partner_id IN (1, 2, 3, 4)
    )
  AND company_id IN (1)
ORDER BY date_order DESC, id DESC
LIMIT 80;

Here the plan is resolved as a BitmapOr between the partner_id index + the trigram indexes on name and client_order_ref, then a filter -> order by -> limit is applied. On a large database, this plan is significantly faster.

---

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