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.
  • A... Saleem Jaffer
    • ... ludovic coues
      • ... Jason Johns
        • ... Adam Johnson
    • ... Curtis Maloney
      • ... Josh Smeaton
    • ... Kye Russell
      • ... Dan Davis
    • ... 'Ivan Anishchuk' via Django developers (Contributions to Django itself)
    • ... Cristiano Coelho
      • ... Tim Allen
        • ... Tom Forbes

Reply via email to