Re: Slow COUNT

On Sun, Dec 04, 2005 at 18:28:53 +0100,
Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> Am Sonntag, den 04.12.2005, 09:56 -0600 schrieb Bruno Wolff III:
> > On Sun, Dec 04, 2005 at 14:40:49 +0100,
> > Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> > >
> > > Doing something to enable aggregates in general to use
> > > an existent index would be a nice ide imho.
> > > (With all the visibility hinting in place)
> >
> > Assuming you are refering to max and min, this has already been done and is
> > in 8.1.
>
> I also mean sum, avg, ... and last not least count :-)

Your comment about indexes threw me there. Indexes are not the problem. If you
use a WHERE clause with enough selectivity and the is an appropiate index, an
an index scan will be used. There is a related issue that when postgres does
an index scan, it also needs to visit the hep to check visibility. The issue
there is that maintaining visibility in the index has costs that are currently
believed to outweigh the benefits of not having to check visibility in the
heap. (Though recently there have been some new suggestions in this area.)

What you are looking for seems to be caching values for the case where the
full table is selected. That has problems as described in the other response
and in more details in the archives. This isn't something you want turned on
by default, but it would be nice if there was something packaged to make doing
this easier for people who want it for selected tables.