Message 55510

Message id


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.
Date User Action Args
2020-02-21 16:36:23pokolisetmessageid: <>
2020-02-21 16:36:23pokolisetrecipients: + ced, reviewbot
2020-02-21 16:36:23pokolilinkissue9080 messages
2020-02-21 16:36:22pokolicreate

Showing 10 items. Show all history (warning: this could be VERY long)