-----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.
<<-----------------------------------------------------------------

Fri, 5 Apr 2002 12:08:11 -0800
"Dann Corbit" <DCorbit@connx.com>
Re: Suggestion for optimization
"Dann Corbit" <DCorbit@connx.com> writes:
> At any rate, there is clearly a concept of cardinality in any case.

Certainly.  The count(*) value is perfectly well defined within any one
transaction.  We *could*, if we wanted to, implement bookkeeping logic
that would keep track of the number of rows inserted by all transactions
and allow derivation of the count-as-seen-by-any-one-transaction at all
times.  The point is that that logic would be vastly more complex than
you thought it would be; and it would not be optional.  (AFAICS, the
counts would have to be determined at postmaster startup and then
maintained faithfully by all transactions.  There wouldn't be any good
way for a transaction to initialize the bookkeeping logic on-the-fly ---
unless you call acquiring an exclusive lock on a table good.)  No one
who's looked at it has thought that it would be a good tradeoff for
making count(*) faster.

			regards, tom lane

Fri, 05 Apr 2002 15:21:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Suggestion for optimization 
Not to mention it only increases the speed of:

SELECT count(*) FROM foo;

and not:

SELECT count(*) FROM foo WHERE bar;
--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Dann Corbit" <DCorbit@connx.com>
Cc: "Doug McNaught" <doug@wireboard.com>;
<pgsql-hackers@postgresql.org>
Sent: Friday, April 05, 2002 3:21 PM
Subject: Re: [HACKERS] Suggestion for optimization


> "Dann Corbit" <DCorbit@connx.com> writes:
> > At any rate, there is clearly a concept of cardinality in any
case.
>
> Certainly.  The count(*) value is perfectly well defined within any
one
> transaction.  We *could*, if we wanted to, implement bookkeeping
logic
> that would keep track of the number of rows inserted by all
transactions
> and allow derivation of the count-as-seen-by-any-one-transaction at
all
> times.  The point is that that logic would be vastly more complex
than
> you thought it would be; and it would not be optional.  (AFAICS, the
> counts would have to be determined at postmaster startup and then
> maintained faithfully by all transactions.  There wouldn't be any
good
> way for a transaction to initialize the bookkeeping logic
on-the-fly ---
> unless you call acquiring an exclusive lock on a table good.)  No
one
> who's looked at it has thought that it would be a good tradeoff for
> making count(*) faster.
>
> regards, tom lane
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Fri, 5 Apr 2002 15:35:19 -0500
"Rod Taylor" <rbt@zort.ca>
Re: Suggestion for optimization 
Dann Corbit wrote:
> 
> 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.

Oracle is also a MVCC database. So this notion that MVCC somehow makes
it inappropriate for data warehousing would imply that Oracle is also
inappropriate. However, in your defense, Oracle did apparently find
enough customer demand for a MVCC-compatible hack of COUNT() to
implement a short-cut route to calculate its value...

Mike Mascari
mascarm@mascari.com

Fri, 05 Apr 2002 15:44:03 -0500
Mike Mascari <mascarm@mascari.com>
Re: Suggestion for optimization
At 12:08 PM -0800 4/5/02, Dann Corbit wrote:
>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.

That is exactly the point of MVCC. When you start your reporting cycle, you initiate a transaction. That transaction causes the database to _act_ as if you had "pump[ed] the data into a new copy of the database that does not allow writes at all."

Your transaction is isolated from ongoing activities in the database. Your transaction _is_ a snapshot of the database at some instant in time.

This is a good thing. You should probably ponder it for a while before claiming it hasn't been thought out well wrt. certain applications.


Still, your suggestion _could_ be implemented. Your comment: "An accurate
cardinality figure can greatly enhance the optimizer's ability to
perform joins in the correct order" was intriguing, and I'd be interested in Tom's thoughts on just that bit.

-pmb



Fri, 5 Apr 2002 13:01:38 -0800
Peter Bierman <bierman@apple.com>
Re: Suggestion for optimization
Peter Bierman <bierman@apple.com> writes:
> ... Your comment: "An
> accurate cardinality figure can greatly enhance the optimizer's
> ability to perform joins in the correct order" was intriguing, and I'd
> be interested in Tom's thoughts on just that bit.

Approximate figures are quite sufficient for the planner's purposes.
AFAICS, making them exact would not improve the planning estimates
at all, because there are too many other sources of error.  We have
approximate stats already via vacuum/analyze statistics gathering.

			regards, tom lane

Fri, 05 Apr 2002 18:41:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Suggestion for optimization 
Af far as I know Oracle doesn't have any short cut (along the lines of 
what is being discussed in this thread) for this operation.  However 
Oracle is more efficient in providing the answer than postgres currently 
is.  While postgres needs to perform a full scan on the table, Oracle 
will only need to perform a full index scan on the primary key if one 
exists.  Since the index will likely have much less data than the full 
table this will result in fewer IOs and be faster than what postgres 
does, but it still takes a while for large tables even in Oracle.

thanks,
--Barry

Mike Mascari wrote:
> Dann Corbit wrote:
> 
>>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.
> 
> 
> Oracle is also a MVCC database. So this notion that MVCC somehow makes
> it inappropriate for data warehousing would imply that Oracle is also
> inappropriate. However, in your defense, Oracle did apparently find
> enough customer demand for a MVCC-compatible hack of COUNT() to
> implement a short-cut route to calculate its value...
> 
> Mike Mascari
> mascarm@mascari.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 



Fri, 05 Apr 2002 19:28:27 -0800
Barry Lind <barry@xythos.com>
Re: Suggestion for optimization
On Fri, 5 Apr 2002, Barry Lind wrote:

> Af far as I know Oracle doesn't have any short cut (along the lines of 
> what is being discussed in this thread) for this operation.  However 
> Oracle is more efficient in providing the answer than postgres currently 
> is.  While postgres needs to perform a full scan on the table, Oracle 
> will only need to perform a full index scan on the primary key if one 
> exists.  Since the index will likely have much less data than the full 

Under Postgres, a full index scan is generally more expensive than a full
table scan since indices, particularly btree, carry a large amount of meta
data and theefore consume more pages.

Gavin


Sun, 7 Apr 2002 22:27:08 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Suggestion for optimization
> > Af far as I know Oracle doesn't have any short cut (along the lines of
> > what is being discussed in this thread) for this operation.  However
> > Oracle is more efficient in providing the answer than postgres
> currently
> > is.  While postgres needs to perform a full scan on the table, Oracle
> > will only need to perform a full index scan on the primary key if one
> > exists.  Since the index will likely have much less data than the full
>
> Under Postgres, a full index scan is generally more expensive than a full
> table scan since indices, particularly btree, carry a large amount of meta
> data and theefore consume more pages.

Don't forget that Postgres also doesn't store tids in the index, so must
always check with the main table that a row is visible in current
transaction.

Chris


Mon, 8 Apr 2002 10:05:08 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Suggestion for optimization
Christopher Kings-Lynne wrote:
> > > Af far as I know Oracle doesn't have any short cut (along the lines of
> > > what is being discussed in this thread) for this operation.  However
> > > Oracle is more efficient in providing the answer than postgres
> > currently
> > > is.  While postgres needs to perform a full scan on the table, Oracle
> > > will only need to perform a full index scan on the primary key if one
> > > exists.  Since the index will likely have much less data than the full
> >
> > Under Postgres, a full index scan is generally more expensive than a full
> > table scan since indices, particularly btree, carry a large amount of meta
> > data and theefore consume more pages.
> 
> Don't forget that Postgres also doesn't store tids in the index, so must

I assume you mean xid here.  tids are in the index or there would be no
way to find the heap row. :-)

> always check with the main table that a row is visible in current
> transaction.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 7 Apr 2002 23:23:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Suggestion for optimization