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

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: gearond(at)cvc(dot)net, 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-05-31 02:31:38
Message-ID: 200305310231.h4V2VcV16487@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-sql
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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org)
> 

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

In response to

pgsql-performance by date

Next:From: Robert CreagerDate: 2003-05-31 03:21:01
Subject: Re: db growing out of proportion
Previous:From: Tomas SzepeDate: 2003-05-30 23:08:21
Subject: Re: db growing out of proportion

pgsql-sql by date

Next:From: Bruno Wolff IIIDate: 2003-05-31 13:08:31
Subject: Re: join/case
Previous:From: Stephan SzaboDate: 2003-05-31 00:04:11
Subject: Re: join/case

pgsql-general by date

Next:From: Bruce MomjianDate: 2003-05-31 02:32:18
Subject: Re: count syntax
Previous:From: Bruce MomjianDate: 2003-05-31 02:26:10
Subject: Re: [GENERAL] Are we losing momentum?

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