Tryton - Issues

 

Issue7342

Title Use indexable clause calculating stock
Priority feature Status chatting
Superseder Nosy List albertca, ced, reviewbot
Type performance Components stock
Assigned To Keywords review
Reviews 44961003
View: 44961003

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

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

Thoughts?
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 https://codereview.tryton.org/44961003/#ps1
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, datetime.date.max) > period.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 > period.date) | ((move.effective_date == Null) & (move.planned_date > period.date)) | ((move.effective_date == Null) & (move.planned_date == Null))

Bringing the query execution time down to 0.9 seconds.
History
Date User Action Args
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)