Re: ctid access is slow

On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
> Hello!
>
> Could anybody help me with this [artificial] query
>
> select ctid from aaa where ctid in (select ctid from aaa limit 10);
>
> here is explained plan
>
> Nested Loop IN Join (cost=300000000.47..300325932.99 rows=10 width=6)
> Join Filter: ("outer".ctid = "inner".ctid)
> -> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998 width=6)
> -> Materialize (cost=0.47..0.57 rows=10 width=6)
> -> Subquery Scan "IN_subquery" (cost=0.00..0.46 rows=10 width=6)
> -> Limit (cost=0.00..0.36 rows=10 width=6)
> -> Seq Scan on aaa (cost=0.00..44457.98 rows=1250998
> width=6)
>
> There are 1250998 records in aaa.
>
> As you see it is pretty slow - actually this thing is faster
> even if I use oid instead of ctid.
> Inner query works promptly of course.
>
> Any clue?
>

I think using an indexed field would probably be faster for you, especially if
you have a PK on the table. Barring that, make sure you have
vacuumed/analyzed and send us explain analyze output.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL