Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Bruce Momjian <pgman@candle.pha.pa.us>, gearond@cvc.net
Subject: Re: [PERFORM] Yet Another (Simple) Case of Index not used
Date: Tue, 15 Apr 2003 17:29:45 +0100
User-Agent: KMail/1.4.3
Cc: Dann Corbit <DCorbit@connx.com>, Denis <denis@next2me.com>,
	pgsql-performance@postgresql.org, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org
References: <200304151423.h3FENUf06986@candle.pha.pa.us>
In-Reply-To: <200304151423.h3FENUf06986@candle.pha.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304151729.45657.dev@archonet.com>

On Tuesday 15 Apr 2003 3:23 pm, Bruce Momjian wrote:
> Dennis Gearon wrote:
> > from mysql manual:
> > -------------------------------------------------------------
> > "COUNT(*) is optimized to return very quickly if the SELECT retrieves
> > from one table, no other columns are retrieved, and there is no WHERE
> > clause. For example:
> >
> > mysql> select COUNT(*) from student;"
> > -------------------------------------------------------------

> The cleanest way would probably be to create an aggregate cache system
> table, and to insert into it when someone does an unqualified aggregate,
> and to delete from it when someone modifies the table --- the MVCC tuple
> visibility rules are handled automatically.  Queries can look in there
> to see if a visible cached value already exists. Of course, the big
> question is whether this would be a big win, and whether the cost of
> upkeep would justify it.

If the rule system could handle something like:

CREATE RULE quick_foo_count AS ON SELECT count(*) FROM foo=20
DO INSTEAD
SELECT quick_count FROM agg_cache WHERE tbl_name=3D'foo';

The whole thing could be handled by user-space triggers/rules and still=20
invisible to the end-user.

--=20
  Richard Huxton