Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6566E2E0071 for ; Mon, 10 Mar 2008 12:16:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 06886-07 for ; Mon, 10 Mar 2008 12:16:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (unknown [62.232.55.118]) by postgresql.org (Postfix) with ESMTP id AD7D92E019B for ; Mon, 10 Mar 2008 12:16:20 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.69) (envelope-from ) id 1JYjjV-0001oJ-0x; Mon, 10 Mar 2008 15:16:09 +0000 To: "Tom Lane" Cc: , , "Craig James" Subject: Re: count * performance issue In-Reply-To: <13345.1204940295@sss.pgh.pa.us> (Tom Lane's message of "Fri\, 07 Mar 2008 20\:38\:15 -0500") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 4640) References: <47D00DB2.7040007@emolecules.com> <18451.1204824562@sss.pgh.pa.us> <200803071727.36146.josh@agliodbs.com> <13345.1204940295@sss.pgh.pa.us> From: Gregory Stark Organization: EnterpriseDB Date: Mon, 10 Mar 2008 15:16:08 +0000 Message-ID: <87r6eia9tz.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/202 X-Sequence-Number: 29156 "Tom Lane" writes: > Well, scanning an index to get a count might be significantly faster > than scanning the main table, but it's hardly "instantaneous". It's > still going to take time proportional to the table size. Hm, Mark's comment about bitmap indexes makes that not entirely true. A bitmap index can do RLE compression which makes the relationship between the size of the table and the time taken to scan the index more complex. In the degenerate case where there are no concurrent updates (assuming you can determine that quickly) it might actually be constant time. > Unless they keep a central counter of the number of index entries; > which would have all the same serialization penalties we've talked > about before... Bitmap indexes do in fact have concurrency issues -- arguably they're just a baroque version of this central counter in this case. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!