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-13 05:25:09
Message-ID: 200501130525.j0D5P9u02522@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-patches
Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> Even if you could track the tuple's committed-good status reliably,
> >> that isn't enough under MVCC.
> 
> > I mentioned that:
> 
> >> (Oh, and you could only update the bit when all active transactions
> >> are newer than the creation transaction so we know they should all see
> >> it as visible.)
> 
> 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.  Maybe that
> would work.  You'd still have to visit the heap when a tuple is in the
> "uncertain" states, but with luck that'd be only a small fraction of the
> time.

Yes, it is sort of the reverse, but how do you get around the delete
case?  Even if the bit is set, how do you know it wasn't deleted since
you set the bit?  Seems you always have to still check the heap, no?

> I'm still concerned about the update costs of maintaining these bits,
> but this would at least escape the index-bloat objection.  I think we
> still have one free bit in index tuple headers...

You mean you are considering clearing the index bit when you delete the
row?

-- 
  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: Tom LaneDate: 2005-01-13 05:39:56
Subject: Re: Much Ado About COUNT(*)
Previous:From: Tom LaneDate: 2005-01-13 05:06:38
Subject: Re: Much Ado About COUNT(*)

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-01-13 05:39:56
Subject: Re: Much Ado About COUNT(*)
Previous:From: Tom LaneDate: 2005-01-13 05:06:38
Subject: Re: Much Ado About COUNT(*)

pgsql-patches by date

Next:From: David FetterDate: 2005-01-13 05:33:30
Subject: Returning multiple cursors from PL/PgSQL
Previous:From: Tom LaneDate: 2005-01-13 05:06:38
Subject: Re: Much Ado About COUNT(*)

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