I created a trigram index on full name where full_name = first_name || ' ' || last_name.
CREATE INDEX "user_full_name_trgm"
ON "user" using gin (upper(first_name || ' ' || last_name) gin_trgm_ops);
I am using django queryset to filter over fullname.
queryset = queryset.annotate(full_name=Concat("first_name", Value(" "), "last_name"))
queryset = queryset.filter(full_name__istartswith=full_name)
which generates WHERE clause as
WHERE (UPPER(CONCAT("user"."first_name", CONCAT(' ', "user"."last_name"))::text) LIKE UPPER('mike%')
The explain analyse shows that its not using user_full_name_trgm index. However when I use pipe to join the strings, the index is being used.
WHERE (UPPER(first_name || ' ' || last_name) LIKE UPPER('mike%')
How can I make django query to use the index? Can I modify query.annotate in such a way that it creates sql query without Concat? Or can I create index in such a way that it is used even with concat?