| 在PostgreSQL中实现update | delete limit 原作者：digoal/德哥 创作时间：2016-10-15 22:50:38+08 |
doudou586 发布于2016-10-15 22:50:38
 评论: 0
 浏览: 10650
 顶: 2029
 踩: 2105
|
在PostgreSQL中实现update | delete limit
2016 Postgres大象会官方报名通道： 点此报名
作者: digoal
日期: 2016-08-27
背景
使用MySQL的用户可能会比较熟悉这样的用法，更新或删除时可以指定限制更新或删除多少条记录。 达到限定的记录数后不再继续进行下去，而是返回。
delete from tbl where xxx limit 100; .. update tbl set xxx=xxx where xxx limit 100;
目前PostgreSQL没有类似的语法，但是可以通过其他手段来达到同样的效果。
with语法实现
创建测试表
postgres=# create table t(id int primary key, info text); CREATE TABLE postgres=# insert into t select generate_series(1,1000000); INSERT 0 1000000
update | delete limit 用法如下
postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10)
delete from t where id in (select * from t1);
DELETE 10
postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10)
update t set info='new' where id in (select * from t1);
UPDATE 10
postgres=# explain with t1 as (select id from t where id between 1 and 1000 limit 10)
postgres-# update t set info='new' where id in (select * from t1);
QUERY PLAN
------------------------------------------------------------------------------------
Update on t (cost=4.89..12.93 rows=1 width=38)
CTE t1
-> Limit (cost=0.42..4.44 rows=1 width=4)
-> Index Only Scan using t_pkey on t t_1 (cost=0.42..4.44 rows=1 width=4)
Index Cond: ((id >= 1) AND (id <= 1000))
-> Nested Loop (cost=0.45..8.48 rows=1 width=38)
-> HashAggregate (cost=0.02..0.03 rows=1 width=32)
Group Key: t1.id
-> CTE Scan on t1 (cost=0.00..0.02 rows=1 width=32)
-> Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
Index Cond: (id = t1.id)
(11 rows)
使用with模拟必须有PK或者非空UK，否则需要用行号，但是行号来扫就慢了，没走tid scan方法。
postgres=# with t1 as (select ctid from t where id between 1 and 100000 limit 10)
delete from t where ctid in (select ctid from t1);
DELETE 10
postgres=# with t1 as (select ctid from t where id between 1 and 100000 limit 10)
update t set info='new' where ctid in (select ctid from t1);
UPDATE 10
postgres=# explain with t1 as (select ctid from t where id between 1 and 100000 limit 10)
update t set info='new' where ctid in (select ctid from t1);
QUERY PLAN
------------------------------------------------------------------------------------
Update on t (cost=129834.64..134784.79 rows=10 width=40)
CTE t1
-> Limit (cost=0.42..0.78 rows=10 width=6)
-> Index Scan using t_pkey on t t_1 (cost=0.42..3178.93 rows=89225 width=6)
Index Cond: ((id >= 1) AND (id <= 100000))
-> Merge Semi Join (cost=129833.86..134784.01 rows=10 width=40)
Merge Cond: (t.ctid = t1.ctid)
-> Sort (cost=129833.49..132308.49 rows=990000 width=10)
Sort Key: t.ctid
-> Seq Scan on t (cost=0.00..14325.00 rows=990000 width=10) --这个NODE非常慢
-> Sort (cost=0.37..0.39 rows=10 width=36)
Sort Key: t1.ctid
-> CTE Scan on t1 (cost=0.00..0.20 rows=10 width=36)
(13 rows)
这应该也是可以优化的地方，已反馈给社区。
函数+游标实现
do language plpgsql $$
declare
rowvar record;
cur cursor for select * from t where id between 1 and 1000000 limit 10;
begin
open cur;
loop
fetch cur into rowvar;
if found then
update t set info='new' where current of cur returning * into rowvar;
raise notice '%', rowvar;
else
return;
end if;
end loop;
close cur;
end;
$$;
NOTICE: 00000: (10011,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10012,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10013,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10014,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10015,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10016,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10017,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10018,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10019,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
NOTICE: 00000: (10020,new)
LOCATION: exec_stmt_raise, pl_exec.c:3216
DO
期待阿里云PostgreSQL扩展支持这个MySQL语法。
2016 Postgres大象会官方报名通道：http://www.huodongxing.com/event/8352217821400
扫描报名
请在登录后发表评论，否则无法保存。
