Search in admin can't use index when several fields are in `search_fields`

[Jonathan Sundqvist]

There are a couple of issues with the current implementation of search. If you have several fields like the following example.

    class MyAdmin(model.ModelAdmin):
        search_fields = ['=field_1', '@field_2']

You'll never be able to use any of the indexes that may exist on those fields, and if you were to remove field_1 you would still not be able to use any index on the full text search index for field_2.

If you have two or more fields in search fields you'll end up with a where statement that uses OR, and the index won't be used. In the case of a full text index you need to define the index with the appropriate config for the ts_vector. (See ​https://www.postgresql.org/docs/current/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX).

The current implementation uses __search (​https://github.com/django/django/blob/master/django/contrib/admin/options.py#L945) which ignores a specific config. So the index won't be used during the query.

To accomodate a config for a full text search I would suggest using @english@field_2. Then you could split on '@' and use a SearchVector and SearchQuery with the config english.

To make it possible to use index when several fields are defined in search_fields, instead of using OR. Make several querysets and do a union on all them. That way it will execute the query using the index.

[Tomer Chachamu]

I've also had to customise ModelAdmin.get_search_results because of its not-so-smartness (I will probably open a separate ticket for it).

We've already moved away from the symbols for lookups, see ​https://docs.djangoproject.com/en/dev/ref/contrib/admin/#django.contrib.admin.ModelAdmin.search_fields and previous discussion https://code.djangoproject.com/ticket/26184.

What do you think of overriding ModelAdmin.get_queryset to use .annotate(sv=SearchVector(...)) and then specifying 'sv__search' in the ModelAdmin.search_fields? It looks like that won't work with the current implementation, as search_fields must be model fields and not queryset annotations.

[Jonathan Sundqvist]

What do you think of overriding ModelAdmin.get_queryset to use .annotate(sv=SearchVector(...)) and then specifying 'sv__search' in the ModelAdmin.search_fields? It looks like that won't work with the current implementation, as search_fields must be model fields and not queryset annotations.

From what I can see that won't really work either even if you were to override the modeladmin queryset. Being able to set the config of the search vector and the search query is key to be able to use the index.

You could require a full_text_search setting in the modeladmin to configure the search vector.

full_text_search = {
    'config': 'english',
    'fields': ['field_1', 'field_2'],   
}

But as far as I know just using sv__search in that case would omit any config for that particular search query. I'll check it out and see what results I get.

[Tim Graham]

Rather than adding more syntactic sugar like @english@field_2, I think overriding ModelAdmin.get_search_results() may be the best approach. If you come up with a solution that you think is particularly elegant, I'd please raise it on the DevelopersMailingList to see if there's a consensus to add it to Django.