We use the explain analyze method at work, but I don't think it's an appropriate thing to include in core.
I agree that these changes should be a function of the paginator and that being able to pass in a could also be very useful. I'm not so sure about providing count estimates in core, but I don't fully grok how the `estimate_above` would work in practise. Would that execute two queries if the estimate returned a value below the threshold? I think this could be tackled in a separate patch because it would then feed into the expansion of the paginator. On Tuesday, 8 August 2017 07:46:19 UTC+10, Tom Forbes wrote: > > Mysql, Oracle and Postgres expose estimated rows in a pretty similar > manner (selecting the table name from a database-specific table). Perhaps a > more generic 'estimate_table_rows' function could be added for all > backends? I guess for sqlite it would have to use an actual count() as a > fallback. This could cause issues if people develop on sqlite and find the > function returns accurate row counts and this changes in production. > > The problem is these methods only return estimates for the entire table, > not including any filters, which narrows down the usefulness a lot. The > postgres wiki suggests 'scraping' the output of `EXPLAIN ANALYZE` which > sounds rather too hacky to add? > > On Mon, Aug 7, 2017 at 9:53 PM, Shai Berger <sh...@platonix.com > <javascript:>> wrote: > >> On PG we can and should do much better -- there is a way to get a very >> fast, >> though not accurate, count of records in a table: >> >> https://wiki.postgresql.org/wiki/Count_estimate >> >> I think we should expose an API along the lines of >> >> queryset.table_count(estimate_above=2000) >> >> which actually performs the COUNT(*) only if the estimate is under the >> threshold, at least where such facilities are available. We could use >> that by >> default for the admin count queries, if the queryset has no filtering or >> limiting joins. IMO, this alone would solve a whole lot of the problem; >> and >> allowing it to be also used explicitly (e.g. by adding Tom's idea to >> allow the >> user to specify the number of objects) would solve a whole lot of the >> rest of >> the problem. >> >> On Monday 07 August 2017 15:03:15 Tom Forbes wrote: >> > This is a great idea. A related issue I've come across with the >> paginator >> > is not being able to pass an explicit count into it. >> > >> > If you have a query set with expensive annotations that don't effect the >> > count in any way it's very wasteful to include them in the count SQL >> > statement (on top of general PG count slowness), which Django does. >> Being >> > able to pass in 'num_objects=x' would be great. >> > >> > On 7 Aug 2017 08:03, "Adam Johnson" <m...@adamj.eu <javascript:>> >> wrote: >> > >> > +1 from me, I've spent long enough on those COUNT(*) queries at work. >> > >> > Is it worth putting this logic in Paginator so applications can reuse >> it? >> > >> > On 7 August 2017 at 07:45, Josh Smeaton <josh.s...@gmail.com >> <javascript:>> wrote: >> > > The admin list page performs a count(*) (twice!) when viewing the list >> > > page of a model. One of these counts can be disabled, but the >> pagination >> > > count can not be. On huge tables, this can be a massive performance >> > > issue. https://code.djangoproject.com/ticket/8408 was created to >> disable >> > > the count, but it only allowed disabling one of them. I've reopened >> the >> > > ticket with Matthew who has implemented the suggestion to disable >> > > pagination features that require count. >> > > >> > > PR: https://github.com/django/django/pull/8858 >> > > >> > > Does anyone have strong views one way or another here? Would like to >> hear >> > > if so. >> > > >> > > -- >> > > 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-develop...@googlegroups.com <javascript:>. >> > > To post to this group, send email to django-d...@googlegroups.com >> <javascript:>. >> > > Visit this group at https://groups.google.com/group/django-developers >> . >> > > To view this discussion on the web visit >> https://groups.google.com/d/ms >> > > gid/django-developers/d093b4dd-4bc4-4c16-910d-53c4740ec5ea% >> > > 40googlegroups.com >> > > < >> https://groups.google.com/d/msgid/django-developers/d093b4dd-4bc4-4c16-9 >> > > 10d-53c4740ec5ea% >> 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 https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3b0d9c41-8f4d-4549-b3a2-4aa6860f00ee%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
