Enabling a gin index for faster LIKE queries#

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:

1
CREATE EXTENSION pg_trgm;

And then create an index like this:

1
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:

1
from django.contrib.postgres.operations import TrigramExtension
2
from django.db import migrations
3

4

5
class Migration(migrations.Migration):
6

7
    dependencies = [
8
        ("blog", "0014_entry_custom_template"),
9
    ]
10

11
    operations = [TrigramExtension()]

Then to configure the index for a model you can add this to the model’s Meta class:

1
class Entry(models.Model):
2
    title = models.CharField(max_length=255)
3
    body = models.TextField()
4

5
    class Meta:
6
        indexes = [
7
            GinIndex(
8
                name="idx_blog_entry_body_gin",
9
                fields=["body"],
10
                opclasses=["gin_trgm_ops"],
11
            ),
12
        ]

The 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 opclasses.

Run ./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.