Re: Slow count(*) again...

bricklen wrote:
> On Sat, Oct 9, 2010 at 4:26 PM, Neil Whelchel <neil(dot)whelchel(at)gmail(dot)com> wrote:
> > Maybe an
> > estimate(*) that works like count but gives an answer from the index without
> > checking visibility? I am sure that this would be good enough to make a page
> > list, it is really no big deal if it errors on the positive side, maybe the
> > list of pages has an extra page off the end. I can live with that. What I
> > can't live with is taking 13 seconds to get a page of results from 850,000
> > rows in a table.
> > -Neil-
> >
>
> FWIW, Michael Fuhr wrote a small function to parse the EXPLAIN plan a
> few years ago and it works pretty well assuming your stats are up to
> date.
>
> http://markmail.org/message/gknqthlwry2eoqey

What I recommend is to execute the query with EXPLAIN, and look at the
estimated rows and costs. If the row number is large, just round it to
the nearest thousand and return it to the application as a count ---
this is what Google does for searches (just try it).

If the row count/cost are low, run the query and return an exact count.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +