[snip]
> With Oracle we do it with: delete ,tname> where <cond> and rownum <
> Y;
> Can we have the same goody on Postgres?
The attached message is Tom's response to a similar question, in any
case it would work fine in your case too (assuming you have postgres
8.2).
HTH,
Csaba.
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
