How does this query look like? Not sure if this is gonna help, but you can take a look at Exists() subqueries <https://docs.djangoproject.com/en/1.11/ref/models/expressions/#exists-subqueries>
On Wednesday, September 6, 2017 at 4:29:21 PM UTC+3, Stephan Seyboth wrote: > > Sorry for resurrecting this ancient thread, but it was the last direct > mention I could find about this topic. > > Curious to hear what happened to Anssi's proposal to change the ORM to use > EXISTS subqueries instead of IN subqueries. Looks like the ORM still uses > IN as of Django 1.11.4. > > One of our queries just started running over 1000x slower from one day to > the next b/c of the issues Postgres has with IN. > > Are there any suggested workarounds that don't require resorting to raw > SQL? > > On Tuesday, March 26, 2013 at 5:40:35 PM UTC+1, Anssi Kääriäinen wrote: >> >> On Tuesday, March 26, 2013 5:19:52 PM UTC+2, Michael Manfre wrote: >>> >>> >>> >>> On Tue, Mar 26, 2013 at 10:23 AM, Tim Chase <django...@tim.thechases.com >>> > wrote: >>> >>>> EXISTS also has some nice features >>>> like the ability to do testing against multiple columns, i.e., you >>>> can't do something like >>>> >>>> select * >>>> from tbl1 >>>> where (col1, col2) in ( >>>> select a, b >>>> from tbl2 >>>> ) >>>> >>>> but that's a simple EXISTS query. >>>> >>> >>> Agreed, EXISTS is more flexible and at least to me, often easier to >>> read, format, and maintain. Assuming this is implemented with a database >>> feature, I'll most likely enable the EXISTS change for django-mssql. My >>> main objections to a blanket change is to not have the specific behaviors >>> of one database dictate how every other database must behave. Some one >>> needs to be the voice of 3rd party database backends and it appears for the >>> time being, I'm wearing that hat. >>> >> >> One of the main reasons for the change is that EXISTS allows for queries >> that are impossible with IN. In addition EXISTS semantics regarding NULLs >> is wanted. And, at least PostgreSQL performs better with NOT EXISTS than >> NOT IN. Granted, the better performance on PostgreSQL is perhaps the most >> important reason for me, but this change is not only about that. >> >> The main reason for this thread was to find out if there are some >> databases where performance of EXISTS is worse than IN. The DB feature >> approach seems good because it allows investigating performance >> characteristics one DB at time. >> >> >>> >>>> > > MSSQL is a 2nd-class citizen in the Django world, so I'm +1 >>>> > >>>> > Reasoning like that helps to keep it in its place. >>>> >>>> MSSQL's lack of certain core features is what does that. >>>> OFFSET/LIMIT provided by pretty much every other DB vendor? Use >>>> baroque hacks to get the same functionality. >>> >>> >>> The non "baroque hack" way of doing limit offset is with nested selects. >>> Maybe someday the non-standard LIMIT/OFFSET keywords will get added to the >>> standard (I truly hope this happens) so Oracle, MSSQL, DB2, and Informix >>> could share SQL with postgres and mysql without needing to mangle it. >>> >> >> Hmmh, this means Oracle, MSSQL, DB2 and Informix are doing more or less >> the same thing for limit/offset support? If so, then having a more generic >> approach to this problem than having a custom compiler per backend might be >> worth it... >> >> BTW there is already something like LIMIT and OFFSET in SQL 2008 >> standard. The syntax is different than LIMIT/OFFSET, and supported only by >> some vendors... See >> https://en.wikipedia.org/wiki/Select_%28SQL%29#FETCH_FIRST_clause >> >> - Anssi >> > -- 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/43e19ef1-5a07-4608-94f4-82ba10ad9a85%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
