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 740E0D1B4EC for ; Sat, 4 Oct 2003 16:08:41 +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 25763-08 for ; Sat, 4 Oct 2003 13:07:55 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 5A888D1B54B for ; Sat, 4 Oct 2003 13:07:54 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h94G7shn006103; Sat, 4 Oct 2003 12:07:54 -0400 (EDT) To: Hannu Krosing Cc: Christopher Browne , pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" Subject: COUNT(*) again (was Re: [HACKERS] Index/Function organized table layout) In-reply-to: <1065258004.2746.30.camel@fuji.krosing.net> 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> Comments: In-reply-to Hannu Krosing message dated "Sat, 04 Oct 2003 12:00:04 +0300" Date: Sat, 04 Oct 2003 12:07:53 -0400 Message-ID: <6102.1065283673@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/140 X-Sequence-Number: 3888 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. At least if you insist on an exact, MVCC-correct answer. So far as I've seen, the actual use cases for unqualified COUNT(*) could be handled equally well by an approximate answer. What we should be doing rather than wasting large amounts of time trying to devise exact solutions is telling people to look at pg_class.reltuples for approximate answers. We could also be looking at beefing up support for that approach --- maybe provide some syntactic sugar for the lookup, maybe see if we can update reltuples in more places than we do now, make sure that the autovacuum daemon includes "keep reltuples accurate" as one of its design goals, etc etc. regards, tom lane