Re: ctid access is slow

From: Vivek Khera <vivek(at)khera(dot)org>
To: pgsql general list <pgsql-general(at)postgresql(dot)org>
Subject: Re: ctid access is slow
Date: 2005-08-25 18:52:20
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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=

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2005-08-25 18:52:31 Re: Postgresql replication
Previous Message Vivek Khera 2005-08-25 18:25:27 Re: Is there such a thing as a 'background database job'?