On Friday 08 Nov 2002 5:21 pm, Bruce Momjian wrote:
> Charles H. Woloszynski wrote:
> > 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.
The only way I could model it when I thought about it some time ago was as
though you had a separate table "pg_table_counts" with columns (tableoid,
count) - every insert/delete would also update this table. Then the standard
transaction-id semantics would work re: visibility of the "current" value.
Of course, this only helps in the scenario of count(*) for a real table and
nothing more complicated (count distinct, views etc). I can also imagine a
fair performance hit unless you optimised quite heavily.
--
Richard Huxton