Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 32FF12E2E77
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  8 Mar 2008 01:31:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 65065-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  8 Mar 2008 01:31:47 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from ironport2-out.pppoe.ca (ironport2-out.pppoe.ca
	[206.248.154.182])
	by postgresql.org (Postfix) with ESMTP id 19CEE2E2E53
	for <pgsql-performance@postgresql.org>;
	Sat,  8 Mar 2008 01:31:47 -0400 (AST)
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: ApoHAHWz0UfO+Igp/2dsb2JhbACCOTWKVZ8GgQc
X-IronPort-AV: E=Sophos;i="4.25,465,1199682000"; d="scan'208,217";a="15732352"
Received: from smtp.pppoe.ca ([65.39.196.238])
	by ironport2-out.pppoe.ca with ESMTP; 08 Mar 2008 00:31:46 -0500
Received: from [206.248.136.41] ([206.248.136.41])
	by smtp.pppoe.ca (Internet Mail Server v1.0) with ESMTP id OIN28447;
	Sat, 08 Mar 2008 00:31:47 -0500
Message-ID: <47D224BE.1060001@mark.mielke.cc>
Date: Sat, 08 Mar 2008 00:31:42 -0500
From: Mark Mielke <mark@mark.mielke.cc>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: josh@agliodbs.com
CC: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>,
	Craig James <craig_james@emolecules.com>
Subject: Re: count * performance issue
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
	<47D00DB2.7040007@emolecules.com> <18451.1204824562@sss.pgh.pa.us>
	<200803071727.36146.josh@agliodbs.com>
In-Reply-To: <200803071727.36146.josh@agliodbs.com>
Content-Type: multipart/alternative;
	boundary="------------070104020801090007000301"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/177
X-Sequence-Number: 29131

This is a multi-part message in MIME format.
--------------070104020801090007000301
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Josh Berkus wrote:
>>> Count() on Oracle and MySQL is almost instantaneous, even for very
>>> large tables. So why can't Postgres do what they do?
>>>       
>> AFAIK the above claim is false for Oracle.  They have the same
>> transactional issues we do.
>>     
>
> Nope.  Oracle's MVCC is implemented through rollback segments, rather than 
> non-overwriting the way ours is.  So Oracle can just do a count(*) on the 
> index, then check the rollback segment for any concurrent 
> update/delete/insert activity and adjust the count.  This sucks if there's 
> a *lot* of concurrent activity, but in the usual case it's pretty fast

I read the "almost instantaneous" against "the above claim is false" and 
"Nope.", and I am not sure from the above whether you are saying that 
Oracle keeps an up-to-date count for the index (which might make it 
instantaneous?), or whether you are saying it still has to scan the 
index - which can take time if the index is large (therefore not 
instantaneous).

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


--------------070104020801090007000301
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Josh Berkus wrote:
<blockquote cite="mid:200803071727.36146.josh@agliodbs.com" type="cite">
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
      </pre>
    </blockquote>
    <pre wrap="">AFAIK the above claim is false for Oracle.  They have the same
transactional issues we do.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Nope.  Oracle's MVCC is implemented through rollback segments, rather than 
non-overwriting the way ours is.  So Oracle can just do a count(*) on the 
index, then check the rollback segment for any concurrent 
update/delete/insert activity and adjust the count.  This sucks if there's 
a *lot* of concurrent activity, but in the usual case it's pretty fast</pre>
</blockquote>
<br>
I read the "almost instantaneous" against "the above claim is false"
and "Nope.", and I am not sure from the above whether you are saying
that Oracle keeps an up-to-date count for the index (which might make
it instantaneous?), or whether you are saying it still has to scan the
index - which can take time if the index is large (therefore not
instantaneous).<br>
<br>
Cheers,<br>
mark<br>
<br>
<pre class="moz-signature" cols="72">-- 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc">&lt;mark@mielke.cc&gt;</a>
</pre>
</body>
</html>

--------------070104020801090007000301--