Re: ctid access is slow

On Aug 23, 2005, at 10:02 AM, Ilja Golshtein wrote:

> The only thing I am curios is ctid good for
> anything from user point of view?
>

I have a very specific use for it -- to bypass the index on an
update.  Something like this:

select ctid,user_id from users where ...
  ... do stuff based on user_id ...
update users set last_mod=CURRENT_TIME where ctid='$ctid' and user_id=
$user_id

since I have already locked those rows earlier in the transaction I
worry not about anyone else updating those rows.  However, the extra
safetynet of checking that the current row at $ctid is still the one
I want, I check that.  If the row is not updated (ie, count 0
returned) then I do a standard update based just on the user_id which
is the PK.

When you add this up over millions of rows, it makes a difference to
bypass the PK index lookup every time.

Vivek Khera, Ph.D.
+1-301-869-4449 x806