A sequence scan will only be made, if you query non indexed values. So if you add a simple ORDER BY you will make a index scan, which is very fast. The problem relies more on the database than on the ORM. As already said. If you need to deal with that much queries you need to log your SQL statements and need to optimize them. Nothing to do with django at all. We ran into several performance issues of our applications as well even at something like 10.000 or 100.000 entries per table and it was quite obvious that the problem relied on the database/queries.
Also we learned that using the cache gives you a big performance win. "The best query you could have, is the query you never need to make" I don't think its good to make django more safely when dealing with high performance systems, since it will make the system more complex and django is already really complex inside the core orm. 2014-11-19 13:59 GMT+01:00 Marco Paolini <markopaol...@gmail.com>: > also, the offset + limit pagination strategy of django paginator is > sub-optimal as it has N complexity: doing SELECT * FROM auth_user LIMIT 100 > offset 1000000 causes a 100000-long table scan > > 2014-11-19 13:56 GMT+01:00 Marco Paolini <markopaol...@gmail.com>: > >> >> >> 2014-11-19 13:50 GMT+01:00 Rick van Hattem <wolp...@gmail.com>: >> >>> Definitely agree on this, silently altering a query's limit is probably >>> not the way to go. Raising an exception in case of no limit and lots of >>> results could be useful. >>> >>> For the sake of keeping the discussion useful: >>> - Let's say you have a table with 50,000 items, not an insanely large >>> amount imho. >>> - Now someone does a non-restricted loop through "Model.objects.all()" >>> which would return 50,000 items. >>> >> even a restricted loop causes troubles (at least in postgres). >> >> if you do: >> >> for i, m in enumerate(Model.objects.all()): >> if i > 4: >> break >> >> then psycopg will fetch (and cache locally) the entire cursor resultset. >> Of course django orm will only fetch the first batch (100 items I think) >> from the psycppg cursor, bu that does not solve the underlying issue. >> >> There are options to have server-side cursors in psycopg but django does >> not use them AFAIK >> >> see this thread >> https://groups.google.com/forum/#!searchin/django-developers/psycopg$20results/django-developers/8ZBA4uMinXo/M1osYTGmvbUJ >> >> >> >> >> - In what case would this be desirable as opposed to limiting to 10,000 >>> items and raising an error when the database actually returns 10,000 items. >>> >>> Naturally this case would only apply if no slice is given, but if you're >>> really processing over 10,000 items in a single loop you probably know how >>> to slice the queryset when needed. >>> Perhaps something like this: raise QuerysetTooLarge('Your query returned >>> over {LIMIT} results, if this is intentional please slice the queryset') >>> >>> Not saying this should be a default straight away but having a default >>> of 10,000 or even 100,000 should not hurt anyone and protects against >>> killing a server which is always a positive result in my book. >>> >>> On Wednesday, November 19, 2014 1:42:59 AM UTC+1, Josh Smeaton wrote: >>>> >>>> To me, "sane default" means django should not silently alter the query >>>> to provide a LIMIT when it is not asked for. >>>> >>>> I have also run into situations where doing a .count() or iterating a >>>> full table has broken the application, or put too much pressure on the >>>> database. Specifically with django bindings to javascript datatables. But I >>>> still wouldn't want artificial limiting on such queries. >>>> >>>> What *may* be useful, is to be able to apply queryset methods onto an >>>> already sliced queryset. That would allow users to implement >>>> queryset/manager methods that provide pre-sliced querysets to the rest of >>>> the code. The problem would be, what should happen in this case? >>>> >>>> Model.objects.all()[0:10].filter(field=True) >>>> >>>> Should the filter be logically/internally moved to before the limit? Or >>>> should the filter be applied to the result of the limit in an outer query? >>>> Traditionally, django applies mutations in succession, but this wouldn't be >>>> very useful the for the majority of operations that would occur "after" the >>>> slicing. We *could* say all slicing is "saved" and applied at the end, but >>>> we'd definitely run into issues with users reporting that filtering isn't >>>> working as they expect - after the slice. >>>> >>> -- >>> 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 http://groups.google.com/group/django-developers. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/django-developers/96868573-1c37-44a3-9a2a-61df62994308%40googlegroups.com >>> <https://groups.google.com/d/msgid/django-developers/96868573-1c37-44a3-9a2a-61df62994308%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> > -- > 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 http://groups.google.com/group/django-developers. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-developers/CAEbA68nhESxaAf44x1R%2BhffNd5YnVLMJ-HQK88Lv_bPr%3DxZoYA%40mail.gmail.com > <https://groups.google.com/d/msgid/django-developers/CAEbA68nhESxaAf44x1R%2BhffNd5YnVLMJ-HQK88Lv_bPr%3DxZoYA%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > For more options, visit https://groups.google.com/d/optout. > -- 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 http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAPDLAU6p1ByCHvFfpD%2BqQHE7b%3Driu6qXwcTe4SThCzBQVqRpng%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
