Issue 9080

Add code to template as variant prefix
Nosy list
ced, pokoli, reviewbot, roundup-bot
Assigned to

Created on 2020-02-21.14:44:25 by ced, last changed 15 months ago by roundup-bot.


New changeset 5bc5d9aaf5c8 by Cédric Krier in branch 'default':
Add code to template as variant prefix code
New changeset 88f15ac40dbe by Cédric Krier in branch 'default':
Add code to template as variant prefix code
Author: [hidden] (pokoli) Tryton committer Tryton translator
Date: 2020-02-21.18:31:31
OK, if we want to add a unique constraint on code it is worth materializing it.
Author: [hidden] (ced) Tryton committer Tryton translator
Date: 2020-02-21.17:22:43
Indeed I said that (everyone has contradiction) but there we were talking about synchronizing all the template field to the product. Here it is only one code.

Now about the benchmark.
I find that above 20ms such simple query should be considered as slow (usually we configure logging at this threshold).
So for me, all your queries are too slow for production use case.

If you create the proper index:

    create index  product_product_code_index ON product_product(code text_pattern_ops);

The code search become really fast. I have a factor of 1000 between both queries and with '=' operator it is 10000.
You got similar result with the standard index and '=' or 'in' operator which will be used more often in the web shop context.

And if you increase the factor of variant, it become worst. I get a factor of 10  with your benchmark with 100000 template and 50x variant. And I got a factor of 10000 with the proper index.

So of course it will always be faster if we get a proper index. But the materialized view will not be easy to implement and will still require a join which will be performance killing if the query has large result (or clause that can not be injected to the materialized view subquery).

Another point is the ability to create a unique constraint on product code, that's why I linked the issue9004.

In conclusion, I think it worth the data duplication in this case considering that product and template code are fields that will be very rarely changed.
Author: [hidden] (pokoli) Tryton committer Tryton translator
Date: 2020-02-21.16:36:22
Why not using the solution that uses the Function field? We always try to avoid data syncronization. See some comments about it:

I've done some tests with on my laptop. Generate products and 5 variants for each:

insert into product_template (active, code, name, type, default_uom) select true, code::varchar, Concat('Product', code), 'goods', 1 from generate_series(1, 100000) as code;
insert into product_product (template, active, suffix_code, code) select id, true, num, CONCAT(code, num) from product_template inner join generate_series(1,5) as num on true;

Select a random product with join and without it:

select from product_template t inner join product_product p on p.template = where concat(coalesce(t.code, ''), coalesce(p.suffix_code)) like '55534%'
select id from product_product where code like '55534%';

with 10.000 products and 50.000 variants:

SELECT WITH JOIN Execution Time: 50.102 ms
SELECT WITH CODE Execution Time: 18.457 ms

with 100.000 products, 500.000 variants:

SELECT WITH JOIN Execution Time: 260.993 ms
SELECT WITH CODE Execution Time: 81.735 ms

With 1M products and 5M variants:

SELECT WITH JOIN Execution Time: 1861.759 ms
SELECT WITH CODE Execution Time: 246.477 ms

The searcher starts to become slower when the number of products is bigger. I do not see a big deal for setups managing 5M of variants to materialize the colum and override the function searcher to use the materialized column.
Author: [hidden] (ced) Tryton committer Tryton translator
Date: 2020-02-21.14:44:24

Having a code on template is useful to get a SKU for web shop that display variant as a single product (see issue9010).
My proposal is to add a prefix_code on template and a suffix_code on variant and to keep up to date the field code with the concatenation of both codes.
This design allow to have both codes stored in the database in the proper table which can have an index for searching.
Another option would have been to use a Function field that concatenate both codes but this means that we need to join the both table when doing a search which may not be very efficient with a lot of variants.
Date User Action Args
2020-03-19 00:29:10roundup-botsetmessages: + msg56385
2020-03-19 00:29:05roundup-botsetstatus: testing -> resolved
nosy: + roundup-bot
messages: + msg56384
2020-02-29 01:41:18reviewbotsetmessages: + msg55636
2020-02-21 18:31:32pokolisetmessages: + msg55512
2020-02-21 17:22:44cedsetmessages: + msg55511
2020-02-21 16:36:23pokolisetnosy: + pokoli
messages: + msg55510
2020-02-21 15:14:03reviewbotsetnosy: + reviewbot
messages: + msg55509
2020-02-21 14:51:56cedlinkissue9010 superseder
2020-02-21 14:51:45cedlinkissue9004 superseder
2020-02-21 14:49:42cedsetstatus: in-progress -> testing
reviews: 293101002
keyword: + review
2020-02-21 14:44:25cedcreate