til: django_enabling-gin-index.md
This data as json
| path | topic | title | url | body | html | shot | created | created_utc | updated | updated_utc | shot_hash | slug |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| django_enabling-gin-index.md | django | Enabling a gin index for faster LIKE queries | https://github.com/simonw/til/blob/main/django/enabling-gin-index.md | I tried using a gin index to speed up `LIKE '%term%'` queries against a column. [PostgreSQL: More performance for LIKE and ILIKE statements](https://www.cybertec-postgresql.com/en/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: ```sql CREATE EXTENSION pg_trgm; ``` And then create an index like this: ```sql 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: ```python 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 `Meta` class: ```python 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"], ), ] ``` 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. | <p>I tried using a gin index to speed up <code>LIKE '%term%'</code> queries against a column.</p> <p><a href="https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/" rel="nofollow">PostgreSQL: More performance for LIKE and ILIKE statements</a> provided useful background. The raw-SQL way to do this is to install the extension like so:</p> <div class="highlight highlight-source-sql"><pre>CREATE EXTENSION pg_trgm;</pre></div> <p>And then create an index like this:</p> <div class="highlight highlight-source-sql"><pre><span class="pl-k">CREATE</span> <span class="pl-k">INDEX</span> <span class="pl-en">idx_gin</span> <span class="pl-k">ON</span> mytable USING gin (mycolumn gin_trgm_ops);</pre></div> <p>This translates to two migrations in Django. The first, to enable the extension, looks like this:</p> <div class="highlight highlight-source-python"><pre><span class="pl-k">from</span> <span class="pl-s1">django</span>.<span class="pl-s1">contrib</span>.<span class="pl-s1">postgres</span>.<span class="pl-s1">operations</span> <span class="pl-k">import</span> <span class="pl-v">TrigramExtension</span> <span class="pl-k">from</span> <span class="pl-s1">django</span>.<span class="pl-s1">db</span> <span class="pl-k">import</span> <span class="pl-s1">migrations</span> <span class="pl-k">class</span> <span class="pl-v">Migration</span>(<span class="pl-s1">migrations</span>.<span class="pl-v">Migration</span>): <span class="pl-s1">dependencies</span> <span class="pl-c1">=</span> [ (<span class="pl-s">"blog"</span>, <span class="pl-s">"0014_entry_custom_template"</span>), ] <span class="pl-s1">operations</span> <span class="pl-c1">=</span> [<span class="pl-v">TrigramExtension</span>()]</pre></div> <p>Then to configure the index for a model you can add this to the model's <code>Meta</code> class:</p> <div class="highlight highlight-source-python"><pre><span class="pl-k">class</span> <span class="pl-v">Entry</span>(<span class="pl-s1">models</span>.<span class="pl-v">Model</span>): <span class="pl-s1">title</span> <span class="pl-c1">=</span> <span class="pl-s1">models</span>.<span class="pl-v">CharField</span>(<span class="pl-s1">max_length</span><span class="pl-c1">=</span><span class="pl-c1">255</span>) <span class="pl-s1">body</span> <span class="pl-c1">=</span> <span class="pl-s1">models</span>.<span class="pl-v">TextField</span>() <span class="pl-k">class</span> <span class="pl-v">Meta</span>: <span class="pl-s1">indexes</span> <span class="pl-c1">=</span> [ <span class="pl-v">GinIndex</span>( <span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s">"idx_blog_entry_body_gin"</span>, <span class="pl-s1">fields</span><span class="pl-c1">=</span>[<span class="pl-s">"body"</span>], <span class="pl-s1">opclasses</span><span class="pl-c1">=</span>[<span class="pl-s">"gin_trgm_ops"</span>], ), ]</pre></div> <p>The <code>opclasses=["gin_trgm_ops"]</code> line is necessary to have the same efect as the <code>CREATE INDEX</code> statement shown above. The <code>name=</code> option is required if you specify <code>opclasses</code>.</p> <p>Run <code>./manage.py makemigrations</code> and Django will automatically create the correct migration to add the new index.</p> <p>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.</p> | <Binary: 62,932 bytes> | 2021-05-16T17:59:05-07:00 | 2021-05-17T00:59:05+00:00 | 2021-05-16T17:59:05-07:00 | 2021-05-17T00:59:05+00:00 | ab4106d1cd70b2fabb2cb63117d18edd | enabling-gin-index |