On 2015-06-11 20:52, Jordi Esteve wrote:
> I have tested the duplication and reception of supplier shipments in a
> database with 1,200,000 invoice lines in my laptop (old machine with a
> postgres tuned to use 400MB).
>
> Before applying the patch this query was generated which takes 25864.758 ms
> - 28027.834 ms (between 26 and 28 seconds):
>
> SELECT "a"."id" AS "id", "a"."stock_move" AS "stock_move",
> "a"."invoice_line" AS "invoice_line", ...
> FROM "account_invoice_line-stock_move" AS "a"
> LEFT JOIN "account_invoice_line" AS "b" ON ("b"."id" = "a"."invoice_line")
> WHERE (("a"."stock_move" IN (1826865, 1826864))
> AND ("a"."invoice_line" IN (SELECT "c"."id" AS "id" FROM
> "account_invoice_line" AS "c" WHERE ((("c"."id" IS NOT NULL))
> AND ("c"."id" IN (SELECT "d"."id" AS "id" FROM "account_invoice_line" AS
> "d" WHERE (((("d"."company" = 1))) AND true)))))))
> ORDER BY "b"."description" ASC;
>
> After applying the patch and setting target_search='join' in the m2o field
> invoice_line in account.invoice.line-stock.move table, this query was
> generated without the subquery which takes only 6.708 ms:
>
> SELECT "a"."id" AS "id", "a"."stock_move" AS "stock_move",
> "a"."invoice_line" AS "invoice_line", ...
> FROM "account_invoice_line-stock_move" AS "a"
> LEFT JOIN "account_invoice_line" AS "b" ON ("b"."id" = "a"."invoice_line")
> WHERE (("a"."stock_move" IN (1826865, 1826864))
> AND ((("b"."id" IS NOT NULL))
> AND ("b"."id" IN (SELECT "c"."id" AS "id" FROM "account_invoice_line" AS
> "c" WHERE (((("c"."company" = 1))) AND true)))))))
> ORDER BY "b"."description" ASC;
>
>
> As we can see it is nearly 4000 times faster, so the improvement is
> impressive. I suppose testing in a server with more and better resources the
> difference will not be so high, but IMHO really worth it.
Thanks for the report.
> By the way, the default option in the new parameter 'target_search' for m2o
> fields is 'subquery'. Maybe is it better to set 'join' by default? If not,
> then the m2o relations suspected to have a lot of records like move lines,
> invoice lines, sale lines, stock moves, ... should have the 'join' parameter
> set in the tryton modules?
I will explain why I kept 'subquery' as default. On small database,
there will be almost no difference with the 'join'. On larger database,
in many cases the query planner could infer the 'join' if it worths it
base on its statistics. But the 'in' join could be worse on specific
case.
So for me, we must explicitly set the 'target_search' after some
thoughts just like the 'ondelete'. We will have to make an issue after
this one to search on each module the Many2One to fix.
--
Cédric Krier - B2CK SPRL
Email/Jabber: [email protected]
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/
