DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=haUREcqJsoen5eVnP+mdNm/qf31JeyCV6LVeIdqCh0KqEW/aDid4vb5iDsJA/V0gzRUM7H0rLzfsfVCIRTbjyY5CeDg78k1XaKU93QEPVoyrXbS5tflYByiwQQUzXPOXS95MHeCdoTVHjqHisK+wneFjPpDgSr+Ye78YSqdMyeY=
Message-ID: <47D23C17.7010207@gmail.com>
Date: Fri, 07 Mar 2008 23:11:19 -0800
From: paul rivers <rivers.paul@gmail.com>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: Mark Mielke <mark@mark.mielke.cc>
CC: josh@agliodbs.com, 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>
	<47D224BE.1060001@mark.mielke.cc>
In-Reply-To: <47D224BE.1060001@mark.mielke.cc>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Mark Mielke wrote:
> 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>
> 

Oracle scans the index pages, if the b-tree index is on non-nullable 
columns, or if the bitmap index is on low-ish cardinality data. 
Otherwise, it table scans.  MyISAM in MySQL would be an example where a 
counter is kept.