Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 0D3922E2F88 for ; Thu, 20 Dec 2007 18:16:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 31060-01-9 for ; Thu, 20 Dec 2007 18:15:55 -0400 (AST) Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.232]) by postgresql.org (Postfix) with ESMTP id 9A6A32E36E6 for ; Thu, 20 Dec 2007 18:15:38 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id l8so42245nzf.28 for ; Thu, 20 Dec 2007 14:15:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=45MSGN01/rwpdw4mgC7Vu7aHui4EVwZVBp0ax/xhmhk=; b=CX3i7dHRebgAl2vmMUhDxUoT7AQYzeRTqJqbRm+JhPtBWP/wJAJRx0Lb1IYGtntYeY4dY3RnAxoL+SifFfB9J47xu6s7N6Czvd8NASfGgelqL4N52pg+Yj47m9sJL/8ugLee2zvCpnzr6kRfmSQ0TYfgukUDAaX1fnHnj1P+pwE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=tgQiUWYdNgRqXmBomocabzvTN6aK5L+Qr8eUEB6+tKuywyIxm8QfwqampwuzGb+aCZ7hSDI7V6d/Ty+KM+ReeLh8pdW4pxODBcsHtbJ+7XHG6ABPVcwuSuTPYisV5v+T0InrZGwrEJYDtua8Cgvx/fuU+K04U6b+WWTwis3iF4A= Received: by 10.142.231.7 with SMTP id d7mr431633wfh.30.1198188936928; Thu, 20 Dec 2007 14:15:36 -0800 (PST) Received: by 10.142.143.6 with HTTP; Thu, 20 Dec 2007 14:15:36 -0800 (PST) Message-ID: Date: Thu, 20 Dec 2007 16:15:36 -0600 From: "Scott Marlowe" To: "S Golly" Subject: Re: performance index scan vs bitmap-seq scan. Cc: pgsql-performance@postgresql.org In-Reply-To: <4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/330 X-Sequence-Number: 28146 On Dec 20, 2007 4:06 PM, S Golly wrote: > The server is running 8.2.5 FreeBSD 6.1 with 3 GB of RAM. > I have a table with over 100M rows. I have a unique index (primary key) on > column name called aid. > The select count(aid) .. does a Bitmap heap scan when the right side > condition is above 100,000,000 (if i take one zero off it does a pure index > scan). > My question : why is the optimizer choosing an Bitmap Heap Scan when count > can be done with index. Because count can't be done with the index alone. In pgsql the visibility info is in the table itself, so even if it can hit the index, it has to go back and hit the table to be sure if the tuple is visible.