Re: Why count(*) doest use index?

On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard <glenn(at)zewt(dot)org> wrote:
> > That's often perfectly fine, with read-heavy, single-writer workloads.
> >
> > I definitely wish there was a way to create indexes to track counters on
> > various types of queries, even if it eliminates write concurrency on
> > affected writes. Doing it by hand is a pain.
>
> beyond what the stats system does you mean?
>

The stats system only helps for the most basic case--counting the number of
rows in a table. In my experience that's not very common; most of the time
it's counting total results from some more interesting query, eg. for
pagination. In my particular case, I'm caching results for SELECT COUNT(*),
expr2 FROM table WHERE expr GROUP BY expr2 (for a very limited set of
expressions).

If you aren't interested in high concurrency count it really isn't all
> that difficult -- just push table modifying queries into a procedure
> and grab rows affected. Row level trigger can also do it but
> performance will suck unless you are already doing all row by row
> processing (in which case your performance already sucks).
>

Row triggers are fast enough for my case--it's a read-heavy workload, so
it's okay to take a bit more time inserting new data. It's easier to ensure
consistency with row triggers, since they can be tested independently of
anything modifying the table.

--
Glenn Maynard