Re: Suggestion for optimization

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

"Dann Corbit" <DCorbit(at)connx(dot)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.
<<-----------------------------------------------------------------