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 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/fb3fd1b6-75ed-454d-85c3-ba0672e5368f%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
