Re: ctid access is slow

On Tue, Aug 23, 2005 at 05:30:31PM +0400, Ilja Golshtein wrote:
> Hi!
> 
> >> Could anybody help me with this [artificial] query
> >>
> >> select ctid from aaa where ctid in (select ctid from aaa limit 10);
> 
> [skipped]
> 
> >I think using an indexed field would probably be faster for you,
> >especially if you have a PK on the table.  
> 
> I used to think ctid is the same as rowid in Oracle,
> where rowid access is the fastest. Is it wrong?

I don't know what is rowid internally, but in Postgres, ctid _is_ the
fastest way to access a (single) tuple, because it's the physical
address.  However, since the ctid datatype does not implement hashjoin
nor mergejoin, the plans are not as good when you have to access lots of
tuples.

> After all, why oid is faster then ctid?

Accessing single values by oid, even when there is an index, will be
slower than accessing single values by ctid; though in practice,
probably there won't be a measurable difference.

If you are too worried about it, you could look at what is needed to
implement hashjoin and mergejoin for ctids.  I take it it isn't trivial,
or it would be done already, but I don't think it's too hard (unless
there is an implementation detail that makes it impossible).

-- 
Alvaro Herrera ()
"Nadie esta tan esclavizado como el que se cree libre no siendolo" (Goethe)