[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] [PERFORM] [GENERAL] Yet Another (Simple) Case of Index not used
From:       Bruce Momjian <pgman () candle ! pha ! pa ! us>
Date:       2003-05-31 2:31:38
[Download RAW message or body]


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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
[prev in list] [next in list] [prev in thread] [next in thread]