Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Yet Another (Simple) Case of Index not used

From: Richard Huxton <dev(at)archonet(dot)com>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, gearond(at)cvc(dot)net
Cc: Dann Corbit <DCorbit(at)connx(dot)com>, Denis <denis(at)next2me(dot)com>,pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org,pgsql-sql(at)postgresql(dot)org
Subject: Re: [PERFORM] Yet Another (Simple) Case of Index not used
Date: 2003-04-15 16:29:45
Message-ID: 200304151729.45657.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-sql
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 
DO INSTEAD
SELECT quick_count FROM agg_cache WHERE tbl_name='foo';

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

-- 
  Richard Huxton


In response to

pgsql-performance by date

Next:From: scott.marloweDate: 2003-04-15 16:34:21
Subject: Re: for help!
Previous:From: Robert John ShepherdDate: 2003-04-15 15:49:18
Subject: Do Views offer any performance advantage?

pgsql-sql by date

Next:From: Pedro Igor Craveiro e SilvaDate: 2003-04-15 17:00:52
Subject: accent problems
Previous:From: rute solipaDate: 2003-04-15 15:52:07
Subject: help need it

pgsql-general by date

Next:From: scott.marloweDate: 2003-04-15 16:31:01
Subject: Re: How to change data type in column ?
Previous:From: scott.marloweDate: 2003-04-15 16:18:28
Subject: Re: Are we losing momentum?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group