Re: vacuum, performance, and MVCC

On 6/24/06, Mark Woodward <pgsql@mohawksoft.com> wrote:
> Currently it looks like this:
>
> ver001->ver002->ver003->...-verN
>
> That's what t_ctid does now, right? Well, that's sort of stupid. Why not
> have it do this:
>
> ver001->verN->...->ver003->ver002->|

Heh, because that's crazy.  The first time you insert a key into the
index it will point to v1 of a tuple... say after 5 updates you have
v2,v3,v4,v5... your c_tid pointer chain looks like v1
(original)->v2->v3->v4-v5 (newest).  However, your whole idea is based
on not having to do another index insert for unchanged keys, so the
index still points to v1... which means you have to follow the c_tid
chain to get to the newest version just like a sequential scan.  I
don't see how you think you can reverse pointer it.

> This will speed up almost *all* queries when there are more than two
> version of rows.

Nope.

> When you vacuum, simply make the latest version (verN) the key row (ver001).

How are you going to do this without a ton of locking... remember, the
index is pointing to v1 with a tid... so you'll have to physically
move the newest version v5 to v1's tid from wherever it was... like a
vacuum full on steroids.  Unless of course, you rebuild the index...
but that's not a solution either.

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 2nd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/