On 26 maalis, 07:16, Michael Manfre <mman...@gmail.com> wrote: > On Monday, March 25, 2013 6:58:12 AM UTC-4, Tim Chase wrote: > > > 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. > > In SQL 2008r2, the optimizer is usually smart enough to end up with the > same execution plan for IN and EXISTS queries. Historically, EXISTS was > usually the faster operation for SQL Server and if memory serves it had to > deal with its ability to bail out of the EXISTS query sooner compared to > the IN query. > > MSSQL is a 2nd-class citizen in the Django world, so I'm +1 > > > > Reasoning like that helps to keep it in its place. > > Anssi, > > Any chance of adding a new database feature to flip the behavior of __in to > either IN or EXISTS? Sounds like this change of logical and documented > behavior is being made specifically because of failings with Postgresql. > The feature would also help satisfy the deprecation cycle normally used for > changes to documented behaviors. Sub-queries are more likely to expose > database specific issues with the SQL provided by Django (normally when > used with aggregates or slicing). Adding the database feature might save > every other backend from having to potentially jump > through unnecessary hoops (mangling more SQL).
Yes, this is a possibility and I think I will take this path. This adds a bit of complexity to the code but not much. There is already support for both IN and EXISTS subqueries in the ORM (as of couple of days ago). In addition I will change the __in lookup to exclude NULL values from the inner query so that EXISTS and IN give the same results. By default PostgreSQL and SQLite will get EXISTS as preferred subquery type. For other databases it will be easy to flip the feature flag if it seems EXISTS is better than IN for the DB. - Anssi -- 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.
