Re: ctid access is slow

Michael Fuhr <mike(at)fuhr(dot)org> writes:
>> On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
>>> select ctid from aaa where ctid in (select ctid from aaa limit 10);

> Aside from that, ctid is of type tid, and its equality operator
> isn't hashable.

Nor mergejoinable, so there's not much scope for a smart join plan.

AFAIR without rereading the code, the only case that's actually fast is

WHERE ctid = constant [ OR ctid = constant ... ]

which of course is the same as

WHERE ctid IN (constant, constant, ...)

but not at all the same as "ctid IN (sub-select)".

>>> The original idea was to collect ctid's of records to delete
>>> and use this info in DELETE statement (and something similar
>>> with UPDATE), but performance is absolutely unacceptable.

Right, you can do that, but you have to actually collect the ctid's on
the client side and incorporate them literally into the later DELETE
command. This is probably a good idea anyway to be sure you are
deleting exactly the rows you saw before, and not some other ones that
happen to now match the query conditions you gave before. Be wary also
that you can't trust a ctid to be valid longer than one transaction.

regards, tom lane