#31304: PostgreSQL full-text search employs coalesce function for non-null
single-
column searches with SearchVector
-------------------------------------+-------------------------------------
Reporter: uiopaubo | Owner: nobody
Type: | Status: new
Uncategorized |
Component: | Version: 2.2
Uncategorized | Keywords: PostgreSQL text
Severity: Normal | search FTS coalesce SearchVector
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When following the PostgreSQL full-text search documentation for Django
(https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/search/), the
search lookup...
{{{
Table.objects.filter(column__search="keyword")
}}}
...produces SQL of the following form:
{{{
to_tsvector(column) @@ plainto_tsquery('keyword')
}}}
However, the PostgreSQL documentation notes that such expressions will be
unable to take advantage of indexes created on the column:
"Only text search functions that specify a configuration name can be used
in expression indexes [...] Because the two-argument version of
to_tsvector was used in the index above, only a query reference that uses
the 2-argument version of to_tsvector with the same configuration name
will use that index."
[https://www.postgresql.org/docs/11/textsearch-tables.html]
Introducing a SearchQuery object employing the config parameter...
{{{
Table.objects.filter(column__search=SearchQuery("keyword",
config="simple"))
}}}
...produces SQL of the following form:
{{{
to_tsvector(column) @@ plainto_tsquery('simple', 'keyword')
}}}
The Django documentation suggests using an annotation employing a
SearchVector as follows:
{{{
Table.objects.annotate(search=SearchVector("column",
config="simple")).filter(search=SearchQuery("keyword", config="simple"))
}}}
The resulting SQL generated by Django is then as follows:
{{{
to_tsvector('simple', coalesce(column, '')) @@ plainto_tsquery('simple',
'keyword')
}}}
Unfortunately, the use of coalesce now blocks any application of an index
on the column.
What seems to be possible, however, is to modify the SQL generation to
avoid using coalesce where it can be determined that the operand given to
to_tsvector will not yield a null value. This should produce the following
more desirable SQL:
{{{
to_tsvector('simple', column) @@ plainto_tsquery('simple', 'keyword')
}}}
A patch is provided as a suggestion of how this issue might be fixed.
--
Ticket URL: <https://code.djangoproject.com/ticket/31304>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.b5f390f39a7b1a05ce008951a8ed46c7%40djangoproject.com.
