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
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/.
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> > ______________________________________ >
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:32 | ced | set | status: chatting -> resolved |
| 2021-09-24 13:55:22 | ced | set | messages: - msg70381 |
| 2021-09-24 13:55:19 | ced | set | messages: - msg70377 |
| 2021-09-24 13:55:15 | ced | set | messages:
- msg70375 status: resolved -> chatting |
| 2021-09-24 13:55:08 | ced | set | status: chatting -> resolved |
| 2021-09-24 13:54:30 | jcavallo | set | messages:
+ msg70381 status: resolved -> chatting |
| 2021-09-24 13:43:44 | ced | set | messages:
+ msg70377 status: chatting -> resolved |
| 2021-09-24 12:23:45 | jcavallo | set | messages:
+ msg70375 nosy: + jcavallo status: resolved -> chatting |
| 2021-09-15 15:16:55 | roundup-bot | set | messages: + msg70121 |
| 2021-09-15 15:16:52 | roundup-bot | set | messages:
+ msg70120 nosy: + roundup-bot status: in-progress -> resolved |
Showing 10 items. Show all history (warning: this could be VERY long)