UDN-企业互联网技术人气社区

板块导航

浏览  : 1971
回复  : 0

[讨论交流] PostgreSQL:临时表不好用?如何发挥数组性能

[复制链接]
舞操的头像 楼主
发表于 2016-6-17 19:37:58 | 显示全部楼层 |阅读模式
  前言:SQL编程都经常会有这样一个需求:把一部分数据临时放在某个地方,经过某些业务处理后再拿回来,在postgresql中临时表可以很方便的满足这个需求,postgresql的临时表是“real temporary table”,即数据连同表结构都是随建随用的。所以如果长期大量使用临时表会带来数据库系统元数据膨胀的问题,德哥最近一篇博客提到这个问题,并给出了很好的数组替代方案:《大量使用临时表带来的问题,学会如何擦屁股》

  笔者的经验,这个方案可以再引申一下:数组本身也是有性能问题的,尤其是宽数组(数组中每个元素内部内容都比较肥胖,如Type Array/Table Array),如何规避数组性能问题?

  先说答案是:数组中只需保留一个ID号,然后再拿这个数组和源表join得到表中其它信息,口说无凭,下面用案例演示一下两种方法性能比较:

  1、环境,一台云主机2C4G,数据库版本:

复制代码


  2、建一个demo表,折中一下搞30个字段,并建一个常规索引:

  1.   postgres=# \d t
  2.                  Table "public.t"
  3. Column |            Type             | Modifiers
  4. --------+-----------------------------+-----------
  5. f01    | bigint                      |
  6. f02    | timestamp without time zone |
  7. f03    | character varying(32)       |
  8. f04    | character varying(32)       |
  9. f05    | character varying(32)       |
  10. f06    | character varying(32)       |
  11. f07    | character varying(32)       |
  12. f08    | character varying(32)       |
  13. f09    | character varying(32)       |
  14. f10    | character varying(32)       |
  15. f11    | character varying(32)       |
  16. f12    | character varying(32)       |
  17. f13    | character varying(32)       |
  18. f14    | character varying(32)       |
  19. f15    | character varying(32)       |
  20. f16    | character varying(32)       |
  21. f17    | character varying(32)       |
  22. f18    | character varying(32)       |
  23. f19    | character varying(32)       |
  24. f20    | character varying(32)       |
  25. f21    | character varying(32)       |
  26. f22    | character varying(32)       |
  27. f23    | character varying(32)       |
  28. f24    | character varying(32)       |
  29. f25    | character varying(32)       |
  30. f26    | character varying(32)       |
  31. f27    | character varying(32)       |
  32. f28    | character varying(32)       |
  33. f29    | character varying(32)       |
  34. f30    | character varying(32)       |
  35. Indexes:
  36.     "t_idx1" btree (f01)
复制代码


  注:这个工具函数会将每个varchar字段都填满:

a.png


  pgetl_tool_padtab的源代码见https://github.com/wurenny/pgetl ... source/istlplus.sql第101行

  4、写两个测试程序,分别测试“宽数组”和“简单数组”两种情况:

  1.   create or replace function f_rich(rows bigint)
  2. returns setof t as $
  3. declare
  4.         arr_t t[];
  5.         rec_t t;
  6. begin
  7.         select array_agg(t.*::t) into arr_t from t where f01<rows+1;
  8.         return query select (unnest(arr_t)).*;
  9. end;
  10. $
  11. language plpgsql;


  12. create or replace function f_sample(rows bigint)
  13. returns setof t as $
  14. declare
  15.         arr_i int8[];
  16.         rec_t t;
  17. begin
  18.         select array_agg(t.f01) into arr_i from t where f01<rows+1;
  19.         return query select t.* from t,(select (unnest(arr_i)) f01) a where t.f01=a.f01;
  20. end;
  21. $
  22. language plpgsql;
复制代码


  5、测试性能:

  10000行起测:

  1.   $ time psql -c "select * from f_rich(10000)" > /dev/null

  2. real    0m1.981s
  3. user    0m0.879s
  4. sys     0m0.019s

  5. $ time psql -c "select * from f_sample(10000)" > /dev/null

  6. real    0m1.044s
  7. user    0m0.911s
  8. sys     0m0.029s
复制代码


  性能相差近2倍,同时对比一下count性能:

  1.   postgres=# select count(*) from f_rich(10000);
  2. count
  3. -------
  4. 10000
  5. (1 row)

  6. Time: 761.541 ms

  7. postgres=# select count(*) from f_sample(10000);
  8. count
  9. -------
  10. 10000
  11. (1 row)

  12. Time: 98.194 ms
复制代码


  count性能相差7倍+

  来10W行:

  1. $ time psql -c "select * from f_rich(100000)" > /dev/null

  2. real    2m27.614s
  3. user    0m9.239s
  4. sys     0m0.195s

  5. $ time psql -c "select * from f_sample(100000)" > /dev/null

  6. real    0m11.161s
  7. user    0m9.096s
  8. sys     0m0.163s
复制代码


  性能相差10倍以上,同时对比一下count性能:

  1. postgres=# select count(*) from f_rich(100000);
  2. count  
  3. --------
  4. 100000
  5. (1 row)

  6. Time: 1418.185 ms

  7. postgres=# select count(*) from f_sample(100000);
  8. count  
  9. --------
  10. 100000
  11. (1 row)

  12. Time: 718.435 ms
复制代码


  count性能差距缩小为2倍

  再加一个数量级,来100W行:

  1. $ time psql -c "select * from f_rich(1000000)" > /dev/null
  2. ERROR:  out of memory
  3. DETAIL:  Failed on request of size 968000024.
  4. CONTEXT:  SQL statement "select (unnest(arr_t)).*"
  5. PL/pgSQL function f_rich(bigint) line 7 at RETURN QUERY

  6. real    1m16.177s
  7. user    0m0.004s
  8. sys     0m0.011s

  9. $ time psql -c "select * from f_sample(1000000)" > /dev/null

  10. real    1m50.677s
  11. user    1m31.587s
  12. sys     0m2.209s
复制代码


  可以看到由于宽数组消耗大量内存,触发了OOM,如果是生产系统这是比较危险的(我测试的这台机器硬件不怎么样,不过100W数据对于生产系统也很小),同时对比一下count性能:

  1.   postgres=# select count(*) from f_rich(1000000);
  2.   count  
  3. ---------
  4. 1000000
  5. (1 row)

  6. Time: 15346.047 ms

  7. postgres=# select count(*) from f_sample(1000000);
  8.   count  
  9. ---------
  10. 1000000
  11. (1 row)

  12. Time: 9321.805 ms
复制代码


  结论:使用宽数组当表用的时候,在处理较大数据量存在很严重的性能问题,编程的时候尽量使用简单数组再join回来可以有效规避这个问题。

  解释一下为什么count性能随着数据量上升,差距反而会变小,笔者猜测是由于unnest这个函数在处理宽数组时引发了性能问题,而count时只需要知道数组长度,不需要解开其内容,大部分计算量转移到了count本身,所以会出现这种情况,有兴趣的读者可以结合源代码做一下unnest函数性能评测

  引申一下:Greenplum目前是不支持宽数组或Type Array/Table Array的,而且也不支持层级递归查询,使用简单数组也可以一并这两个问题,见我之前写一篇博客:《四行代码让Greenplum支持recursive层级查询》

原文作者:renny 来源:开发者头条

相关帖子

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部