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

Re: Much Ado About COUNT(*)

From: Jochem van Dieten <jochemd(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-16 20:38:31
Message-ID: f96a9b83050116123845d1d67f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-patches
On Sun, 16 Jan 2005 20:49:45 +0100, Manfred Koizar wrote:
> On Thu, 13 Jan 2005 00:39:56 -0500, Tom Lane wrote:
>> 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.
> 
> Last time we discussed this, didn't we come to the conclusion, that
> resetting status bits is not a good idea because of possible race
> conditions?

http://archives.postgresql.org/pgsql-performance/2004-05/msg00004.php


> In a previous post you wrote:
> | I think we still have one free bit in index tuple headers...
> 
> AFAICS we'd need two new bits: "visible to all" and "maybe dead".
> 
> Writing the three status bits in the order "visible to all", "maybe
> dead", "known dead", a normal index tuple lifecycle would be
> 
>   000 -> 100 -> 110 -> 111
> 
> In states 000 and 110 the heap tuple has to be read to determine
> visibility.
> 
> The transitions 000 -> 100 and 110 -> 111 happen as side effects of
> index scans.  100 -> 110 has to be done by the deleting transaction.
> This is the operation where the additional run time cost lies.
> 
> One weakness of this approach is that once the index tuple state is
> 110 but the deleting transaction is aborted there is no easy way to
> reset the "maybe deleted" bit.  So we'd have to consult the heap for
> the rest of the tuple's lifetime.

How bad is that really with a typical workload?

Jochem

In response to

pgsql-announce by date

Next:From: Jim C. NasbyDate: 2005-01-17 00:53:01
Subject: Re: Much Ado About COUNT(*)
Previous:From: Tom LaneDate: 2005-01-16 20:22:11
Subject: Re: Much Ado About COUNT(*)

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-01-17 00:53:01
Subject: Re: Much Ado About COUNT(*)
Previous:From: Tom LaneDate: 2005-01-16 20:22:11
Subject: Re: Much Ado About COUNT(*)

pgsql-patches by date

Next:From: Jim C. NasbyDate: 2005-01-17 00:53:01
Subject: Re: Much Ado About COUNT(*)
Previous:From: Tom LaneDate: 2005-01-16 20:22:11
Subject: Re: Much Ado About COUNT(*)

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