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: 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 14:23:30
Message-ID: 200304151423.h3FENUf06986@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-sql
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


In response to

Responses

pgsql-performance by date

Next:From: Will LaShellDate: 2003-04-15 15:40:05
Subject: Re: for help!
Previous:From: Shridhar DaithankarDate: 2003-04-15 09:54:11
Subject: Re: for help!

pgsql-sql by date

Next:From: Adam ShermanDate: 2003-04-15 14:53:52
Subject: Percentage of Total Occurances
Previous:From: pginfoDate: 2003-04-15 14:21:15
Subject: changing column size and type.

pgsql-general by date

Next:From: Johnson, ShaunnDate: 2003-04-15 14:41:44
Subject: import fixed width files into PostgreSQL
Previous:From: Robert TreatDate: 2003-04-15 14:19:11
Subject: Re: Are we losing momentum?

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