Scott,
unless id is indexed there is nothing that can be done with
select count(*) from table where id >10000;
Otherwise, the index should be scanned, not the table.
And, scanning a large table to get count(*) will always be worst than
maintaining your own count.
JLL
"scott.marlowe" wrote:
>[...]
>
> select count(*) from table where id >10000;
>[...]
> But the performance of updating that secondary table may be worse than
> just running a count(*).
>
> I doubt the black (gray??? :-) magic needed to do this will be put into
> the backend of postgresql any time soon. But the userland solution is
> something that could be quite useful.
>
> On Fri, 8 Nov 2002, Jean-Luc Lachance wrote:
>
> > Scott,
> >
> > You answered the question yourself. The operative keyword her is
> > *before* the transaction started.
> > You store the global count before the transaction. While in a
> > transaction, you save the number of inserted and deleted records. When
> > *all* parallel transactions are commited, you update the global count
> > with the total of of updated and deleted records. If a connection start
> > a new transaction before the other transactions have been
> > commited you take the global count plus the adjustment from the previous
> > transaction.
> >
> > JLL
> >
> > "scott.marlowe" wrote:
> > >
> > > but how do you handle the case where two people have two different
> > > connections, and one starts a serializable transaction and adds n rows to
> > > the table. For that transaction, there are x+n rows in the table, while
> > > for the transaction started before his, there are only x rows. which is
> > > the "right" answer?
> > >
> > > On Fri, 8 Nov 2002, Jean-Luc Lachance wrote:
> > >
> > > > 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.