Question about queries on SearchVectorField

Using Django Using the ORM
1

I’m experimenting with a SearchVectorField in order to speed up searching in the Django Admin for a very large table. I’ve added a SearchVectorField and a GinIndex to the model, along with a trigger to automatically update the field when the relevant columns change.

My question is about the query generated when searching this table.

Using the Django Debug Toolbar, if I search for a string foo in the admin, I see this query:

SELECT COUNT(*) AS "__count"
  FROM "mymodel"
 WHERE "mymodel"."search_vector"::text LIKE '%foo%';

PostgreSQL documentation for queries using tsvector column types look more like this:

SELECT count(*)
FROM mymodel
WHERE search_vector @@ to_tsquery('foo');

I also tried running these queries in psql and the difference between the two is staggering. The Django-generated takes about 2.1 seconds but the query using @@ takes about 3 milliseconds.

Running explain, I see a similar difference in magnitude in the cost reported for each query.

I’m curious to know if anybody who has worked with PostgreSQL full text search, and particularly SearchVectorFields, has explored this and found better results.

Thanks

(Django 4.2 LTS, PostgreSQL 15.13, python 3.12.11, psycopg2 2.9.3)

2

Hi @dougharris, the problem you are facing is caused by django admin defaulf behaviour for search.

When you want the search to use SearchVectorField capabilities you sould put '@search_vector' instead of 'search_vector' in yours search_fields. This tells to django admin site to use the search database lookup instead of the default icontaints. You may also set 'search_vector__search' or 'search_vector__exact' to be sure what lookup is used.

See The Django admin site | Django documentation | Django for further details.

1 Like
3

Aha! Thanks.

In my specific case, I had overriden get_search_results() for some number-entry special cases.

My (incorrect) usage had been:

queryset = self.model.objects.filter(search_vector__contains=search_term)

Using __search didn’t work for my column. Properly reading the SearchVectorField documentation (again), I realize that simply using = is the way to go:

queryset = self.model.objects.filter(search_vector=search_term)

and the debug toolbar confirms that the generated query uses the @@ syntax.