I tried using a gin index to speed up
LIKE '%term%' queries against a column.
PostgreSQL: More performance for LIKE and ILIKE statements provided useful background. The raw-SQL way to do this is to install the extension like so:
CREATE EXTENSION pg_trgm;
And then create an index like this:
CREATE INDEX idx_gin ON mytable USING gin (mycolumn gin_trgm_ops);
This translates to two migrations in Django. The first, to enable the extension, looks like this:
from django.contrib.postgres.operations import TrigramExtension from django.db import migrations class Migration(migrations.Migration): dependencies = [ ("blog", "0014_entry_custom_template"), ] operations = [TrigramExtension()]
Then to configure the index for a model you can add this to the model's
class Entry(models.Model): title = models.CharField(max_length=255) body = models.TextField() class Meta: indexes = [ GinIndex( name="idx_blog_entry_body_gin", fields=["body"], opclasses=["gin_trgm_ops"], ), ]
opclasses=["gin_trgm_ops"] line is necessary to have the same efect as the
CREATE INDEX statement shown above. The
name= option is required if you specify
./manage.py makemigrations and Django will automatically create the correct migration to add the new index.
I ended up not shipping this for my blog because with less than 10,000 rows in the table it made no difference at all to my query performance.
Created 2021-05-16T17:59:05-07:00 · Edit