单条插入数据准备:
test=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+-----------
a | integer |
b | character varying(20) |
Distributed by: (a)
test=# insert into test values(generate_series(1,50000),'aaaaaaaaaa');
INSERT 0 50000
pg_dump -Fp -ftest.sql --inserts test –ttest
INSERT INTO public.test VALUES (1, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (5, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (11, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (12, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (14, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (15, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (17, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (20, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (23, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (25, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (26, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (30, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (31, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (35, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (36, 'aaaaaaaaaa');
批量加载数据准备:
copy test_heap to '/home/mppadmin/test.dat' delimiter '|';
2|aaaaaaaaaa
1|aaaaaaaaaa
3|aaaaaaaaaa
5|aaaaaaaaaa
4|aaaaaaaaaa
11|aaaaaaaaaa
6|aaaaaaaaaa
12|aaaaaaaaaa
7|aaaaaaaaaa
单条插入测试:
select current_timestamp;
INSERT INTO public.test VALUES (1, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (5, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (11, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (12, 'aaaaaaaaaa');
……
INSERT INTO public.test VALUES (49994, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (49995, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (49996, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (49997, 'aaaaaaaaaa');
INSERT INTO public.test VALUES (49999, 'aaaaaaaaaa');
select current_timestamp;
批量加载测试:
select current_timestamp;
copy test from '/home/mppadmin/test.dat' delimiter '|';
select current_timestamp;
Heap表:
test=# \d+ test_heap
Table "public.test_heap"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
a | integer | | plain | |
b | character varying(20) | | extended | |
Distributed by: (a)
单条插入:
2024-07-29 22:25:19 ~ 2024-07-29 22:25:58
批量插入:
2024-07-29 22:39:58.597309+08 ~ 2024-07-29 22:39:58.81317+08
AO表:
test=# \d+ test
Append-Only Columnar Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+-----------------------+-----------+----------+--------------+------------------+-------------------+------------+-------------
a | integer | | plain | | zlib | 5 | 32768 |
b | character varying(20) | | extended | | zlib | 5 | 32768 |
Checksum: t
Distributed by: (a)
Options: appendonly=true, compresstype=zlib, compresslevel=5, orientation=column
单条插入:
2024-07-29 22:31:37 ~ 2024-07-29 22:33:29
批量插入:
2024-07-29 22:41:53.848749+08 ~ 2024-07-29 22:41:53.970006+08
结论:
- Heap表单条插入50000条用时:39s
- AO表单条插入50000条用时:112s
- Heap表批量插入50000条用时:<1s
- AO表批量插入50000条用时:<1s
插入效率:批量 < heap表单条插入 < AO表单条插入
本站资源均来自互联网,仅供研究学习,禁止违法使用和商用,产生法律纠纷本站概不负责!如果侵犯了您的权益请与我们联系!
转载请注明出处: 免费源码网-免费的源码资源网站 » KADB heap表VS AO表插入数据测试
发表评论 取消回复