spp_registry: the name/email GIN trigram indexes are unusable by an ORM ilike wherever the unaccent extension exists

[kneckinator]

Summary

spp_registry.init() creates two GIN trigram indexes on raw columns (spp_registry/models/registrant.py:82-94):

CREATE INDEX IF NOT EXISTS res_partner_name_trgm_idx
    ON res_partner USING gin (name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS res_partner_email_trgm_idx
    ON res_partner USING gin (email gin_trgm_ops);

The docstring states the purpose: "Standard B-tree indexes cannot help with ILIKE '%term%' (leading wildcard). Trigram GIN indexes allow PostgreSQL to use indexes for substring matching."

On any database where the unaccent extension exists, neither index can be used by an ORM ilike domain — which is the only thing they exist for. The ORM wraps the column in unaccent(...); these indexes are on the bare column; the two cannot meet.

Why

Odoo wraps both sides of any operator ending in ilike:

# odoo/orm/fields.py:1324-1327 (19.0)
if operator.endswith('ilike'):
    sql_left = model.env.registry.unaccent(sql_left)
    sql_value = model.env.registry.unaccent(sql_value)

registry.unaccent is the wrapping function whenever has_unaccent is truthy, and the identity function only when it is falsy:

# odoo/orm/registry.py:299
self.unaccent = _unaccent if self.has_unaccent else lambda x: x

FunctionStatus is MISSING = 0 / PRESENT = 1 / INDEXABLE = 2 (odoo/modules/db.py:162-165). Odoo's own index builder wraps the index expression to match the domain — but only at INDEXABLE, and otherwise just warns:

# odoo/orm/registry.py:889-891
if self.has_unaccent == FunctionStatus.INDEXABLE:
    column_expression = self.unaccent(column_expression)
elif self.has_unaccent:
    warnings.warn("PostgreSQL function 'unaccent' is present but not immutable, "
                  "therefore trigram indexes may not be effective.", stacklevel=1)

The hand-rolled SQL above carries no wrapper in any state. So:

has_unaccent	domain emits	hand-rolled raw-column index	Odoo's own index='trigram'
MISSING (no extension)	name ILIKE …	works	works (built unwrapped, matches)
PRESENT (extension, not immutable)	unaccent(name) ILIKE …	dead	dead (built unwrapped, warns)
INDEXABLE (extension, immutable)	unaccent(name) ILIKE …	dead	works (built wrapped)

The raw-column pattern is defeated in both truthy states, including INDEXABLE — that is the part that is a bug here regardless of how the extension was provisioned.

Which state a deployment is actually in

Odoo creates databases in one of two of these states, never PRESENT (odoo/service/db.py:150-163):

cr.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")
if odoo.tools.config['unaccent']:
    cr.execute("CREATE EXTENSION IF NOT EXISTS unaccent")
    # ... comment about accepting the incorrectness ...
    cr.execute("ALTER FUNCTION unaccent(text) IMMUTABLE")

• --unaccent is off by default (odoo/tools/config.py:451, my_default=False), so a default-created database is MISSING and these indexes work as intended.
• With --unaccent, Odoo installs the extension and immediately makes it IMMUTABLE itself → INDEXABLE.

PRESENT therefore only arises when the extension is provisioned outside Odoo's path: a DBA running CREATE EXTENSION unaccent, a managed-Postgres image that ships it, or a restored dump. That is a common way to land, and it is where the deployment measured below sits, but it is a misconfiguration relative to Odoo's own tooling rather than a default.

A related hazard worth surfacing

The index expression is chosen at build time from has_unaccent, but the domain wrapper is chosen per query from the same value. So installing the unaccent extension on an existing database silently invalidates every trigram index already built on it. Going MISSING → PRESENT (or → INDEXABLE without a reindex) leaves indexes built unwrapped while queries start arriving wrapped. This applies to Odoo's own index='trigram' fields, not just to hand-rolled SQL, and nothing detects it — the indexes remain valid, just unusable.

An install-time assertion, or raising that warnings.warn to something operators see, would catch both this and the PRESENT case.

Second, independent problem: name is not the column the partner type-ahead searches

res.partner._rec_names_search is core's ['complete_name', 'email', 'ref', 'vat', 'company_registry'] (odoo/addons/base/models/res_partner.py:189), and no spp_* module widens it for res.partner (the two _rec_names_search overrides in the tree are on spp.grm.ticket and spp.reg.relationship). name_search builds Domain('display_name', 'ilike', name), which resolves through _search_display_name over _rec_names_search.

So:

• email is on that path, and its trigram index is defeated wherever the extension exists.
• name is not on that path at all. res_partner_name_trgm_idx only ever served explicit name ilike domains — search-view filters, custom code.
• complete_name is what the type-ahead actually searches. It is fields.Char(compute=..., store=True, index=True) (res_partner.py:214) — a plain btree, which cannot serve ILIKE '%term%' in any deployment state. It has no trigram index.

This half is independent of how unaccent was provisioned, and is arguably the more valuable thing to fix.

Measured impact

Read-only against a downstream pre-production database with roughly 53 million res_partner rows, 8 September 2026, sitting at PRESENT (provolatile = 's'). Measured on an indexed identifier column (id_col below) declared in a layer-3 module, which carries a raw-column trigram index created by exactly the pattern above:

query	plan	time	buffers	rows scanned
id_col ILIKE '%term%' (raw, as written by hand)	Bitmap Index Scan on the trigram index	20.0 ms	1,813	2
unaccent(id_col) ILIKE unaccent('%term%') (what the ORM emits)	Parallel Seq Scan	7,848 ms	2,623,883 (~20 GB)	all rows

A 392× penalty on that column, and the end-to-end HTTP request measured 8,005 ms.

The mechanism transfers to name / email; this magnitude does not, and was not measured there. The column measured is a high-selectivity structured identifier — the indexed query returned 2 rows. A substring of name is far less selective, so both the index path and the sequential scan behave differently, and for a common term a working trigram index would help considerably less than 392×. What is column-independent is that the index cannot be used at all; the size of the loss is not.

This is easy to miss precisely because EXPLAIN on the hand-written query proves the index works. You have to read the SQL the ORM actually emits — and check provolatile on the instance you are reasoning about.

Reproduce

-- 1. Which state is this database in?
--    no row = MISSING (indexes work, no wrapping)
--    's'    = PRESENT (domain wrapped, index not -- the broken state)
--    'i'    = INDEXABLE (Odoo wraps its own trigram indexes to match)
SELECT p.provolatile FROM pg_proc p
WHERE p.proname = 'unaccent' AND p.pronamespace = current_schema::regnamespace AND p.pronargs = 1;

-- 2. What the ORM emits for ("name", "ilike", "term") on a PRESENT/INDEXABLE database -- seq scan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM res_partner WHERE unaccent(name) ILIKE unaccent('%term%') LIMIT 10;

-- 3. What the hand-rolled index can serve -- index scan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM res_partner WHERE name ILIKE '%term%' LIMIT 10;

Suggested fix

Declare the fields index='trigram' and delete the hand-rolled init() SQL. Odoo then builds the index expression to match whatever the domain emits, and keeps it correct by construction as the framework evolves (registry.py:880-916). Correct in all three states: wrapped at INDEXABLE, unwrapped at MISSING where the domain is also unwrapped, and no worse than today at PRESENT.

This needs no additional deployment step on a database Odoo provisioned with --unaccent, since Odoo issues the ALTER FUNCTION unaccent(text) IMMUTABLE itself. Document that statement for operators who installed the extension out of band and are therefore sitting at PRESENT — noting that existing trigram indexes were built unwrapped and need rebuilding after it.

Also worth considering, and independent of all of the above:

• add a trigram index to complete_name, the column res.partner's type-ahead actually searches;
• decide name's index deliberately rather than by accident, given it is on no name_search path;
• assert the unaccent state at install time, or raise that warnings.warn, so both PRESENT and the MISSING → PRESENT migration hazard above become visible.

A like-instead-of-ilike workaround is not applicable here: it only works where the column is case-normalised on every write path, which name and email are not.

Notes

Found while investigating an 8-second registry ID search on a downstream layer-3 deployment, which carries four indexes created by this same pattern — three of them likewise unusable. Happy to supply further EXPLAIN output on request; the originating tracker is private, so it is not linked here.