Issue 10658

Title
Using indexes in queries where the filter is made of 'OR'-ed expressions is suboptimal
Priority
feature
Status
resolved
Nosy list
albertca, ced, jcavallo, nicoe, reviewbot, roundup-bot
Assigned to
nicoe
Keywords
review

Created on 2021-08-13.13:44:07 by nicoe, last changed 3 weeks ago by ced.

Messages

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
New changeset 47c55a8f9db6 by Nicolas Évrard in branch 'default':
Use UNION for 'Or'-ed domain with subqueries
https://hg.tryton.org/trytond/rev/47c55a8f9db6
Author: [hidden] (ced) Tryton committer Tryton translator
Date: 2021-08-13.22:59:48

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/.

Author: [hidden] (albertca)
Date: 2021-08-13.20:23:15
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.

El dv., 13 d’ag. 2021, 13:44, Nicolas Évrard <bugs@tryton.org> va escriure:

>
> New submission from Nicolas Évrard <nicoe@b2ck.com>:
>
> 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`.
>
> ----------
> assignedto: nicoe
> keyword: review
> messages: 69366
> nosy: nicoe
> priority: feature
> reviews: 365781002
> status: in-progress
> title: Using indexes in queries where the filter is made of 'OR'-ed
> expressions is suboptimal
> type: performance
>
> ______________________________________
> Tryton issue tracker <bugs@tryton.org>
> <https://bugs.tryton.org/issue10658>
> ______________________________________
>
Author: [hidden] (nicoe) Tryton committer
Date: 2021-08-13.13:44:07

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.

History
Date User Action Args
2021-09-24 13:55:32cedsetstatus: chatting -> resolved
2021-09-24 13:55:22cedsetmessages: - msg70381
2021-09-24 13:55:19cedsetmessages: - msg70377
2021-09-24 13:55:15cedsetmessages: - msg70375
status: resolved -> chatting
2021-09-24 13:55:08cedsetstatus: chatting -> resolved
2021-09-24 13:54:30jcavallosetmessages: + msg70381
status: resolved -> chatting
2021-09-24 13:43:44cedsetmessages: + msg70377
status: chatting -> resolved
2021-09-24 12:23:45jcavallosetmessages: + msg70375
nosy: + jcavallo
status: resolved -> chatting
2021-09-15 15:16:55roundup-botsetmessages: + msg70121
2021-09-15 15:16:52roundup-botsetmessages: + msg70120
nosy: + roundup-bot
status: in-progress -> resolved

Showing 10 items. Show all history (warning: this could be VERY long)