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 86C11D1B52E for ; Sun, 5 Oct 2003 18:59: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 89668-01 for ; Sun, 5 Oct 2003 15:58:52 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 18B17D1B531 for ; Sun, 5 Oct 2003 15:58:51 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3728969; Sun, 05 Oct 2003 11:59:26 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Bruce Momjian , Tom Lane Subject: Re: count(*) slow on large tables Date: Sun, 5 Oct 2003 11:57:21 -0700 User-Agent: KMail/1.4.3 Cc: Christopher Browne , pgsql-performance@postgresql.org References: <200310041819.h94IJkV07596@candle.pha.pa.us> In-Reply-To: <200310041819.h94IJkV07596@candle.pha.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200310051157.21555.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/160 X-Sequence-Number: 3908 Bruce, > OK, I beefed up the TODO: > > * Use a fixed row count and a +/- count with MVCC visibility rules > to allow fast COUNT(*) queries with no WHERE clause(?) > > I can always give the details if someone asks. It doesn't seem complex > enough for a separate TODO.detail item. Hmmm ... this doesn't seem effort-worthy to me. How often does anyone do COUNT with no where clause, except GUIs that give you a record count? (of course, as always, if someone wants to code it, feel free ...) And for those GUIs, wouldn't it be 97% as good to run an ANALYZE and give the approximate record counts for large tables? As for counts with a WHERE clause, this is obviously up to the user. Joe Conway and I tested using a C trigger to track some COUNT ... GROUP BY values for large tables based on additive numbers. It worked fairly well for accuracy, but the performance penalty on data writes was significant ... 8% to 25% penalty for UPDATES, depending on the frequency and batch size (> frequency > batch size --> > penalty) It's possible that this could be improved through some mechanism more tightly integrated with the source code. However,the coding effort would be significant ( 12-20 hours ) and it's possible that there would be no improvement, which is why we didn't do it. We also discussed an asynchronous aggregates collector that would work something like the statistics collector, and keep pre-programmmed aggregate data, updating during "low-activity" periods. This would significantly reduce the performance penalty, but at the cost of accuracy ... that is, a 1%-5% variance on high-activity tables would be unavoidable, and all cached aggregates would have to be recalculated on database restart, significantly slowing down startup. Again, we felt that the effort-result payoff was not worthwhile. Overall, I think the stuff we already have planned ... the hash aggregates in 7.4 and Tom's suggestion of adding an indexable flag to pg_aggs ... are far more likely to yeild useful fruit than any caching plan. -- Josh Berkus Aglio Database Solutions San Francisco