I think most people would typically sort/paginate on the primary key field, which still exhibits a linear scan, where the first few pages are fast and the last few pages take significantly longer. Just wanted to call that out in case there were listeners thinking an index solves the problem. Sorting by an unindexed field just makes a bad query a horrible one.
On Thursday, 6 December 2018 07:32:47 UTC+11, Curtis Maloney wrote: > > On 12/5/18 8:30 PM, Saleem Jaffer wrote: > > Hi all, > > > > The default paginator that comes with Django is inefficient when dealing > > with large tables. This is because the final query for fetching pages > > uses "OFFSET" which is basically a linear scan till the last index of > > the current page. Does it make sense to have a better paginator which > > does not use "OFFSET". > > > > If this sounds like a good idea, I have some ideas on how to do it and > > with some help from you guys I can implement it. > > There are a number of alternatives to this, as well as low-cost > solutions to improve OFFSET / LIMIT pagination. > > By adding an index to the sorting field(s), it can drastically improve > the "simple" case. > > Beyond that, you start getting into cases with more significant > trade-offs. I know Matthew Schinckel was recently working on a drop-in > replacement paginator that used "keyset pagination" > https://bitbucket.org/schinckel/django-keyset-pagination > > There's a lot of published work on this topic, and I'd be very > interested to see, at least, a library implementing some of these > alternatives. > > At the very least, we could want to ensure the documentation recommends > indexing on the ORDERing fields, where possible. > > -- > Curtis > -- 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 firstname.lastname@example.org. 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/ea4f87d6-5b9a-4f82-9486-7a7f4b094607%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.