postgresql中 建表语句、索引和插入数据如下:
CREATE TABLE test(
ID SERIAL PRIMARY KEY NOT NULL,
UserID varchar(100) NOT NULL,
UserName varchar(100) NOT NULL,
PhoneNumber varchar(20) NOT NULL,
UserAreaID varchar(100) NOT NULL,
ConsumeSum float8 NOT NULL,
ConsumeTimes int NOT NULL,
AverageMoney float8 NOT NULL,
LastConsumeTime timestamp(6),
LastConsumeShop varchar(100),
LastConsumeGoods varchar(100),
nodeid int
);
create index idx_test on test (ID,UserID,UserName,PhoneNumber,UserAreaID);
insert into test (UserID,UserName,PhoneNumber,UserAreaID,ConsumeSum,ConsumeTimes,AverageMoney,LastConsumeTime,LastConsumeShop,LastConsumeGoods,nodeid )
select generate_series(1,700000),'picc','123456','asia-beijing',100.00,99,60,'2018-10-10','插入数据','更新数据',1;
需要更新全表数据,时间很长,请问有什么方法优化。
update test set lastconsumeshop='优化语句' ;