server-tools icon indicating copy to clipboard operation
server-tools copied to clipboard

[IMP] base_search_fuzzy - add support of translatable fields

Open yvaucher opened this issue 2 months ago • 1 comments

depends on:

  • https://github.com/odoo/odoo/pull/232993

This allow to create a trigram index on translatable fields.

Searches on translatable fields look like this:

SELECT * FROM table WHERE COALESCE({column_name}->>'{lang}', {column_name}->>'en_US' % 'text'

On a DB with 8M dummy products (duplicated with odoo populate),

With a query containing

WHERE COALESCE("product_template"."name"->>'fr_FR', "product_template"."name"->>'en_US') % 'search text')

Without index Execution Time: 10606.619 ms

With index: Execution Time: 484.383 ms

:warning: For en_US to work with operator % this requires a patch on the core https://github.com/odoo/odoo/pull/232993

yvaucher avatar Oct 24 '25 10:10 yvaucher

@sbidoul Thanks for the review, I made the fixes following your remarks.

yvaucher avatar Oct 26 '25 15:10 yvaucher