ctid access is slow

From: "Ilja Golshtein" <ilejn(at)yandex(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: ctid access is slow
Date: 2005-08-23 12:39:13
Message-ID: 430B18F1.000002.16967@tide.yandex.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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.

Help is very very appeciated!

--
Best regards
Ilja Golshtein

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2005-08-23 13:15:42 Re: ctid access is slow
Previous Message Leonel Nunez 2005-08-23 11:51:56 Re: Consulta