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 46ADFD1B4E3; Sat, 4 Oct 2003 21:01:05 +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 85650-06; Sat, 4 Oct 2003 18:00:18 -0300 (ADT) Received: from fuji.krosing.net (217-159-136-226-dsl.kt.estpak.ee [217.159.136.226]) by svr1.postgresql.org (Postfix) with ESMTP id E0290D1B573; Sat, 4 Oct 2003 17:59:11 -0300 (ADT) Received: from fuji.krosing.net (localhost.localdomain [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id h94Kx7Cs004926; Sat, 4 Oct 2003 23:59:13 +0300 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id h94Kx03Q004924; Sat, 4 Oct 2003 23:59:00 +0300 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: COUNT(*) again (was Re: [HACKERS] Index/Function From: Hannu Krosing To: Tom Lane Cc: Christopher Browne , pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" In-Reply-To: <6102.1065283673@sss.pgh.pa.us> References: <20031002191547.GZ87525@rlx11.zapatec.com> <20031002193905.GD18417@wolff.to> <3F7C98B8.C892D0E5@nsd.ca> <60brszcng5.fsf@dev6.int.libertyrms.info> <1065258004.2746.30.camel@fuji.krosing.net> <6102.1065283673@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1065301140.2746.37.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Sat, 04 Oct 2003 23:59:00 +0300 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/146 X-Sequence-Number: 3894 Tom Lane kirjutas L, 04.10.2003 kell 19:07: > Hannu Krosing writes: > > Christopher Browne kirjutas R, 03.10.2003 kell 00:57: > >> A while back I outlined how this would have to be done, and for it to > >> be done efficiently, it would be anything BUT simple. > > > Could this be made a TODO item, perhaps with your attack plan. > > If I recall that discussion correctly, no one including Christopher > thought the attack plan was actually reasonable. > > What this keeps coming down to is that an optimization that helps only > COUNT(*)-of-one-table-with-no-WHERE-clause would be too expensive in > development and maintenance effort to justify its existence. Please read further in my email ;) The point I was trying to make was that faster count(*)'s is just a side effect. If we could (conditionally) keep visibility info in indexes, then this would also solve the problem fo much more tricky question of index-structured tables. Count(*) is *not* the only query that could benefit from not needing to go to actual data table for visibilty info, The much more needed case would be the "inveres time series" type of queries, which would otherways trash cache pages badly. ---------------------------- Hannu