On 25 March 2013 10:58, Tim Chase <django.us...@tim.thechases.com> wrote: > On 2013-03-25 03:40, Anssi Kääriäinen wrote: >> I am very likely going to change the ORM to use EXISTS subqueries >> instead of IN subqueries. I know this is a good idea on PostgreSQL >> but I don't have enough experience of other databases to know if >> this is a good idea or not. > > I can only speak for testing IN-vs-EXISTS speed on MSSQLServer at > $OLD_JOB, but there it's usually about the same, occasionally with IN > winning out. However, the wins were marginal, and MSSQL is a 2nd-class > citizen in the Django world, so I'm +1 on using EXISTS instead of IN, > if the results are assured to be the same.
The results are definitely different because NOT IN has some quite strange characteristics: if the subquery returns a NULL then the whole result is "unknown". It is that weirdness that makes it hard to optimize for, or at least, not-yet-optimized for in PostgreSQL. In most cases it is the NOT EXISTS behaviour that people find natural and normal anyway and that is the best mechanism to use. > However, the query constuction to move the condition into the EXISTS > subclause might be a bit more complex. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- You received this message because you are subscribed to the Google Groups "Django developers" 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 http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
