id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31304	PostgreSQL full-text search employs coalesce function for non-null single-column searches with SearchVector	Paul Boddie	nobody	"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."	New feature	new	Database layer (models, ORM)	dev	Normal		PostgreSQL text search FTS coalesce SearchVector	Paul Boddie Simon Charette	Accepted	0	0	0	0	0	0