X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A5DBF475425 for ; Sat, 1 Feb 2003 14:42:53 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 11F33474E53 for ; Sat, 1 Feb 2003 14:42:53 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2816540 for pgsql-performance@postgresql.org; Sat, 01 Feb 2003 11:42:47 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: not using index for select min(...) Date: Sat, 1 Feb 2003 11:41:42 -0800 User-Agent: KMail/1.4.3 References: <200301311531.12605.josh@agliodbs.com> <20030201040954.GK15936@perrin.int.nxad.com> In-Reply-To: <20030201040954.GK15936@perrin.int.nxad.com> MIME-Version: 1.0 Message-Id: <200302011141.42245.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/1 X-Sequence-Number: 1057 Sean, > I've spent some time in the past thinking about this, and here's the > best idea that I can come up with: > > Part one: setup an ALTER TABLE directive that allows for the > addition/removal of cached aggregates. Ex: Actually, Joe Conway and I may be working on something like this for a clie= nt.=20=20 Joe's idea is to use a hacked version of the statistics collector to cache= =20 selected aggregate values in memory. These aggregates would be=20 non-persistent, but the main concern for us is having aggregate values that= =20 are instantly accessable, and that don't increase the cost of INSERTS and= =20 UPDATES more than 10%. This is to satisfy the needs of a particular client, though, so it may neve= r=20 make it into the general PostgreSQL source. We'll post it somewhere if it= =20 works, though. We already implemented caching aggregates to tables, with is trivially easy= to=20 do with triggers. The problem with this approach is the=20 UPDATE/INSERT/DELETE overhead; even with an SPI-optimized C trigger, it's= =20 costing us up to 40% additional time when under heavy write activity ...=20 which is exactly when we can't afford delays. For a database which has a low level of UPDATE activity, though, you can=20 already implement cached aggregates as tables without inventing any new=20 Postgres extensions. --=20 Josh Berkus Aglio Database Solutions San Francisco