FK index created two times on PostgreSQL if referenced table PK is varchar

[Kamil Kujawiński]

Django version: 1.8.14

Scenario 1. with creating index only

1. Initial models:

class Foo(models.Model):
    guid = models.CharField(max_length=36, primary_key=True)


class Bar(models.Model):
    key = models.ForeignKey(Foo, db_index=False)

2. Initial migration:

class Migration(migrations.Migration):

    dependencies = []

    operations = [
        migrations.CreateModel(
            name='Bar',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
        ),
        migrations.CreateModel(
            name='Foo',
            fields=[
                ('guid', models.CharField(max_length=36, serialize=False, primary_key=True)),
            ],
        ),
        migrations.AddField(
            model_name='bar',
            name='key',
            field=models.ForeignKey(to='myapp.Foo', db_index=False),
        ),
    ]

3. Changes in to model:

-    key = models.ForeignKey(Foo, db_index=False)
+    key = models.ForeignKey(Foo, db_index=True)

4. Migration:

class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        migrations.AlterField(
            model_name='bar',
            name='key',
            field=models.ForeignKey(to='myapp.Foo'),
        ),
    ]

5. Applying migration logs:

Operations to perform:
  Apply all migrations: myapp
Running migrations:
  Rendering model states... DONE
  Applying myapp.0002_auto_20160829_1936...[2016-08-29 19:37:55,385 pid=11084] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" DROP CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_id"; (params [])
[2016-08-30 11:26:19,228 pid=25347] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" DROP CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_guid"; (params [])
[2016-08-30 11:26:19,230 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_uniq" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:26:19,230 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_uniq" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:26:19,235 pid=25347] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_guid" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("guid") DEFERRABLE INITIALLY DEFERRED; (params [])
[2016-08-30 11:26:19,235 pid=25347] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_guid" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("guid") DEFERRABLE INITIALLY DEFERRED; (params [])
[2016-08-30 11:26:19,237 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_like" ON "myapp_bar" ("key_id" varchar_pattern_ops); (params [])
[2016-08-30 11:26:19,237 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_like" ON "myapp_bar" ("key_id" varchar_pattern_ops); (params [])
 OK

6. Indexes created in postgres database

SELECT i.relname as indname, i.relowner as indowner, idx.indrelid::regclass, am.amname as indam, idx.indkey,
       ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) as k
             ORDER BY k) as indkey_names, idx.indexprs IS NOT NULL as indexprs, idx.indpred IS NOT NULL as indpred,
       idx.indisunique
FROM   pg_index as idx JOIN pg_class as i ON i.oid = idx.indexrelid JOIN pg_am as am ON i.relam = am.oid
WHERE  idx.indrelid::regclass='myapp_bar'::regclass;

                indname                 | indowner | indrelid  | indam | indkey | indkey_names | indexprs | indpred | indisunique 
----------------------------------------+----------+-----------+-------+--------+--------------+----------+---------+-------------
 myapp_bar_key_id_1647a22de55ad985_like |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_key_id_1647a22de55ad985_uniq |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_pkey                         |    16384 | myapp_bar | btree | 1      | {id}         | f        | f       | t

Scenario 2. indexes created in first migration

1. Initial models:

class Foo(models.Model):
    guid = models.CharField(max_length=36, primary_key=True)


class Bar(models.Model):
    key = models.ForeignKey(Foo, db_index=True)

2. Initial migration:

class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0033_auto_20160829_1931'),
    ]

    operations = [
        migrations.CreateModel(
            name='Bar',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
        ),
        migrations.CreateModel(
            name='Foo',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
        ),
        migrations.AddField(
            model_name='bar',
            name='key',
            field=models.ForeignKey(to='myapp.Foo'),
        ),
    ]

3. Applying migration logs:

Operations to perform:
  Apply all migrations: myapp
Running migrations:
  Rendering model states... DONE
  Applying myapp.0034_auto_20160830_1136...[2016-08-30 11:37:00,682 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_bar" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,682 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_bar" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,704 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_foo" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,704 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_foo" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,749 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD COLUMN "key_id" integer NOT NULL; (params [])
[2016-08-30 11:37:00,749 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD COLUMN "key_id" integer NOT NULL; (params [])
[2016-08-30 11:37:00,750 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ALTER COLUMN "key_id" DROP DEFAULT; (params [])
[2016-08-30 11:37:00,750 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ALTER COLUMN "key_id" DROP DEFAULT; (params [])
[2016-08-30 11:37:00,751 pid=28406] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_30f69126" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:37:00,751 pid=28406] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_30f69126" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:37:00,759 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_id" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("id") DEFERRABLE INITIALLY DEFERRED; (params [])
[2016-08-30 11:37:00,759 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_id" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("id") DEFERRABLE INITIALLY DEFERRED; (params [])
 OK

4. Indexes created in postgres database

SELECT i.relname as indname, i.relowner as indowner, idx.indrelid::regclass, am.amname as indam, idx.indkey,
       ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) as k
             ORDER BY k) as indkey_names, idx.indexprs IS NOT NULL as indexprs, idx.indpred IS NOT NULL as indpred,
       idx.indisunique
FROM   pg_index as idx JOIN pg_class as i ON i.oid = idx.indexrelid JOIN pg_am as am ON i.relam = am.oid
WHERE  idx.indrelid::regclass='myapp_bar'::regclass;

                indname                 | indowner | indrelid  | indam | indkey | indkey_names | indexprs | indpred | indisunique 
----------------------------------------+----------+-----------+-------+--------+--------------+----------+---------+-------------
 myapp_bar_30f69126                     |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_key_id_1647a22de55ad985_like |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_pkey                         |    16384 | myapp_bar | btree | 1      | {id}         | f        | f       | t
(3 rows)

Note: I've edited manually in logs app name and migration numbers. I hope it didn't intefere this test case.

[Tim Graham]

Could you please check if the issue still exists on Django master?

[Tim Graham]

I could reproduce at d8ef5b0e6501692b8b767ebccddc936f496d77e8.

[Tim Graham]

I'm not sure what I did 6 weeks ago when I confirmed the issue. I seem to remember that maybe it was the duplicated queries that appear in logging but I see that all queries are duplicated, so that seems to be caused by your logging setup. What do you mean by "FK index created two times"? There are a couple extra indexes on CharField due to #12234 -- are those the indexes that you're referring to?

[Maciej Gol]

Isn't this just how indexes on varchar/text columns work? The regular index is used for exact match queries, whilst the _like index is supporting the LIKE operator?

[Tim Graham]

If that's what meant by "FK index created two times", yes. Closing absent some further clarification from the reporter.

[Markus Holtermann]

Exactly what Maciej Gol said. VARCHAR fields with an db_index=True have 2 indexes. One for exact matches, one for LIKE.

[Kamil Kujawiński]

Replying to Markus Holtermann:

Exactly what Maciej Gol said. VARCHAR fields with an db_index=True have 2 indexes. One for exact matches, one for LIKE.

Agree.

I thought that those are same indexes with only different name. My pg query doesn't show any differences between them. I found in Django code that _like index is created with varchar_pattern_ops option.

So problem is with my pg query, not with Django.

Sorry for bother.