Re: Delete/update with limit

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> Unfortunately the stuff that makes a ctid=<value> nice doesn't seem to be
> used when you're doing an in.

Yeah, see the header comments in tidpath.c:

* There is currently no special support for joins involving CTID; in
* particular nothing corresponding to best_inner_indexscan(). Since it's
* not very useful to store TIDs of one table in another table, there
* doesn't seem to be enough use-case to justify adding a lot of code
* for that.

Of course, that argument is wrong for a self-join, which is what this
would essentially be. So maybe it would be worth doing sometime.
Still, the issue doesn't come up very often.

[ thinks for a bit ... ] Actually, you can do it as of 8.2 or so,
by abusing the ScalarArrayOp stuff: turn the subquery into an array.
An example in the regression database:

regression=# explain update tenk1 set ten=ten+1
regression-# where ctid = any (array(select ctid from tenk1 limit 10));
QUERY PLAN
-------------------------------------------------------------------------
Tid Scan on tenk1 (cost=0.46..40.71 rows=10 width=250)
TID Cond: (ctid = ANY ($0))
InitPlan
-> Limit (cost=0.00..0.46 rows=10 width=6)
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=6)
(5 rows)

It even seems to get the cost estimate right...

regards, tom lane