阿里云静态网站托管,django商城网站开发的功能,js开发安卓app,点击seo软件时序数据合并场景加速分析和实现 - 复合索引#xff0c;窗口分组查询加速#xff0c;变态递归加速 作者 digoal 日期 2016-11-28 标签 PostgreSQL , 数据合并 , 时序数据 , 复合索引 , 窗口查询 背景 在很多场景中#xff0c;都会有数据合并的需求。 例如记录了表的变更明细… 时序数据合并场景加速分析和实现 - 复合索引窗口分组查询加速变态递归加速 作者 digoal 日期 2016-11-28 标签 PostgreSQL , 数据合并 , 时序数据 , 复合索引 , 窗口查询 背景 在很多场景中都会有数据合并的需求。 例如记录了表的变更明细(insert,update,delete)需要合并明细从明细中快速取到每个PK的最新值。 又比如有很多传感器不断的在上报数据要快速的取出每个传感器的最新状态。 对于这种需求可以使用窗口查询但是如何加速如何快速的取出批量数据呢 这个是有优化的门道的。 传感器例子 假设传感器数据不断的上报用户需要查询当前最新的每个传感器上报的值。 创建测试表如下 create unlogged table sort_test(id serial8 primary key, -- 主键c2 int, -- 传感器IDc3 int -- 传感器值
); 写入1000万传感器测试数据
postgres# insert into sort_test (c2,c3) select random()*100000, random()*100 from generate_series(1,10000000);
INSERT 0 10000000 查询语句如下 postgres# explain (analyze,verbose,timing,costs,buffers) select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn1;QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------Subquery Scan on t (cost10001512045.83..10001837045.83 rows50000 width16) (actual time23865.363..44033.984 rows100001 loops1)Output: t.id, t.c2, t.c3Filter: (t.rn 1)Rows Removed by Filter: 9899999Buffers: shared hit54055, temp read93801 written93801- WindowAgg (cost10001512045.83..10001712045.83 rows10000000 width24) (actual time23865.351..41708.460 rows10000000 loops1)Output: sort_test.id, sort_test.c2, sort_test.c3, row_number() OVER (?)Buffers: shared hit54055, temp read93801 written93801- Sort (cost10001512045.83..10001537045.83 rows10000000 width16) (actual time23865.335..31540.089 rows10000000 loops1)Output: sort_test.id, sort_test.c2, sort_test.c3Sort Key: sort_test.c2, sort_test.id DESCSort Method: external merge Disk: 254208kBBuffers: shared hit54055, temp read93801 written93801- Seq Scan on public.sort_test (cost10000000000.00..10000154055.00 rows10000000 width16) (actual time0.021..1829.135 rows10000000 loops1)Output: sort_test.id, sort_test.c2, sort_test.c3Buffers: shared hit54055Planning time: 0.194 msExecution time: 44110.560 ms
(18 rows) 优化手段新增复合索引避免SORT注意id需要desc postgres# create index sort_test_1 on sort_test(c2,id desc);
CREATE INDEX 优化后的SQL性能 postgres# explain (analyze,verbose,timing,costs,buffers) select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn1;QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------Subquery Scan on t (cost0.43..542565.80 rows50000 width16) (actual time0.048..33844.843 rows100001 loops1)Output: t.id, t.c2, t.c3Filter: (t.rn 1)Rows Removed by Filter: 9899999Buffers: shared hit10029020 read1- WindowAgg (cost0.43..417564.59 rows10000097 width24) (actual time0.042..30490.662 rows10000000 loops1)Output: sort_test.id, sort_test.c2, sort_test.c3, row_number() OVER (?)Buffers: shared hit10029020 read1- Index Scan using sort_test_1 on public.sort_test (cost0.43..242562.89 rows10000097 width16) (actual time0.030..18347.482 rows10000000 loops1)Output: sort_test.id, sort_test.c2, sort_test.c3Buffers: shared hit10029020 read1Planning time: 0.216 msExecution time: 33865.321 ms
(13 rows) 如果被取出的数据需要后续的处理可以使用游标分批获取因为不需要显示sort所以分批获取速度很快从而加快整个的处理速度。 \timing
begin;
declare c1 cursor for select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn1;
postgres# fetch 100 from c1;id | c2 | c3
------------------9962439 | 0 | 939711199 | 1 | 529987709 | 2 | 659995611 | 3 | 349998766 | 4 | 129926693 | 5 | 81....9905064 | 98 | 449991592 | 99 | 99
(100 rows)
Time: 31.408 ms -- 很快就返回 优化前需要显示SORT所以使用游标并不能加速拿到第一条记录是在SORT后的。 drop index sort_test_1;begin;
declare c1 cursor for select id,c2,c3 from (select id,c2,c3,row_number() over(partition by c2 order by id desc) rn from sort_test) t where rn1;postgres# fetch 100 from c1;
....
Time: 22524.783 ms -- sort结束后才开始返回很慢 增量合并数据同步例子 类似Oracle的物化视图apply时对于同一条记录的update并不需要每次update的中间过程都需要执行只需要执行最后一次的。 因此也可以利用类似的操作手段分组取最后一条 create extension hstore;create unlogged table sort_test1(id serial8 primary key, -- 主键c2 int, -- 目标表PKc3 text, -- insert or update or deletec4 hstore -- row
); create index idx_sort_test1_1 on sort_test1(c2,id desc);select c2,c3,c4 from (select c2,c3,c4,row_number() over(partition by c2 order by id desc) rn from sort_test1) t where rn1;postgres# explain select c2,c3,c4 from (select c2,c3,c4,row_number() over(partition by c2 order by id desc) rn from sort_test1) t where rn1;QUERY PLAN
---------------------------------------------------------------------------------------------------Subquery Scan on t (cost0.15..46.25 rows4 width68)Filter: (t.rn 1)- WindowAgg (cost0.15..36.50 rows780 width84)- Index Scan using idx_sort_test1_1 on sort_test1 (cost0.15..22.85 rows780 width76)
(4 rows) 稀疏列的变态优化方法 我们看到前面的优化手段其实只是消除了SORT并没有消除扫描的BLOCK数。 如果分组很少时即稀疏列还有一种更变态的优化方法递归查询。 优化方法与这篇文档类似, 《distinct xx和count(distinct xx)的变态递归优化方法》 例子 create type r as (c2 int, c3 int);postgres# explain (analyze,verbose,timing,costs,buffers) with recursive skip as ( ( select (c2,c3)::r as r from sort_test where id in (select id from sort_test where c2 is not null order by c2,id desc limit 1) ) union all ( select (select (c2,c3)::r as r from sort_test where id in (select id from sort_test t where t.c2(s.r).c2 and t.c2 is not null order by c2,id desc limit 1) ) from skip s where (s.r).c2 is not null) -- 这里的where (s.r).c2 is not null 一定要加, 否则就死循环了.
)
select (t.r).c2, (t.r).c3 from skip t where t.* is not null; QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CTE Scan on skip t (cost302.97..304.99 rows100 width8) (actual time0.077..4184.770 rows100001 loops1)Output: (t.r).c2, (t.r).c3Filter: (t.* IS NOT NULL)Rows Removed by Filter: 1Buffers: shared hit800947, temp written476CTE skip- Recursive Union (cost0.91..302.97 rows101 width32) (actual time0.066..3970.580 rows100002 loops1)Buffers: shared hit800947- Nested Loop (cost0.91..2.95 rows1 width32) (actual time0.064..0.066 rows1 loops1)Output: ROW(sort_test_1.c2, sort_test_1.c3)::rBuffers: shared hit8- HashAggregate (cost0.47..0.48 rows1 width8) (actual time0.044..0.044 rows1 loops1)Output: sort_test_2.idGroup Key: sort_test_2.idBuffers: shared hit4- Limit (cost0.43..0.46 rows1 width12) (actual time0.036..0.036 rows1 loops1)Output: sort_test_2.id, sort_test_2.c2Buffers: shared hit4- Index Only Scan using sort_test_1 on public.sort_test sort_test_2 (cost0.43..267561.43 rows10000000 width12) (actual time0.034..0.034 rows1 loops1)Output: sort_test_2.id, sort_test_2.c2Index Cond: (sort_test_2.c2 IS NOT NULL)Heap Fetches: 1Buffers: shared hit4- Index Scan using sort_test_pkey on public.sort_test sort_test_1 (cost0.43..2.45 rows1 width16) (actual time0.011..0.012 rows1 loops1)Output: sort_test_1.id, sort_test_1.c2, sort_test_1.c3Index Cond: (sort_test_1.id sort_test_2.id)Buffers: shared hit4- WorkTable Scan on skip s (cost0.00..29.80 rows10 width32) (actual time0.037..0.038 rows1 loops100002)Output: (SubPlan 1)Filter: ((s.r).c2 IS NOT NULL)Rows Removed by Filter: 0Buffers: shared hit800939SubPlan 1- Nested Loop (cost0.92..2.96 rows1 width32) (actual time0.034..0.035 rows1 loops100001)Output: ROW(sort_test.c2, sort_test.c3)::rBuffers: shared hit800939- HashAggregate (cost0.49..0.50 rows1 width8) (actual time0.023..0.023 rows1 loops100001)Output: t_1.idGroup Key: t_1.idBuffers: shared hit400401- Limit (cost0.43..0.48 rows1 width12) (actual time0.021..0.021 rows1 loops100001)Output: t_1.id, t_1.c2Buffers: shared hit400401- Index Only Scan using sort_test_1 on public.sort_test t_1 (cost0.43..133557.76 rows3333333 width12) (actual time0.019..0.019 rows1 loops100001)Output: t_1.id, t_1.c2Index Cond: ((t_1.c2 (s.r).c2) AND (t_1.c2 IS NOT NULL))Heap Fetches: 100000Buffers: shared hit400401- Index Scan using sort_test_pkey on public.sort_test (cost0.43..2.45 rows1 width16) (actual time0.006..0.007 rows1 loops100000)Output: sort_test.id, sort_test.c2, sort_test.c3Index Cond: (sort_test.id t_1.id)Buffers: shared hit400538Planning time: 0.970 msExecution time: 4209.026 ms
(54 rows) 依旧支持快速的FETCH postgres# begin;
BEGIN
Time: 0.079 ms
postgres# declare cur cursor for with recursive skip as ( ( select (c2,c3)::r as r from sort_test where id in (select id from sort_test where c2 is not null order by c2,id desc limit 1) ) union all ( select (select (c2,c3)::r as r from sort_test where id in (select id from sort_test t where t.c2(s.r).c2 and t.c2 is not null order by c2,id desc limit 1) ) from skip s where (s.r).c2 is not null) -- 这里的where (s.r).c2 is not null 一定要加, 否则就死循环了.
)
select (t.r).c2, (t.r).c3 from skip t where t.* is not null;
DECLARE CURSOR
Time: 1.240 ms
postgres# fetch 100 from cur;r
----------(0,93)(1,52)(2,65)
.....(97,78)(98,44)(99,99)
(100 rows)Time: 4.314 ms 使用变态的递归优化性能提升了10倍仅仅花了4秒完成了1000万记录的筛选。
