Using indexes in queries where the filter is made of 'OR'-ed expressions is suboptimal

We noticed that domains of the kind ['OR, ('a', '=', x), ('b.f1', '=', y)] that result in an expression of the kind t.a = x OR t.id in (subquery) could be more optimal if we used an UNION of the two queries (because postgres can sometimes use an index but in my test even without indexes it's a bit more optimal).

The review implements this on Model.search.

assigned to @nicoe

added type::performance + 1 deleted label

New review365781002 at https://codereview.tryton.org/365781002/#ps371511002

I'd advice to check performane in upcoming Postgres 14. It introduces some
optimizations which may make the patch unnecessary. The ones I remember
improved NOT IN queries but may be worth checking.

I see nothing which could suggest that the planner would perform better under PostgreSQL 14. But even if the database could optimize such OR-ed queries with indexes, I can not see how converting the query into UNION would degrade the performance. The database would style optimize the query by using the same indexes.

By the way here is a post explaining why using UNION is better for OR clause with joins: https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/.

review365781002 updated at https://codereview.tryton.org/365781002/#ps362401002

review365781002 updated at https://codereview.tryton.org/365781002/#ps358681002

review365781002 updated at https://codereview.tryton.org/365781002/#ps338681002

review365781002 updated at https://codereview.tryton.org/365781002/#ps343051002

review365781002 updated at https://codereview.tryton.org/365781002/#ps334851003

review365781002 updated at https://codereview.tryton.org/365781002/#ps373491002

review365781002 updated at https://codereview.tryton.org/365781002/#ps369631002

review365781002 updated at https://codereview.tryton.org/365781002/#ps371911002

review365781002 updated at https://codereview.tryton.org/365781002/#ps353971002

New changeset 47c55a8f9db6 by Nicolas Évrard in branch 'default':
Use UNION for 'Or'-ed domain with subqueries
https://hg.tryton.org/trytond/rev/47c55a8f9db6

added 1 deleted label and removed 1 deleted label

closed

New changeset 5d84b75a99a7 by Nicolas Évrard in branch 'default':
Use UNION for 'Or'-ed domain with subqueries
https://hg.tryton.org/tryton-env/rev/5d84b75a99a7

Hello,

I noticed a regression introduced with this change.

If we search on a domain such as the UNION is triggered, with a sort on a Reference field of the main table, the query fails. This is because __searched_column ignores Reference fields (because they are lazy-loaded by default).

This was not a problem before because even though the column was not in the searched columns, it always existed on the table, so a sort on the column did not cause any issue.

This is not specific to Reference fields, but may happen as soon as a search on a domain that triggers the UNION sorts on a lazy-loaded field.

reopened