Re: allow LIMIT in UPDATE and DELETE

On Fri, May 19, 2006 at 04:22:42PM +0200, Csaba Nagy wrote:
> "Then use ctid."
>
> For the problem at hand in your post it is a good solution, except
> that it will cause a full table scan cause I guess few people have
> indexes on ctid. Or you have to write your queries really contrived,
> by duplicating most of your query conditions so that it can use some
> indexes. I'm not sure if you'll get away without at least 2 full
> table scans if using ctid and no indexes, one for the subquery and
> one for the delete itself... not to mention the need for something
> like a HashAggregate on the subquery results... all this is
> speculation, but for sure you'll spend 10x the time for optimizing
> the subquery then you would writing a simple DELETE with LIMIT.

Err, you don't need an index on ctid because the ctid represents that
physical location of the tuple on disk. ctids are what indexes use to
refer to tuples...

# explain DELETE FROM t WHERE ctid=(SELECT ctid FROM t WHERE pronargs=1 LIMIT 1);
QUERY PLAN
----------------------------------------------------------------
Tid Scan on t (cost=3.75..7.76 rows=2 width=6)
Filter: (ctid = $0)
InitPlan
-> Limit (cost=0.00..3.75 rows=1 width=6)
-> Seq Scan on t (cost=0.00..22.50 rows=6 width=6)
Filter: (pronargs = 1)
(6 rows)

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.