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

Re: Much Ado About COUNT(*)

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-22 20:06:33
Message-ID: 200501222006.j0MK6XD14490@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-patches
Added to TODO based on this discusion:

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

* Speed up COUNT(*)

  We could use a fixed row count and a +/- count to follow MVCC
  visibility rules, or a single cached value could be used and
  invalidated if anyone modifies the table.  Another idea is to  <--
  get a count directly from a unique index, but for this to be
  faster than a sequential scan it must avoid access to the heap
  to obtain tuple visibility information.

* Allow data to be pulled directly from indexes

  Currently indexes do not have enough tuple tuple visibility
  information to allow data to be pulled from the index without
  also accessing the heap.  One way to allow this is to set a bit
  to index tuples to indicate if a tuple is currently visible to
  all transactions when the first valid heap lookup happens.  This
  bit would have to be cleared when a heap tuple is expired.



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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> Ah, right, I missed the connection.  Hmm ... that's sort of the inverse
> >> of the "killed tuple" optimization we put in a release or two back,
> >> where an index tuple is marked as definitely dead once it's committed
> >> dead and the deletion is older than all active transactions.
> 
> > Yes, it is sort of the reverse, but how do you get around the delete
> > case?
> 
> A would-be deleter of a tuple would have to go and clear the "known
> good" bits on all the tuple's index entries before it could commit.
> This would bring the tuple back into the "uncertain status" condition
> where backends would have to visit the heap to find out what's up.
> Eventually the state would become certain again (either dead to
> everyone or live to everyone) and one or the other hint bit could be
> set again.
> 
> The ugly part of this is that clearing the bit is not like setting a
> hint bit, ie it's not okay if we lose that change.  Therefore, each
> bit-clearing would have to be WAL-logged.  This is a big part of my
> concern about the cost.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 

-- 
  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-announce by date

Next:From: Bruce MomjianDate: 2005-01-22 21:19:16
Subject: Re: Much Ado About COUNT(*)
Previous:From: Josh BerkusDate: 2005-01-21 04:20:34
Subject: It's OSCON Submission Time, please contact the PostgreSQL committee!

pgsql-hackers by date

Next:From: Nicolai TufarDate: 2005-01-22 20:19:20
Subject: Re: [HACKERS] %2$, %1$ gettext placeholder replacement is not working under Win32
Previous:From: Stephen FrostDate: 2005-01-22 19:49:06
Subject: Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED

pgsql-patches by date

Next:From: Euler Taveira de OliveiraDate: 2005-01-22 20:36:27
Subject: Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED
Previous:From: Stephen FrostDate: 2005-01-22 19:49:06
Subject: Re: [PATCHES] Merge pg_shadow && pg_group -- UNTESTED

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