X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 02F0CD1B503 for ; Fri, 3 Oct 2003 05:13:56 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 61176-03 for ; Fri, 3 Oct 2003 02:13:10 -0300 (ADT) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 6CCD8D1B550 for ; Fri, 3 Oct 2003 02:13:08 -0300 (ADT) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 60CD436B2D; Fri, 3 Oct 2003 01:13:08 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1A5IFY-0000Ys-00; Fri, 03 Oct 2003 01:13:08 -0400 To: Christopher Browne Cc: pgsql-performance@postgresql.org Subject: Re: count(*) slow on large tables References: <20031002191547.GZ87525@rlx11.zapatec.com> <20031002193905.GD18417@wolff.to> <3F7C98B8.C892D0E5@nsd.ca> <60brszcng5.fsf@dev6.int.libertyrms.info> In-Reply-To: <60brszcng5.fsf@dev6.int.libertyrms.info> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 03 Oct 2003 01:13:08 -0400 Message-ID: <87brsyrjiz.fsf@stark.dyndns.tv> Lines: 43 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/75 X-Sequence-Number: 3823 Christopher Browne writes: > It would be very hairy to implement it correctly, and all this would > cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;" > > If you had a single WHERE clause attached, you would have to revert to > walking through the tuples looking for the ones that are live and > committed, which is true for any DBMS. Well it would be handy for a few other cases as well. 1 It would be useful for the case where you have a partial index with a matching where clause. The optimizer already considers using such indexes but it has to pay the cost of the tuple lookup, which is substantial. 2 It would be useful for the very common queries of the form WHERE x IN (select id from foo where some_indexed_expression) (Or the various equivalent forms including outer joins that test to see if the matching record was found and don't retrieve any other columns in the select list.) 3 It would be useful for many-many relationships where the intermediate table has only the two primary key columns being joined. If you create a multi-column index on the two columns it shouldn't need to look up the tuple. This would be effectively be nearly equivalent to an "index organized table". 4 It would be useful for just about all the referential integrity queries... I don't mean to say this is definitely a good thing. The tradeoff in complexity and time to maintain the index pages would be large. But don't dismiss it as purely a count(*) optimization hack. I know Oracle is capable of it and it can speed up your query a lot when you remove that last unnecessary column from a join table allowing oracle to skip the step of reading the table. -- greg