Re: Why count(*) doest use index?

2011/3/8 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Mon, Mar 7, 2011 at 3:16 PM, Glenn Maynard <glenn(at)zewt(dot)org> wrote:
> > 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).
>
> SELECT COUNT(*) FROM table WHERE expr;
>
> will use index (assuming expr is optimizable and is worth while to
> optimize). Your case might be interesting for cache purposes if expr2
> is expensive, but has nothing to do with postgres index usage via
> count(*). mysql/myisam needs to scan as well in this case -- it
> can't magically 'look up' the value as it can for the in filtered
> (very special) case...

Exactly!

> it only differs from pg in that it can skip
> heap visibility check because all records are known good (and pg is
> moving towards optimizing this case in mostly read only workloads!)
>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.