Re: Delete/update with limit

Stephan Szabo <sszabo@megazone.bigpanda.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