KADB heap表VS AO表插入数据测试

单条插入数据准备：

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

结论：

1. Heap表单条插入50000条用时：39s
2. AO表单条插入50000条用时：112s
3. Heap表批量插入50000条用时：<1s
4. AO表批量插入50000条用时：<1s

插入效率：批量 < heap表单条插入 < AO表单条插入