performance issue in stock/product.py

[Ferdinand]

no need to read the table twice - once for in once for out

        if 'in' in what:
            # all moves from a location out of the set to a location in the set
            cr.execute(
                'select sum(product_qty), product_id, product_uom '\
                'from stock_move '\
                'where location_id not in ('+location_ids_str+') '\
                'and location_dest_id in ('+location_ids_str+') '\
                'and product_id in ('+prod_ids_str+') '\
                'and state in ('+states_str+') '+ (date_str and 'and '+date_str+' ' or '') +''\
                'group by product_id,product_uom'
            )
            results = cr.fetchall()
        if 'out' in what:
            # all moves from a location in the set to a location out of the set
            cr.execute(
                'select sum(product_qty), product_id, product_uom '\
                'from stock_move '\
                'where location_id in ('+location_ids_str+') '\
                'and location_dest_id not in ('+location_ids_str+') '\
                'and product_id in ('+prod_ids_str+') '\
                'and state in ('+states_str+') '+ (date_str and 'and '+date_str+' ' or '') + ''\
                'group by product_id,product_uom'
            )
            results2 = cr.fetchall()

for a similar task I use this

            cr.execute(
                    'select sum('\
                    'case when location_dest_id in ('+location_ids_str+') then product_qty else 0 end + '\
                    'case when location_id in ('+location_ids_str+') then -product_qty else 0 end '\
                    ') as qty, ' \
                    'sum('\
                    'case when location_dest_id in ('+location_ids_str+') then move_value_cost else 0 end + '\
                    'case when location_id in ('+location_ids_str+') then -move_value_cost else 0 end '\
                    ') as value_cost, product_id '\
                    'from stock_move '\
                    'where (location_id in ('+location_ids_str+') '\
                    'or location_dest_id in ('+location_ids_str+')) '\
                    'and product_id in ('+prod_ids_str+') '\
                    'and state in ('+states_str+') '+ (date_str and 'and '+date_str+' ' or '') +''\
                    'group by product_id'
                )

b5-proposed=# explain select * from stock_move where location_id in (1,2,3) or location_dest_id in (1,2,3);
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on stock_move (cost=25.79..114.51 rows=37 width=384)
   Recheck Cond: ((location_id = ANY ('{1,2,3}'::integer[])) OR (location_dest_id = ANY ('{1,2,3}'::integer[])))
   -> BitmapOr (cost=25.79..25.79 rows=37 width=0)
         -> Bitmap Index Scan on stock_move_location_id_location_dest_id_product_id_state (cost=0.00..12.89 rows=19 width=0)
               Index Cond: (location_id = ANY ('{1,2,3}'::integer[]))
         -> Bitmap Index Scan on chircar_location_dest_id (cost=0.00..12.89 rows=18 width=0)
               Index Cond: (location_dest_id = ANY ('{1,2,3}'::integer[]))
(7 rows)

b5 - current=# explain select * from stock_move where location_id in (1,2,3) or location_dest_id not in (1,2,3);
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on stock_move (cost=0.00..183.63 rows=1675 width=384)
   Filter: ((location_id = ANY ('{1,2,3}'::integer[])) OR (location_dest_id <> ALL ('{1,2,3}'::integer[])))
(2 rows)

[Ferdinand (office-chricar)]

I suggest to change the index as mentioned in the patch

the postgresql optimizer (and users ) will honor this with enhanced performance

[Ferdinand (office-chricar)]

a more realistic scenario for Explain with the new index

stock.py - query

explain select sum(product_qty), product_id, product_uom,sum(move_value_cost) from stock_move where location_id in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96) and location_dest_id not in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96) and product_id in (1,85) and state in ('done') group by product_id,product_uom;

 HashAggregate (cost=118.00..118.02 rows=1 width=24)
   -> Bitmap Heap Scan on stock_move (cost=90.73..117.94 rows=6 width=24)
         Recheck Cond: ((product_id = ANY ('{1,85}'::integer[])) AND ((state)::text = 'done'::text) AND (location_id = ANY ('{15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96}'::integer[])))
         Filter: (location_dest_id <> ALL ('{15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96}'::integer[]))
         -> Bitmap Index Scan on stock_move_location_id_location_dest_id_product_id_state (cost=0.00..90.73 rows=8 width=0)
               Index Cond: ((product_id = ANY ('{1,85}'::integer[])) AND ((state)::text = 'done'::text) AND (location_id = ANY ('{15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96}'::integer[])))
(6 rows)

compared to

select sum(case when location_dest_id in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96) then product_qty else 0 end + case when location_id in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96) then -product_qty else 0 end ) as qty, sum(case when location_dest_id in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96) then move_value_cost else 0 end + case when location_id in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96) then -move_value_cost else 0 end ) as value_cost, product_id from stock_move where (location_id in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96) or location_dest_id in (15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96)) and product_id in (1,85) and state in ('done') group by product_id;

 HashAggregate (cost=55.95..56.11 rows=1 width=28)
   -> Bitmap Heap Scan on stock_move (cost=8.64..55.87 rows=10 width=28)
         Recheck Cond: ((product_id = ANY ('{1,85}'::integer[])) AND ((state)::text = 'done'::text))
         Filter: ((location_id = ANY ('{15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96}'::integer[])) OR (location_dest_id = ANY ('{15,102,10,71,75,123,124,104,81,85,86,87,67,88,103,16,17,44,68,69,70,73,83,72,79,80,84,96}'::integer[])))
         -> Bitmap Index Scan on stock_move_location_id_location_dest_id_product_id_state (cost=0.00..8.64 rows=15 width=0)
               Index Cond: ((product_id = ANY ('{1,85}'::integer[])) AND ((state)::text = 'done'::text))

original cost 118 * 2 queries=236
proposed = 56 - about 75% better

[Azazahmed Saiyed (OpenERP) (saz-openerp)]

Let the team will decide.

[Fabien (Open ERP) (fp-tinyerp)]

released in r6582.