I would be strongly against misusing EXPLAIN like that inside Django, and I feel keyset/cursor pagination is best if your going down this road.
I've got no concrete evidence for this opinion, but I feel like navigating to an exact page is very rarely used and only then as a proxy for range filtering on a column (i.e "page 5 contains columns within the range I want"). On Sun, 23 Dec 2018, 12:41 Tim Allen <flip...@peregrinesalon.com wrote: > On Friday, December 21, 2018 at 10:18:04 AM UTC-5, Cristiano Coelho wrote: >> >> Let's not forget how the various *count *calls starts to kill your >> database when you get over 1 million rows (postgres at least). >> >> So far the only options I have found with postgres are: >> - Estimate count for non filtered queries: SELECT reltuples::BIGINT FROM >> pg_class WHERE relname = '%s'; >> - If queries are filtered, replace it with a subquery that will first >> limit the results to a reasonable number (such as 1k), this is silly, and >> won't allow you to go through all results but at least the count call won't >> kill your database. This is only useful if the filtered query returns over >> one million rows as well. >> > > I had this problem with very large tables of data being presented as > endpoints through DRF. For filtered queries estimate, we parse the EXPLAIN > syntax to get an approximate rowcount, which handles filtered queries. It > is by no means perfect, but allows us to have next / previous pagination > while using the EXPLAIN approximate count for an estimated total. We then > keep showing NEXT until there's a page with no results. More frequent > vacuums of PostgreSQL are another option for improving accurance of the > estimate count you mention. We subclassed DRF's limit/offset pagination and > overrode the count() method to accomplish this. It might be an option to > consider for Django pagination as well, especially with EXPLAIN now being > available through the ORM in 2.1: > https://docs.djangoproject.com/en/2.1/ref/models/querysets/#explain > > -- > 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 email@example.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/fb3fd1b6-75ed-454d-85c3-ba0672e5368f%40googlegroups.com > <https://groups.google.com/d/msgid/django-developers/fb3fd1b6-75ed-454d-85c3-ba0672e5368f%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 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/CAFNZOJMXrK_YVgCV714Gc4updRf3PNVyy31U15DyGhdLdPKQFw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.