CODEWITHSUNDEEP
pythondjangopostgresql
lollercoaster
lollercoaster

Reputation: 16523

Optimizing postgres contains (LIKE) query in django for CHAR field

So I've got a query for a search endpoint in my Django (postgres) backend like so:

widgets = Widgets.objects.\
    filter(user=request.user).\
    filter(title__icontains="marketing director").\
    distinct('url')[:250]

title is a CHAR field (models.CharField(max_length=255, blank=True) in django).

contains here of course amounts to a '%LIKE%' query in Postgres. I want specifically this (ie: NOT an infix 'LIKE%' search).

I'd like to speed up that query.

It seems like most optimizations for postgres text search are only for TEXT fields - is there any way to speed up exact string searches on a CHAR column?

I could use Postgres trigram indices, but I don't actually need the fuzzy/mispelling-type search. Though I'd happily use it if it's actually just faster for some reason.

Or would I be better off converting those columns to TEXT, taking the storage increase hit, and better indexing them somehow?

Upvotes: 5

Views: 2221

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247575

The only way so use an index with a LIKE pattern that starts with % (is not anchored at the beginning) is to use a trigram index.

It does not matter if you define the attributes as character or text, since they will be converted to text anyway.

Do your data contain a lot of trailing blanks? Other than that, I cannot see how text could waste storage space.

Upvotes: 3

Related Questions