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