On 25 March 2013 10:58, Tim Chase <[email protected]> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
