Produits Entr'ouvert » Authentic 2

J'ai dans les logs de PostgreSQL la requête suivante, relativement courante:

SELECT count(*) AS "__count" FROM "custom_user_user" WHERE (NOT (EXISTS ( SELECT (?) AS "a" FROM "a2_rbac_role_members" u1 WHERE (u1."role_id" = ? AND u1."user_id" = "custom_user_user"."id") LIMIT ?)) AND (upper("custom_user_user"."username"::text) LIKE upper(?) OR upper("custom_user_user"."first_name"::text) LIKE upper(?) OR upper("custom_user_user"."last_name"::text) LIKE upper(?) OR upper("custom_user_user"."email"::text) LIKE upper(?)));

Elle n'est pas optimisable sans ajouter un certain nombre d'index trigram sur upper(email), upper(first_name), upper(last_name) et upper(username).
Or, on a déjà dans la base des index trigram sur lower(email) et sur lower(unaccent(first_name || ' ' || last_name)).
On pourrait donc ajouter un index trigram sur lower(username), et modifier la requête comme suit:

SELECT count(*) AS "__count" FROM "custom_user_user" WHERE (NOT (EXISTS ( SELECT (?) AS "a" FROM "a2_rbac_role_members" u1 WHERE (u1."role_id" = ? AND u1."user_id" = "custom_user_user"."id") LIMIT ?)) AND (lower("custom_user_user"."username"::text) LIKE lower(?) OR lower(immutable_unaccent(first_name::text || ' ' || last_name)) LIKE lower(?) OR lower("custom_user_user"."email"::text) LIKE lower(?)));

Sur un cas simple, on divise par trois le temps d'exécution de la requête (180ms => 60ms sur un tenant)

Autre solution, qui permet d'effacer complètement le temps d'exécution de la requête :

authentic2_multitenant=# create index concurrently test_trgm_user on custom_user_user using gist (lower(immutable_unaccent(first_name || ' ' || last_name || ' ' || username || ' ' || email)) gist_trgm_ops);
authentic2_multitenant=# explain analyze SELECT COUNT(*) AS "__count" 
FROM "custom_user_user" WHERE 
        (NOT (EXISTS ( SELECT (1) AS "a" FROM "a2_rbac_role_members" U1 WHERE (U1."role_id" = 752 AND U1."user_id" = "custom_user_user"."id") LIMIT 1)) AND lower(immutable_unaccent(first_name || ' ' || last_name || ' ' || username || ' ' || email)) like lower('%toto%'));

La requête passe alors à 2 ms.