Re: Why count(*) doest use index?

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... 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