Automatic indexes on foreign keys

[Aryeh Leib Taurog]

With the following models:

class Group(models.Model):
group_name = models.CharField(max_length=10, primary_key=True)

class Item(models.Model):
item_name = models.CharField(max_length=10)
group = models.ForeignKey(Group)
class Meta:
unique_together = [('item_name','group')]

Django's ORM seems to create *two* indexes automatically:
CREATE INDEX "myapp_item_group_id" ON "myapp_item" ("group_id");
CREATE INDEX "myapp_item_group_id_like" ON "myapp_item" ("group_id"
varchar_pattern_ops);

Two!? Is this really necessary? My understanding is that one usually
wants an index on the *referenced* field, not the *referencing*
field. In my case the referenced field is indexed implicitly by the
db, since it is already a primary key. I am looking for ways to
improve insert performance on the referencing table, and my
unique_together constraint already implies all the index I need on
this table. Is there any reason why I shouldn't drop the indexes
django creates for me?

[Javier Guerra Giraldez]

On Fri, Mar 23, 2012 at 4:37 AM, Aryeh Leib Taurog <v...@aryehleib.com> wrote:
> My understanding is that one usually
> wants an index on the *referenced* field, not the *referencing*
> field.

it's for the back-reference link. so that you can do
group.item_set.all() and get all the items that share a group.

yes, the unique_together index implies the other one and could be
used, but Django doesn't do that analysis for you. yes, it seems you
could drop the index, but be sure to test if any of your queries is
affected.

--
Javier

[Aryeh Leib Taurog]

On Mar 23, 3:56 pm, Javier Guerra Giraldez <jav...@guerrag.com> wrote:
> On Fri, Mar 23, 2012 at 4:37 AM, Aryeh Leib Taurog <v...@aryehleib.com> wrote:
>
> > My understanding is that one usually
> > wants an index on the *referenced* field, not the *referencing*
> > field.
>
> it's for the back-reference link.  so that you can do
> group.item_set.all() and get all the items that share a group.

Ah, okay. Yes, this would definitely improve performance of that
query. But why the second index (varchar_pattern_ops in postgresql)?

> yes, the unique_together index implies the other one and could be
> used, but Django doesn't do that analysis for you.  yes, it seems you
> could drop the index, but be sure to test if any of your queries is
> affected.

Thanks!

[Ramiro Morales]

On Sun, Mar 25, 2012 at 9:30 AM, Aryeh Leib Taurog <v...@aryehleib.com> wrote:
>
> On Mar 23, 3:56 pm, Javier Guerra Giraldez <jav...@guerrag.com> wrote:
> > On Fri, Mar 23, 2012 at 4:37 AM, Aryeh Leib Taurog <v...@aryehleib.com>
> > wrote:
> >
> > > My understanding is that one usually
> > > wants an index on the *referenced* field, not the *referencing*
> > > field.
> >
> > it's for the back-reference link.  so that you can do
> > group.item_set.all() and get all the items that share a group.
>
> Ah, okay.  Yes, this would definitely improve performance of that
> query.  But why the second index (varchar_pattern_ops in postgresql)?

See this ticket

https://code.djangoproject.com/ticket/12234

and the commit that fixed it:

https://code.djangoproject.com/changeset/11912

--
Ramiro Morales