Here is a suggestion.
When a count(*) is computed (for all records) store that value and
unvalidate it if there is a later insert or delete on the table. Next
improvement would be to maintain a count per active transaction.
Bruce Momjian wrote:
>
> Charles H. Woloszynski wrote:
> >
> >
> > Richard Huxton wrote:
> >
> > >Depends on usage patterns and how you build your application. There are a
> > >couple of oddities with workarounds: count() and max() aren't very optimised
> > >for example.
> > >
> > You can 'fix' the max() SNAFU with a new query of the form
> > "select field from tbl limit 1 order by field desc" (not precise
> > syntax, but the idea is correct)
> >
> > I call it a SNAFU since it I hate to have to change queries from
> > something obvious to a more obscure format just to work around
> > an optimizer issue.
> >
> > Not sure if there is an equivalent query to make count() work
> > faster
>
> The problem with optimizing COUNT() is that different backends have
> different tuple views, meaning the count from one backend could be
> different than from another backend. I can't see how to optimize that.
> Does oracle do it? Maybe by looking their redo segements. We don't
> have those because redo is stored in the main table.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org