From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200305310231.h4V2VcV16487@candle.pha.pa.us>
Subject: Re: [PERFORM] Yet Another (Simple) Case of Index not used
In-Reply-To: <200304151423.h3FENUf06986@candle.pha.pa.us>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Date: Fri, 30 May 2003 22:31:38 -0400 (EDT)
Cc: gearond@cvc.net, Dann Corbit <DCorbit@connx.com>,
	Denis <denis@next2me.com>, pgsql-performance@postgresql.org,
	pgsql-general@postgresql.org, pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


Added to TODO:

	* Consider using MVCC to cache count(*) queries with no WHERE
	  clause

---------------------------------------------------------------------------

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;"
> > -------------------------------------------------------------
> > 
> > A nice little optimization, maybe not possible in a MVCC system.
> 
> I think the only thing you can do with MVCC is to cache the value and
> tranaction id for "SELECT AGG(*) FROM tab" and make the cached value
> visible to transaction id's greater than the one that executed the
> query, and invalidate the cache every time the table is modified.
> 
> In fact, don't clear the cache, just record the transaction id of the
> table modification command so we can use standard visibility routines to
> make the cache usable as long as possiible.
> 
> 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.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073