Search Postgresql Archives

Re: Delete/update with limit

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux