Re: Suggestion for optimization

-----Original Message-----
From: Doug McNaught [mailto:doug@wireboard.com]
Sent: Friday, April 05, 2002 11:55 AM
To: Dann Corbit
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] 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.
>>-----------------------------------------------------------------
I guess that this model can be viewed as "everything is a snapshot".
It seems plain that the repercussions for a data warehouse and for
reporting have not been thought out very well.  This is definitely
very, very bad in that arena.  I suppose that reporting could still
be accomplished, but it would require pumping the data into a new
copy of the database that does not allow writes at all.  Yuck.

At any rate, there is clearly a concept of cardinality in any case.
Perhaps the information would have to be kept as part of the
connection.  If (after all) you cannot even compute cardinality
for a single connection then the database truly is useless.  In
fact, under a scenario where cardinality has no meaning, neither does
select count() since that is what it measures.  Might as well
remove it from the language.

I have read a couple books on Postgresql and somehow missed the
whole MVCC idea.  Maybe after I understand it better the clammy
beads of sweat on my forehead will dry up a little.
<<-----------------------------------------------------------------