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 |
Message-ID: | F36FE7E8-2419-47BB-A9AA-FCCDE24A11ED@khera.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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=
$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
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'? |