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 2 months 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
2021-09-15 11:49:52reviewbotsetmessages: + msg70116
2021-09-15 09:24:37reviewbotsetmessages: + msg70112
2021-09-08 09:51:11reviewbotsetmessages: + msg69933
2021-09-03 13:36:57reviewbotsetmessages: + msg69856
2021-09-01 18:04:09reviewbotsetmessages: + msg69763
2021-09-01 17:14:01reviewbotsetmessages: + msg69759
2021-08-26 13:11:47reviewbotsetmessages: + msg69604
2021-08-20 16:23:31reviewbotsetmessages: + msg69473
2021-08-17 12:58:26reviewbotsetmessages: + msg69407
2021-08-13 22:59:48cedsetmessages: + msg69375
nosy: + ced
2021-08-13 20:23:15albertcasetmessages: + msg69374
nosy: + albertca
2021-08-13 13:47:57reviewbotsetmessages: + msg69368
nosy: + reviewbot
2021-08-13 13:44:07nicoecreate