Re: Automatic indexes on foreign keys
On Sun, Mar 25, 2012 at 9:30 AM, Aryeh Leib Taurog wrote: > > On Mar 23, 3:56 pm, Javier Guerra Giraldez wrote: > > On Fri, Mar 23, 2012 at 4:37 AM, Aryeh Leib Taurog > > 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 -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: Automatic indexes on foreign keys
On Mar 23, 3:56 pm, Javier Guerra Giraldez wrote: > On Fri, Mar 23, 2012 at 4:37 AM, Aryeh Leib Taurog 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! -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: Automatic indexes on foreign keys
On Fri, Mar 23, 2012 at 4:37 AM, Aryeh Leib Taurog 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 -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Automatic indexes on foreign keys
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?
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
