X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DE16B9FA5BE for ; Thu, 15 Jun 2006 15:46:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 37756-01 for ; Thu, 15 Jun 2006 15:46:12 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id A17529FA13E for ; Thu, 15 Jun 2006 15:46:12 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so353781ugf for ; Thu, 15 Jun 2006 11:46:11 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=sCT0xr1jN9L/ji3L1fO6AcnDGieR3zvJwWjO/z9659UlwPMjebBiiyj84MbqtFi6hV0+efKEI3FtLMr/fpLvT8yud2XMKjS7IWeMNMrqqIk4VxenCWn+oMtzoI2E+x5Q0I/Qyqp0C2hxezILdgVIOWwx1ESvgtydQ5Lf/Mc9OMU= Received: by 10.66.250.17 with SMTP id x17mr1998377ugh; Thu, 15 Jun 2006 11:46:11 -0700 (PDT) Received: by 10.66.216.11 with HTTP; Thu, 15 Jun 2006 11:46:11 -0700 (PDT) Message-ID: Date: Thu, 15 Jun 2006 14:46:11 -0400 From: "John Vincent" Reply-To: pgsql-performance@lusis.org To: "Mark Lewis" Subject: Re: Optimizer internals Cc: "PGSQL Performance" In-Reply-To: <1150396425.31200.66.camel@archimedes> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9353_24436772.1150397171195" References: <1150396425.31200.66.camel@archimedes> X-Google-Sender-Auth: e08d39f0933ec9f4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/322 X-Sequence-Number: 19679 ------=_Part_9353_24436772.1150397171195 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/15/06, Mark Lewis wrote: > DB2 can satisfy the query using only indexes because DB2 doesn't do > MVCC. > > Although MVCC is generally a win in terms of making the database easier > to use and applications less brittle, it also means that the database > must inspect the visibility information for each row before it can > answer a query. For most types of queries this isn't a big deal, but > for count(*) type queries, it slows things down. Mark, Thanks for the answer. My DBAs just got this look on thier face when I showed. It's not like the couldn't have investigated this information themselves but I think the light finally came on. One question that we came up with is how does this affect other aggregate functions like MAX,MIN,SUM and whatnot? Being that this is our data warehouse, we use these all the time. As I've said previously, I didn't know a human could generate some of the queries we've passed through this system. Since adding the visibility information to indexes would make them > significantly more expensive to use and maintain, it isn't done. > Therefore, each row has to be fetched from the main table anyway. > > Since in this particular query you are counting all rows of the > database, PG must fetch each row from the main table regardless, so the > sequential scan is much faster because it avoids traversing the index > and performing random read operations. > > -- Mark Lewis > ------=_Part_9353_24436772.1150397171195 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/15/06, Mark Lewis <mark.lewis@mir3.com> wrote:

DB2 can satisfy the query using only indexes because DB2 doesn't do
MVCC.

Although MVCC is generally a win in terms of making the database easier
to use and applications less brittle, it also means that the database
must inspect the visibility information for each row before it can
answer a query.  For most types of queries this isn't a big deal, but
for count(*) type queries, it slows things down.


Mark,

Thanks for the answer. My DBAs just got this look on thier face when I showed. It's not like the couldn't have investigated this information themselves but I think the light finally came on.

One question that we came up with is how does this affect other aggregate functions like MAX,MIN,SUM and whatnot? Being that this is our data warehouse, we use these all the time. As I've said previously, I didn't know a human could generate some of the queries we've passed through this system.


Since adding the visibility information to indexes would make them
significantly more expensive to use and maintain, it isn't done.
Therefore, each row has to be fetched from the main table anyway.

Since in this particular query you are counting all rows of the
database, PG must fetch each row from the main table regardless, so the
sequential scan is much faster because it avoids traversing the index
and performing random read operations.

-- Mark Lewis

------=_Part_9353_24436772.1150397171195--