Re: Implicit ForeignKey index and unique_together
I agree - #24082 is unrelated since it's PostgresSQL and text field specific, presumably resulting from: https://github.com/django/django/blob/8eb56f3c786b4dd7f78a60145ae2e483e24b62c1/django/db/backends/postgresql/schema.py#L34-L48 The issue in this thread is specific to the interaction between unique_together and a ForeignKey, where unless an explicit `db_index=False` is set on the foreign key, Django creates a redundant index (under MySQL at least, haven't checked the others). However this appears fixed on master - I thought originally it might have been inadvertently fixed by this refactor: https://github.com/django/django/commit/6bf7964023487f2a352084e74aca27aecb354d6c ...but that commit was backported to 1.10.x, and this reproduces on Django 1.10.3 so it can't be. (If anyone wanted to try and bisect, a reduced testcase is here: https://emorley.pastebin.mozilla.org/8933048) As mentioned below, the workaround is to add an explicit `db_index=False` to the `ForeignKey`, however if that's done to an existing `ForeignKey`, the migration actually drops and recreates the constraint rather than just dropping the index, which is pretty bad since the latter is an performed as an online DDL operation with InnoDB, whereas adding a constraint is not. As such, I've filed: https://code.djangoproject.com/ticket/27558 Best wishes, Ed On Saturday, 17 September 2016 01:48:15 UTC+1, Cristiano Coelho wrote: > > I think that the issue on Trac is actually something different, it talks > about the need (or not) of an index, when defining a unique constraint. > Most databases (if not all) will create an index automatically when a > unique constraint is defined, and correct me if I'm wrong, but PostgreSQL > (I don't about Oracle) is the only one that actually has constraints > (unique ones included here) and indexes as a separate thing, but for > SQLServer and MySQL the unique constraint is just an additional option of > the index. > > What Dilyan is talking about, and correct me if I'm wrong again, is about > the redundancy of defining an index on a foreing key, if you already have > that column as the left-most part of an index (unique or not). Most of the > time it will be redundant to have an index A, and another one (A,B), since > the latter will be also used for A queries. However this is up to debate > since using the (A,B) index can be potentially slower than using just the A > index due to the index being bigger, but you save space and > insert/update/delete performance for not having two different indexes. > > In my case, most of the time I end up with a db_index=False on foreing > keys that I know I have a index/unique defined somewhere else to avoid the > overhead of the additional index. > > El viernes, 16 de septiembre de 2016, 11:34:52 (UTC-3), Tim Graham > escribió: >> >> Did you try to find anything related in Trac? Maybe >> https://code.djangoproject.com/ticket/24082? >> >> I use this query in Google: postgresql unique index site: >> code.djangoproject.com >> >> On Friday, September 16, 2016 at 9:51:13 AM UTC-4, Dilyan Palauzov wrote: >>> >>> Hello, >>> >>> according to the documentation models.ForeignKeys creates implicitly an >>> index on the underlying database. >>> >>> Wouldn't it be reasonable to change the default behaviour to only create >>> implicit index, if there is no index_together or unique_together starting >>> with the name of the foreign key? In such cases the implicit index is >>> redundant, at least with Postgresql, as the value can be found fast using >>> the _together index. >>> >>> Greetings >>>Dilian >>> >> -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/2bc94b77-0b32-4df4-8d05-21e3c811b4a9%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: Implicit ForeignKey index and unique_together
I think that the issue on Trac is actually something different, it talks about the need (or not) of an index, when defining a unique constraint. Most databases (if not all) will create an index automatically when a unique constraint is defined, and correct me if I'm wrong, but PostgreSQL (I don't about Oracle) is the only one that actually has constraints (unique ones included here) and indexes as a separate thing, but for SQLServer and MySQL the unique constraint is just an additional option of the index. What Dilyan is talking about, and correct me if I'm wrong again, is about the redundancy of defining an index on a foreing key, if you already have that column as the left-most part of an index (unique or not). Most of the time it will be redundant to have an index A, and another one (A,B), since the latter will be also used for A queries. However this is up to debate since using the (A,B) index can be potentially slower than using just the A index due to the index being bigger, but you save space and insert/update/delete performance for not having two different indexes. In my case, most of the time I end up with a db_index=False on foreing keys that I know I have a index/unique defined somewhere else to avoid the overhead of the additional index. El viernes, 16 de septiembre de 2016, 11:34:52 (UTC-3), Tim Graham escribió: > > Did you try to find anything related in Trac? Maybe > https://code.djangoproject.com/ticket/24082? > > I use this query in Google: postgresql unique index site: > code.djangoproject.com > > On Friday, September 16, 2016 at 9:51:13 AM UTC-4, Dilyan Palauzov wrote: >> >> Hello, >> >> according to the documentation models.ForeignKeys creates implicitly an >> index on the underlying database. >> >> Wouldn't it be reasonable to change the default behaviour to only create >> implicit index, if there is no index_together or unique_together starting >> with the name of the foreign key? In such cases the implicit index is >> redundant, at least with Postgresql, as the value can be found fast using >> the _together index. >> >> Greetings >>Dilian >> > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3200c618-5665-4c9e-8255-ef34da22aef1%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: Implicit ForeignKey index and unique_together
Did you try to find anything related in Trac? Maybe https://code.djangoproject.com/ticket/24082? I use this query in Google: postgresql unique index site:code.djangoproject.com On Friday, September 16, 2016 at 9:51:13 AM UTC-4, Dilyan Palauzov wrote: > > Hello, > > according to the documentation models.ForeignKeys creates implicitly an > index on the underlying database. > > Wouldn't it be reasonable to change the default behaviour to only create > implicit index, if there is no index_together or unique_together starting > with the name of the foreign key? In such cases the implicit index is > redundant, at least with Postgresql, as the value can be found fast using > the _together index. > > Greetings >Dilian > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/53e1efb0-381a-40ca-874f-763d9f09e8d1%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Implicit ForeignKey index and unique_together
Hello, according to the documentation models.ForeignKeys creates implicitly an index on the underlying database. Wouldn't it be reasonable to change the default behaviour to only create implicit index, if there is no index_together or unique_together starting with the name of the foreign key? In such cases the implicit index is redundant, at least with Postgresql, as the value can be found fast using the _together index. Greetings Dilian -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d3ca65cc-5146-15ca-a54d-e08cf0b98cd4%40aegee.org. For more options, visit https://groups.google.com/d/optout.
