Re: Why count(*) doest use index?

> -----Original Message-----
> From: Glenn Maynard [mailto:glenn@zewt.org]
> Sent: Monday, March 07, 2011 5:27 PM
> To: pgsql-general@postgresql.org
> Subject: Re: Why count(*) doest use index?
>
>
> An index on "events(user, event_time::date)" could optimize
> this, eg. effectively maintaining a count of matching rows
> for each (user, day) tuple--which is ultimately what I'm
> doing manually with triggers.  Of course, it would have a
> significant cost, in some combination of complexity, index
> size and write concurrency, and couldn't be the default
> behavior for an index.
>
> --
> Glenn Maynard
>

Indexes don't "maintain counts", indexes maintain pointers to the table
records.

What you need is "materialized view" storing aggregates.
And it looks like you already have it with your triggers.

Regards,
Igor Neyman