performance account_move_line needs index on partner_id

[Ferdinand]

reduction ~ 90%
payables and receivables access account_move_lines using partner_id

before
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 HashAggregate (cost=1351.87..1351.89 rows=1 width=21)
   -> Nested Loop (cost=3.17..1351.86 rows=1 width=21)
         -> Nested Loop IN Join (cost=3.17..1335.75 rows=2 width=20)
               Join Filter: (l.period_id = account_period.id)
               -> Seq Scan on account_move_line l (cost=0.00..1328.75 rows=34 width=24)
                     Filter: ((reconcile_id IS NULL) AND ((state)::text <> 'draft'::text) AND (partner_id = 20))
               -> Materialize (cost=3.17..3.22 rows=5 width=4)
                     -> Seq Scan on account_period (cost=0.00..3.17 rows=5 width=4)
                           Filter: (fiscalyear_id = ANY ('{1,2,3,5,6}'::integer[]))
         -> Index Scan using account_account_pkey on account_account a (cost=0.00..8.04 rows=1 width=9)
               Index Cond: (a.id = l.account_id)
               Filter: ((a.type)::text = ANY ('{receivable,payable}'::text[]))

create index chricar_account_move_line_partner_id on account_move_line (partner_id);

after
 HashAggregate (cost=84.41..84.43 rows=1 width=21)
   -> Nested Loop (cost=7.63..84.40 rows=1 width=21)
         -> Hash IN Join (cost=7.63..76.11 rows=1 width=20)
               Hash Cond: (l.period_id = account_period.id)
               -> Bitmap Heap Scan on account_move_line l (cost=4.40..72.81 rows=17 width=24)
                     Recheck Cond: (partner_id = 20)
                     Filter: ((reconcile_id IS NULL) AND ((state)::text <> 'draft'::text))
                     -> Bitmap Index Scan on chricar_account_move_line_partner_id (cost=0.00..4.39 rows=19 width=0)
                           Index Cond: (partner_id = 20)
               -> Hash (cost=3.17..3.17 rows=5 width=4)
                     -> Seq Scan on account_period (cost=0.00..3.17 rows=5 width=4)
                           Filter: (fiscalyear_id = ANY ('{1,2,3,5,6}'::integer[]))
         -> Index Scan using account_account_pkey on account_account a (cost=0.00..8.28 rows=1 width=9)
               Index Cond: (a.id = l.account_id)
               Filter: ((a.type)::text = ANY ('{receivable,payable}'::text[]))

BVTW - the docu says
select: True - (creates an index on the Foreign Key field)
so what does select=2 mean ?

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

I confirm, just add select="1" on partner_id field of account.move.line.

[Mustufa Rangwala (Open ERP) (mra-tinyerp)]

Hello Ferdinand,

We have applied your patch in Addons3 branch.

revision-id: mra@mra-laptop-20101116041416-zonccm8lg9dvf2at
revno: 4648

Thank you for contribution,
mra