Re: ctid access is slow

On Tue, Aug 23, 2005 at 09:15:42AM -0400, Robert Treat wrote:
> On Tuesday 23 August 2005 08:39, Ilja Golshtein wrote:
> >
> > select ctid from aaa where ctid in (select ctid from aaa limit 10);
> >
> > 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.

Aside from that, ctid is of type tid, and its equality operator
isn't hashable. Here's an example that shows the difference between
ctid (not hashable) and oid (hashable) on a table with 100000 rows:

EXPLAIN ANALYZE SELECT ctid FROM foo WHERE ctid IN (SELECT ctid FROM foo LIMIT 10);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=0.27..24137.27 rows=10 width=6) (actual time=0.127..12729.741 rows=10 loops=1)
Join Filter: ("outer".ctid = "inner".ctid)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=6) (actual time=0.029..951.297 rows=100000 loops=1)
-> Materialize (cost=0.27..0.37 rows=10 width=6) (actual time=0.005..0.052 rows=10 loops=100000)
-> Subquery Scan "IN_subquery" (cost=0.00..0.26 rows=10 width=6) (actual time=0.037..0.318 rows=10 loops=1)
-> Limit (cost=0.00..0.16 rows=10 width=6) (actual time=0.023..0.195 rows=10 loops=1)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=6) (actual time=0.013..0.094 rows=10 loops=1)
Total runtime: 12730.011 ms
(8 rows)

EXPLAIN ANALYZE SELECT oid FROM foo WHERE oid IN (SELECT oid FROM foo LIMIT 10);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Hash IN Join (cost=0.29..2137.39 rows=10 width=4) (actual time=0.574..1477.235 rows=10 loops=1)
Hash Cond: ("outer".oid = "inner".oid)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=4) (actual time=0.016..864.519 rows=100000 loops=1)
-> Hash (cost=0.26..0.26 rows=10 width=4) (actual time=0.412..0.412 rows=0 loops=1)
-> Subquery Scan "IN_subquery" (cost=0.00..0.26 rows=10 width=4) (actual time=0.063..0.336 rows=10 loops=1)
-> Limit (cost=0.00..0.16 rows=10 width=4) (actual time=0.048..0.218 rows=10 loops=1)
-> Seq Scan on foo (cost=0.00..1637.00 rows=100000 width=4) (actual time=0.035..0.118 rows=10 loops=1)
Total runtime: 1477.508 ms
(8 rows)

--
Michael Fuhr