title: How can I create a case-insensitive database index in Django?
body: <p>I am using Django to create some database tables, as so:</p>
  
  <pre><code>class MetadataTerms(models.Model):
      term = models.CharField(max_length=200)
      size = models.IntegerField(default=0)
      validity = models.IntegerField(default=0, choices=TERM_VALIDITY_CHOICES)
  </code></pre>
  
  <p>I am then running lookup queries to find the appropriate row with the correct <code>term</code>, matched in a case-insensitive way. E.g.:</p>
  
  <pre><code>MetadataTerms.objects.filter(term__iexact=search_string, size=3)
  </code></pre>
  
  <p>This lookup clause translates to something like so in SQL:</p>
  
  <pre><code> SELECT "app_metadataterms"."id", "app_metadataterms"."term", "app_metadataterms"."size" FROM "app_metadataterms" WHERE (UPPER("app_metadataterms"."term"::text) = UPPER('Jack Nicklaus survives')  AND "app_metadataterms"."size" = 3 );
  </code></pre>
  
  <p>On Postgres, I can perform an <code>EXPLAIN</code> query on the above, and I get this query plan:</p>
  
  <pre><code>                                    QUERY PLAN
  -----------------------------------------------------------------------------------
   Seq Scan on app_metadataterms  (cost=0.00..1233.01 rows=118 width=21)
     Filter: ((size = 3) AND (upper((term)::text) = 'JACK NICKLAUS SURVIVES'::text))
  </code></pre>
  
  <p>Because the <code>term</code> field is not indexed, and is not indexed in a case-normalized way, the above query needs to perform a slow Seq[uential] Scan operation across all database rows.</p>
  
  <p>Then I insert a simple case-normalized index, e.g.:</p>
  
  <pre><code> CREATE INDEX size_term_insisitive_idx ON app_metadataterms (upper(term), size);
  </code></pre>
  
  <p>The above query now runs about 6x faster:</p>
  
  <pre><code>                                         QUERY PLAN
  ---------------------------------------------------------------------------------------------
   Bitmap Heap Scan on app_metadataterms  (cost=5.54..265.15 rows=125 width=21)
     Recheck Cond: ((upper((term)::text) = 'JACK NICKLAUS SURVIVES'::text) AND (size = 3))
     -&gt;  Bitmap Index Scan on size_term_insisitive_idx  (cost=0.00..5.51 rows=125 width=0)
           Index Cond: ((upper((term)::text) = 'JACK NICKLAUS SURVIVES'::text) AND (size = 3))
  </code></pre>
  
  <p>My question is: how can I inject the creation of advanced DB indexes into the Django model management commands?</p>