-----Original Message-----
From: Doug McNaught [mailto:doug@wireboard.com]
Sent: Friday, April 05, 2002 11:30 AM
To: Dann Corbit
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Suggestion for optimization


"Dann Corbit" <DCorbit@connx.com> writes:

> It would be nice if total table cardinality could be maintained live.
> So (after the initial vacuum) we update the cardinality for each table
> in the system table (or perhaps add an entry to the table itself).
> There are two reasons why this is an important optimization.  Firstly,
> it is a psychological benefit for both benchmarks and customers when
> doing a select count(*) from <tablename>.  This is something that pops
> up all the time in benchmarks and customers do it too, in order to get
a
> feel for speed.  By storing the current number and incrementing for
> every insert and decrementing for every delete, the count(*) case with
> no where clause can return the value instantly.

How would this work with MVCC?
>>
Whenever a commit occurs, the pending inserts are totaled into the sum
and the pending deletes are subtracted.  It can be a list in memory or
whatever.  Maybe you are referring to the old (expired) rows begin
stored until vacuum?  Perhaps I really don't understand your question or
the issues involved.  Why does MVCC complicate issues?
<<

Fri, 5 Apr 2002 11:43:30 -0800
"Dann Corbit" <DCorbit@connx.com>
Re: Suggestion for optimization
"Dann Corbit" <DCorbit@connx.com> writes:

> How would this work with MVCC?
> >>
> Whenever a commit occurs, the pending inserts are totaled into the sum
> and the pending deletes are subtracted.  It can be a list in memory or
> whatever.  Maybe you are referring to the old (expired) rows begin
> stored until vacuum?  Perhaps I really don't understand your question or
> the issues involved.  Why does MVCC complicate issues?
> <<

Because the row count depends on what transactions have committed when
yours starts.  Also, you will see the count(*) reflecting INSERTs in
your transaction, but others won't until you commit.  So there is no
well-defined concept of cardinality under MVCC--it depends on which
rows are visible to which transactions.

-Doug
-- 
Doug McNaught       Wireboard Industries      http://www.wireboard.com/

      Custom software development, systems and network consulting.
      Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...

05 Apr 2002 14:55:07 -0500
Doug McNaught <doug@wireboard.com>
Re: Suggestion for optimization