Tryton - Issues



Title Use indexable clause calculating stock
Priority feature Status resolved
Superseder Allow expression in index_action
View: 7580
Nosy List albertca, ced, reviewbot, roundup-bot
Type performance Components stock
Assigned To ced Keywords review
Reviews 44961003,51441002
View: 44961003, 51441002

Created on 2018-04-11.02:02:37 by albertca, last changed by roundup-bot.

New changeset eefdfceaf02a by C├ędric Krier in branch 'default':
Add index on move for join on period
review51441002 updated at
msg42212 (view) Author: [hidden] (ced) (Tryton committer) (Tryton translator) Date: 2018-07-19.15:50:45
Here is review51441002 which add an index with the COALESCE expression.
msg40145 (view) Author: [hidden] (ced) (Tryton committer) (Tryton translator) Date: 2018-04-15.01:26:23
Optimizing query execution is the purpose of indexes.
Your "OR-ed" case is less efficient than the right index.
msg40142 (view) Author: [hidden] (albertca) (Tryton committer) (Tryton translator) Date: 2018-04-15.01:12:52
We could create an index but I think we should try to minimize the number of indexes the database needs for our queries to be efficient because otherwise creating new records will be more expensive.

What I've been thinking is that maybe we could have something like "OrCoalesce" which has the same syntax as existing "Coalesce" but generates the appropriate "OR" expression instead of just using Postgres Coalesce method.

msg39941 (view) Author: [hidden] (ced) (Tryton committer) (Tryton translator) Date: 2018-04-11.22:18:58
Why not create an index for this clause instead of changing the SQL into a complex syntax ?
review44961003 updated at
msg39936 (view) Author: [hidden] (albertca) (Tryton committer) (Tryton translator) Date: 2018-04-11.02:02:36
The query returned compute_quantities_query() uses Coalesce like this:

Coalesce(move.effective_date, move.planned_date, >

The problem is that PostgreSQL is not capable of using existing effective_date and planned_date indexes resulting in a Sequential Scan of the whole stock_move table although there are stock periods closed and thus kills performance in a +9M rows table,

The complete query takes +11 seconds.

The problem can alleviated by replacing the above expression with:
(move.effective_date > | ((move.effective_date == Null) & (move.planned_date > | ((move.effective_date == Null) & (move.planned_date == Null))

Bringing the query execution time down to 0.9 seconds.
Date User Action Args
2018-07-24 19:30:23roundup-botsetstatus: testing -> resolved
nosy: + roundup-bot
messages: + msg42286
2018-07-19 16:09:22reviewbotsetmessages: + msg42215
2018-07-19 15:50:45cedsetstatus: chatting -> testing
reviews: 44961003 -> 44961003,51441002
superseder: + Allow expression in index_action
messages: + msg42212
assignedto: ced
2018-04-15 01:26:23cedsetmessages: + msg40145
2018-04-15 01:12:52albertcasetmessages: + msg40142
2018-04-11 22:18:59cedsetnosy: + ced
messages: + msg39941
2018-04-11 22:12:01cedsettype: performance
2018-04-11 02:05:31reviewbotsetstatus: unread -> chatting
nosy: + reviewbot
messages: + msg39937
2018-04-11 02:02:37albertcacreate

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