performance issue account_invoice missing index on move_id

[Ferdinand]

Example

SELECT l.id, i.id FROM account_move_line l, account_invoice i WHERE l.move_id = i.move_id AND l.id in (133695,133694,133691,133690,133689,133688,133687,133686,133685,133684,133683,133682,133681,133680,133679,133678,133675,133674,133673,133672,133698,133697,133696,132344,132343,132342,132341,132340,132339,132338,132337,132336,132335,132334,132333,132332,132331,132330,132329,132328,132327,132326,132325,132324,132323,132322,132321,132320,132319,132316,132315,132314,132313,132312,132311,132310,132309,132308,132307,132306,132305,132304,132303,132302,132301,132383,132382,132278,132277,132276,132275,132273,132272,132271,132270,132269,132268,132267,132266,132265);

WHERE l.move_id = i.move_id will create a full table scan without index.

(do not have enough data to show difference)

[Borja López Soilán (NeoPolus) (borjals)]

+1 Ferdinand

There is also a missing index on the account code (that would used on every account search).

---------

Just as an example of performance improvements that can be achieved with proper indexes, every time we do an "--update=all" (on one of our development databases) this kind of query is executed about 400 times:

(1) SELECT ir_model_data.id FROM "ir_model_data" WHERE (ir_model_data.module IN (E'')) AND (ir_model_data.model IN (E'',E'',E'')) ORDER BY id;

This other kind of query is performed almost 900 times:

(2) SELECT * FROM ir_model_fields WHERE model=E'' AND state=E'';

It spends about 2 seconds doing the first query and about one second doing the last one.

Adding the next indexes the times go down to 0.2 seconds and 0.3 seconds. That means that on 3 seconds we save 2.5 seconds!:

        CREATE INDEX ir_model_data__module_model_id__index
            ON ir_model_data (module, model, id);
        CREATE INDEX ir_model_fields__model_state__index
           ON ir_model_fields (model, state);

[Borja López Soilán (NeoPolus) (borjals)]

This is another (basic) index we usually create:

   CREATE INDEX ir_property_res_id_fields_id_index ON ir_property (res_id, fields_id)

[Borja López Soilán (NeoPolus) (borjals)]

By the way, your patch leaves this "period_id_name" variable undefined :(

[Borja López Soilán (NeoPolus) (borjals)]

Ups, sorry, my bad, I just have seen that the variable is defined some lines after :)

[xrg (xrg)]

On Thursday 29 April 2010, you wrote:
> Public bug reported:
>
> Example
>....

Thanks, Ferdinard

I have applied your patches in my trunk-pg84 branch, testing them.

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

thanks for the good contribution, I confirm and assign.

[qdp (OpenERP) (qdp)]

add select=1 on move_id field of account_invoice and of code field of account_account

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

Hello,

Added select=1 on both the fields in addons3.

revision-id: mra@mra-laptop-20101116043809-m5fxcb5mj4z62dg9
revno: 4650

Thanks,
mra