阿里云PostgreSQL 数据库即将推出的分区表功能。
实际测试case比社区版基于触发器的分区表插入性能提升1184倍，比基于规则的触发器插入性能提升159倍。
测试case:
1000张分区表，按INT8类型范围分区，每个分区2000万记录，测试查询，插入性能。
测试结果
1. 非分区表
查询1条记录耗时 9 毫秒
插入1010001条记录 9 秒
2. 阿里PostgreSQL 分区表
查询1条记录耗时 (第1个分区) 89 毫秒
查询1条记录耗时 (第996个分区) 93 毫秒
插入1010001条记录 (第1个分区) 4.8 秒
插入1010001条记录 (第996个分区) 4.8 秒
3. 社区PostgreSQL 分区表 (性能和分区表的判断顺序有关，越后越慢)
基于规则
查询1条记录耗时 (第1个分区) 96 毫秒
查询1条记录耗时 (第996个分区) 98 毫秒
插入1010001条记录 (第1个分区) 388 秒
插入1010001条记录 (第996个分区) 765 秒
基于触发器
查询1条记录耗时 (第1个分区) 80 毫秒
查询1条记录耗时 (第996个分区) 83 毫秒
插入1010001条记录 (第1个分区) 133 秒
插入1010001条记录 (第996个分区) 5686 秒
阿里PostgreSQL 分区表使用语法
[ PARTITION BY partition_type (column)
[ SUBPARTITION BY partition_type (column) ]
[ SUBPARTITION TEMPLATE ( template_spec ) ]
[...]
( partition_spec )
| [ SUBPARTITION BY partition_type (column) ]
[...]
( partition_spec
[ ( subpartition_spec
[(...)]
) ]
)
where partition_element is:
VALUES (list_value [,...] )
| START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
| END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
where subpartition_spec is:
subpartition_element [, ...]
and subpartition_element is:
DEFAULT SUBPARTITION subpartition_name
| [SUBPARTITION subpartition_name] VALUES (list_value [,...] )
| [SUBPARTITION subpartition_name]
START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
[ EVERY ( [number | datatype] 'interval_value') ]
| [SUBPARTITION subpartition_name]
END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ EVERY ( [number | datatype] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ TABLESPACE tablespace ]
详细测试过程：
#./tmp_basedir_for_pgsql_bld/bin/psql -h /tmp -p 1921 -U digoal postgres
psql (9.4.1)
Type "help" for help.
非分区表
阿里PostgreSQL 分区表
postgres=# create table test(id int8, info text,crt_time timestamp) partition by range (id) ( start (1) end (20000000000::int8) every (20000000::int8));
NOTICE: CREATE TABLE will create partition "test_1_prt_1" for table "test"
...
NOTICE: CREATE TABLE will create partition "test_1_prt_1000" for table "test"
CREATE TABLE
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | test | table | digoal
public | test_1_prt_1 | table | digoal
...
public | test_1_prt_1000| table | digoal
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | bigint |
info | text |
crt_time | timestamp without time zone |
Number of child tables: 1000 (Use \d+ to list them.)
postgres=# \d+ test
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | bigint | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | | plain | |
Child tables: test_1_prt_1,
...
postgres=# \d test_1_prt_89
Table "public.test_1_prt_89"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | bigint |
info | text |
crt_time | timestamp without time zone |
Check constraints:
"test_1_prt_89_check" CHECK (id >= 1760000001::bigint AND id < 1780000001::bigint)
Inherits: test
postgres=# insert into test select generate_series (19990000,21000000);
INSERT 0 1010001
Time: 4850.703 ms
postgres=# insert into test select generate_series (19919990000,19921000000);
INSERT 0 1010001
Time: 4774.363 ms
在主表创建索引，会自动在分区表创建对应的索引。
postgres=# create index idx_test_id on test(id);
CREATE INDEX
Time: 2003.232 ms
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | bigint |
info | text |
crt_time | timestamp without time zone |
Indexes:
"idx_test_id" btree (id)
Number of child tables: 100 (Use \d+ to list them.)
postgres=# \d test_1_prt_99
Table "public.test_1_prt_99"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | bigint |
info | text |
crt_time | timestamp without time zone |
Indexes:
"test_1_prt_99_idx_test_id" btree (id)
Check constraints:
"test_1_prt_99_check" CHECK (id >= 1960000001::bigint AND id < 1980000001::bigint)
Inherits: test
postgres=# select * from test where id=19919990000;
id | info | crt_time
-------------+------+----------
19919990000 | |
(1 row)
Time: 93.481 ms
postgres=# select * from test where id=19990000;
id | info | crt_time
----------+------+----------
19990000 | |
(1 row)
Time: 89.114 ms
postgres=# explain select * from test where id=19990000;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Append (cost=0.00..8.30 rows=2 width=48)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=48)
Filter: (id = 19990000)
-> Index Scan using test_1_prt_1_idx_test_id on test_1_prt_1 (cost=0.29..8.30 rows=1 width=48)
Index Cond: (id = 19990000)
(5 rows)
Time: 92.461 ms
postgres=# explain select * from test where id=19919990000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Append (cost=0.00..8.30 rows=2 width=48)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=48)
Filter: (id = 19919990000::bigint)
-> Index Scan using test_1_prt_996_idx_test_id on test_1_prt_996 (cost=0.29..8.30 rows=1 width=48)
Index Cond: (id = 19919990000::bigint)
(5 rows)
Time: 95.625 ms
基于规则的分区表测试
建表繁琐。
性能比较差。
postgres=# create table test1(id int8, info text, crt_time timestamp);
CREATE TABLE
Time: 12.851 ms
postgres=# create index idx_test1_id on test1(id);
CREATE INDEX
Time: 9.513 ms
建表
postgres=# do language plpgsql
$$
declare
i int;
begin
for i in 1..1000 loop
execute 'create table test1_'||i||'(like test1 including all) inherits(test1)';
execute 'alter table test1_'||i||' add constraint ck_test1_'||i||' check(id>='||20000000::int8*(i-1)+1||' and id<'||20000000::int8*i+1||')';
end loop;
end;
$$
;
建规则
do language plpgsql
$$
declare
i int;
begin
for i in 1..1000 loop
execute 'create or replace rule r'||i||' as on insert to test1 where id >= '||20000000::int8*(i-1)+1||' and id<'||20000000::int8*i+1||' do instead (insert into test1_'||i||' values (new.id,new.info,new.crt_time))';
end loop;
end;
$$
;
测试插入性能
postgres=# insert into test1 select generate_series (19990000,21000000);
INSERT 0 0
Time: 387710.425 ms
postgres=# insert into test1 select generate_series (19919990000,19921000000);
INSERT 0 0
Time: 764688.154 ms
测试查询性能
postgres=# select * from test1 where id=19919990000;
id | info | crt_time
-------------+------+----------
19919990000 | |
(1 row)
Time: 98.881 ms
postgres=# select * from test1 where id=19990000;
id | info | crt_time
----------+------+----------
19990000 | |
(1 row)
Time: 96.142 ms
postgres=# explain select * from test1 where id=19990000;
QUERY PLAN
-------------------------------------------------------------------------------------
Append (cost=0.00..8.30 rows=2 width=48)
-> Seq Scan on test1 (cost=0.00..0.00 rows=1 width=48)
Filter: (id = 19990000)
-> Index Scan using test1_1_id_idx on test1_1 (cost=0.29..8.30 rows=1 width=48)
Index Cond: (id = 19990000)
(5 rows)
Time: 96.185 ms
postgres=# explain select * from test1 where id=19919990000;
QUERY PLAN
-----------------------------------------------------------------------------------------
Append (cost=0.00..8.30 rows=2 width=48)
-> Seq Scan on test1 (cost=0.00..0.00 rows=1 width=48)
Filter: (id = 19919990000::bigint)
-> Index Scan using test1_996_id_idx on test1_996 (cost=0.29..8.30 rows=1 width=48)
Index Cond: (id = 19919990000::bigint)
(5 rows)
Time: 100.640 ms
基于触发器的分区表测试
性能最差
create table test2(id int8, info text, crt_time timestamp);
postgres=# do language plpgsql
$$
declare
i int;
begin
for i in 1..1000 loop
execute 'create table test2_'||i||'(like test2 including all) inherits(test2)';
execute 'alter table test2_'||i||' add constraint ck_test2_'||i||' check(id>='||20000000::int8*(i-1)+1||' and id<'||20000000::int8*i+1||')';
end loop;
end;
$$
;
生成触发器逻辑部分
postgres=# do language plpgsql
$$
declare
i int;
begin
for i in 1..1000 loop
raise notice '%', 'if (new.id >= '||20000000::int8*(i-1)+1||' and new.id<'||20000000::int8*i+1||') then insert into test2_'||i||' values (NEW.*); return null; end if;';
end loop;
end;
$$
;
NOTICE: if (new.id >= 1 and new.id<20000001) then insert into test2_1 values (NEW.*); return null; end if;
...
NOTICE: if (new.id >= 19980000001 and new.id<20000000001) then insert into test2_1000 values (NEW.*); return null; end if;
DO
建立触发器函数
create or replace function tg1() returns trigger as
$$
declare
begin
if (new.id >= 1 and new.id<20000001) then insert into test2_1 values (NEW.*); return null; end if;
...
if (new.id >= 19980000001 and new.id<20000000001) then insert into test2_1000 values (NEW.*); return null; end if;
return null;
end;
$$
language plpgsql;
CREATE FUNCTION
Time: 55.847 ms
建立触发器
postgres=# create trigger tg1 before insert on test2 for each row execute procedure tg1();
CREATE TRIGGER
Time: 10.472 ms
测试插入性能
postgres=# insert into test2 select generate_series (19990000,21000000);
INSERT 0 0
Time: 132572.550 ms
postgres=# insert into test2 select generate_series (19919990000,19921000000);
INSERT 0 0
Time: 5686336.622 ms
测试查询性能
postgres=# select * from test2 where id=19919990000;
id | info | crt_time
-------------+------+----------
19919990000 | |
(1 row)
Time: 83.109 ms
postgres=# select * from test2 where id=19990000;
id | info | crt_time
----------+------+----------
19990000 | |
(1 row)
Time: 79.735 ms
postgres=# explain select * from test2 where id=19990000;
QUERY PLAN
----------------------------------------------------------------
Append (cost=0.00..170.01 rows=2 width=48)
-> Seq Scan on test2 (cost=0.00..0.00 rows=1 width=48)
Filter: (id = 19990000)
-> Seq Scan on test2_1 (cost=0.00..170.01 rows=1 width=48)
Filter: (id = 19990000)
(5 rows)
Time: 76.744 ms
postgres=# explain select * from test2 where id=19919990000;
QUERY PLAN
------------------------------------------------------------------
Append (cost=0.00..170.01 rows=2 width=48)
-> Seq Scan on test2 (cost=0.00..0.00 rows=1 width=48)
Filter: (id = 19919990000::bigint)
-> Seq Scan on test2_996 (cost=0.00..170.01 rows=1 width=48)
Filter: (id = 19919990000::bigint)
(5 rows)
Time: 81.251 ms
单表测试：
postgres=# \d t
Table "public.t"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | bigint |
info | text |
crt_time | timestamp without time zone |
Indexes:
"t_id_idx" btree (id)
postgres=# insert into t select generate_series (19990000,21000000);
INSERT 0 1010001
Time: 9012.904 ms
postgres=# select * from t where id=19990000;
id | info | crt_time
----------+------+----------
19990000 | |
(1 row)
Time: 9.133 ms
[小结]
1. 性能提升关键点在哪里？
社区版的PG，要支持分区要的功能，可以用规则或者触发器，但是可以明显的感到，当分区表数量越多时，效率越低，并且排在后面的分区表性能更低。
通过oprofile来跟踪代码的开销：
wget http://prdownloads.sourceforge.net/oprofile/oprofile-1.1.0.tar.gz
tar -zxvf oprofile-1.1.0.tar.gz
cd oprofile-1.1.0
yum install -y binutils-devel
./configure
make
make install
1.1. AliCloudDB for PostgreSQL
#operf --system-wide --lazy-conversion
postgres=# insert into test select generate_series (19999000000,19999100000);
INSERT 0 100001
Time: 564.876 ms
# ctrl + c退出operf
#opreport -l -f -g -w -x -t 1 /home/digoal/tmp_basedir_for_pgsql_bld/bin/postgres
Using /home/oprofile_data/samples/ for samples directory.
WARNING: Lost samples detected! See /home/oprofile_data/samples/operf.log for details.
CPU: Intel Sandy Bridge microarchitecture, speed 2299.94 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
vma samples % linenr info symbol name
0050e3d7 3501 15.5600 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:843 XLogInsert
004d6f8f 1695 7.5333 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:388 _bt_compare
007d6e1c 779 3.4622 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/../../../../src/include/storage/s_lock.h:219 tas
004d6d48 520 2.3111 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:283 _bt_binsrch
00930ff1 413 1.8356 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:824 hash_search_with_hash_value
007e026b 409 1.8178 /home/digoal/rds_pg_9400/src/backend/storage/page/bufpage.c:182 PageAddItem
007d7c22 373 1.6578 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:1119 LWLockRelease
004d66ec 367 1.6311 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:60 _bt_search
004c945a 352 1.5644 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtcompare.c:130 btint8cmp
004ca949 321 1.4267 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtinsert.c:713 _bt_insertonpg
0092b721 305 1.3556 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:1342 FunctionCall2Coll
007ad768 302 1.3422 /home/digoal/rds_pg_9400/src/backend/storage/buffer/../../../../src/include/storage/s_lock.h:219 tas
007d72e3 292 1.2978 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:484 LWLockAcquireCommon
0094f01f 289 1.2844 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:936 AllocSetFree
0094e950 288 1.2800 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:654 AllocSetAlloc
00689d4b 276 1.2267 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5283 ExecTargetList
0050fe98 272 1.2089 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:1881 XLogBytePosToRecPtr
007b0081 271 1.2044 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1893 BufferGetBlockNumber
004ab68f 255 1.1333 /home/digoal/rds_pg_9400/src/backend/access/heap/heapam.c:2054 heap_insert
004d6acb 254 1.1289 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:179 _bt_moveright
004a052a 253 1.1244 /home/digoal/rds_pg_9400/src/backend/access/hash/hashfunc.c:306 hash_any
006823a6 251 1.1156 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1666 ExecMakeFunctionResult
009509e9 246 1.0933 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:690 pfree
007b19fc 241 1.0711 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:2788 LockBuffer
最大的开销在XLogInsert，和插入单表的开销基本一致。相比规则和触发器少了很多开销。
1.2. 社区版，基于规则
#operf --system-wide --lazy-conversion
postgres=# insert into test1 select generate_series (19999000000,19999100000);
INSERT 0 0
Time: 40349.332 ms
# ctrl + c退出operf
#opreport -l -f -g -w -x -t 0.1 /home/digoal/tmp_basedir_for_pgsql_bld/bin/postgres
Using /home/oprofile_data/samples/ for samples directory.
WARNING! Some of the events were throttled. Throttling occurs when
the initial sample rate is too high, causing an excessive number of
interrupts. Decrease the sampling frequency. Check the directory
/home/oprofile_data/samples/current/stats/throttled
for the throttled event names.
WARNING: Lost samples detected! See /home/oprofile_data/samples/operf.log for details.
CPU: Intel Sandy Bridge microarchitecture, speed 2299.94 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
vma samples % linenr info symbol name
00682b07 121678 13.3298 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1977 ExecMakeFunctionResultNoSets
00689d4b 72127 7.9015 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5283 ExecTargetList
00689beb 63456 6.9516 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5169 ExecQual
0085c4db 55855 6.1189 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:1441 generate_series_step_int8
00680455 46819 5.1290 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:665 ExecEvalScalarVarFast
006823a6 43900 4.8092 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1666 ExecMakeFunctionResult
0085a79c 42942 4.7043 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:249 int8ge
00463c7d 40070 4.3896 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:1156 slot_getattr
0085a70c 36748 4.0257 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:222 int8lt
00680df4 35229 3.8593 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1082 ExecEvalConst
0068a5d3 32706 3.5829 /home/digoal/rds_pg_9400/src/backend/executor/execScan.c:111 ExecScan
0068b30a 28203 3.0896 /home/digoal/rds_pg_9400/src/backend/executor/execTuples.c:450 ExecClearTuple
0068a0e2 27186 2.9782 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5425 ExecProject
0067f870 25356 2.7777 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:90 list_head
0067f84c 23400 2.5635 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/utils/palloc.h:92 MemoryContextSwitchTo
00761e17 22180 2.4298 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1508 pgstat_init_function_usage
007eaf66 21910 2.4002 /home/digoal/rds_pg_9400/src/backend/tcop/postgres.c:3232 check_stack_depth
00762040 21459 2.3508 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1580 pgstat_end_function_usage
0068b426 19698 2.1579 /home/digoal/rds_pg_9400/src/backend/executor/execTuples.c:499 ExecStoreVirtualTuple
0067f183 19529 2.1394 /home/digoal/rds_pg_9400/src/backend/executor/execProcnode.c:356 ExecProcNode
0068a448 18984 2.0797 /home/digoal/rds_pg_9400/src/backend/executor/execScan.c:40 ExecScanFetch
0092e0f7 17211 1.8855 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/funcapi.c:130 per_MultiFuncCall
0094fb5d 13472 1.4758 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:131 MemoryContextReset
006a5f2c 9489 1.0395 /home/digoal/rds_pg_9400/src/backend/executor/nodeResult.c:68 ExecResult
006aba50 9145 1.0018 /home/digoal/rds_pg_9400/src/backend/executor/nodeSubqueryscan.c:47 SubqueryNext
0085a8bc 4853 0.5316 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:306 int84ge
0085a82c 4654 0.5098 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:279 int84lt
0050e3d7 3616 0.3961 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:843 XLogInsert ------------------------------------ 单表最大开销位置，上面都是多余部分
0085c4c1 3080 0.3374 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:1435 generate_series_int8
004d6f8f 2033 0.2227 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:388 _bt_compare
007d6e1c 1036 0.1135 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/../../../../src/include/storage/s_lock.h:219 tas
0094e950 922 0.1010 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:654 AllocSetAlloc
XLogInsert只占了0.3374%，大量的开销耗费在executor
1.3. 社区版，基于触发器
#operf --system-wide --lazy-conversion
postgres=# insert into test2 select generate_series (19999000000,19999100000);
INSERT 0 0
Time: 595721.715 ms
# ctrl + c退出operf
#opreport -l -f -g -w -x -t 0.01 /home/digoal/tmp_basedir_for_pgsql_bld/bin/postgres
Using /home/oprofile_data/samples/ for samples directory.
WARNING! Some of the events were throttled. Throttling occurs when
the initial sample rate is too high, causing an excessive number of
interrupts. Decrease the sampling frequency. Check the directory
/home/oprofile_data/samples/current/stats/throttled
for the throttled event names.
WARNING: Lost samples detected! See /home/oprofile_data/samples/operf.log for details.
CPU: Intel Sandy Bridge microarchitecture, speed 2299.94 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
vma samples % linenr info symbol name
0094e950 481019 8.1028 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:654 AllocSetAlloc
00680f09 277442 4.6735 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1134 ExecEvalParamExtern
00682b07 270488 4.5564 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1977 ExecMakeFunctionResultNoSets
009509e9 256627 4.3229 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:690 pfree
0094f01f 252116 4.2469 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:936 AllocSetFree
007c0ba0 246408 4.1508 /home/digoal/rds_pg_9400/src/backend/storage/ipc/procarray.c:1359 GetSnapshotData
0094e308 231822 3.9051 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:318 AllocSetFreeIndex
006840b9 210097 3.5391 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:2804 ExecEvalAnd
0094e658 174083 2.9324 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:553 AllocSetReset
0067f870 167061 2.8142 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:90 list_head
006b5803 150638 2.5375 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:1654 SPI_plan_get_cached_plan
00908c9c 142255 2.3963 /home/digoal/rds_pg_9400/src/backend/utils/cache/../../../../src/include/nodes/pg_list.h:90 list_head
0094f788 125489 2.1139 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:1292 AllocSetCheck
00461c7b 120680 2.0329 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:326 nocachegetattr
00761e17 100771 1.6975 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1508 pgstat_init_function_usage
006d0599 100441 1.6919 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:57 check_list_invariants
007d6e1c 87467 1.4734 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/../../../../src/include/storage/s_lock.h:219 tas
005452d9 85166 1.4346 /home/digoal/rds_pg_9400/src/backend/catalog/namespace.c:3146 OverrideSearchPathMatchesCurrent
00680df4 84555 1.4243 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1082 ExecEvalConst
009508b3 83698 1.4099 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:665 palloc0
006d3891 83184 1.4012 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:812 bms_first_member
0090aa27 79325 1.3362 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1412 AcquireExecutorLocks
006d263e 74906 1.2618 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:1157 list_copy
0090abc8 69602 1.1724 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1482 AcquirePlannerLocks
00909a7b 67500 1.1370 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:771 CheckCachedPlan
00966d16 65457 1.1026 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:566 PopActiveSnapshot
009507f3 63620 1.0717 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:646 palloc
0085a79c 59801 1.0074 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:249 int8ge
006e174c 58969 0.9933 /home/digoal/rds_pg_9400/src/backend/nodes/../../../src/include/nodes/pg_list.h:90 list_head
006b3d44 58768 0.9900 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:824 SPI_fnumber
005410a0 55070 0.9277 /home/digoal/rds_pg_9400/src/backend/catalog/../../../src/include/utils/palloc.h:92 MemoryContextSwitchTo
0094e395 54245 0.9138 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:352 wipe_mem
00953d23 52013 0.8762 /home/digoal/rds_pg_9400/src/backend/utils/resowner/resowner.c:979 ResourceOwnerForgetPlanCacheRef
006eb9cf 51246 0.8632 /home/digoal/rds_pg_9400/src/backend/nodes/equalfuncs.c:2546 equal
0094e3dd 50881 0.8571 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:372 sentinel_ok
00950530 49418 0.8324 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:574 MemoryContextAlloc
0090a324 49256 0.8297 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1213 ReleaseCachedPlan
006d335e 49138 0.8277 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:590 bms_is_empty
00966932 48470 0.8165 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:431 CopySnapshot
009095f3 46243 0.7790 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:546 RevalidateCachedQuery
00545190 44756 0.7539 /home/digoal/rds_pg_9400/src/backend/catalog/namespace.c:3094 GetOverrideSearchPath
006d2854 44481 0.7493 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:111 bms_copy
0085a70c 44293 0.7461 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:222 int8lt
0090a00a 40777 0.6869 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1095 GetCachedPlan
006b2614 39817 0.6707 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:102 list_length
006b42b6 39070 0.6581 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:898 SPI_getbinval
006b47a3 38320 0.6455 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:954 SPI_gettypeid
0094fb5d 38250 0.6443 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:131 MemoryContextReset
006d0752 37950 0.6393 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:86 new_list
006d2548 37676 0.6347 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:1100 list_free_private
006eb665 35464 0.5974 /home/digoal/rds_pg_9400/src/backend/nodes/equalfuncs.c:2457 _equalList
007d72e3 32177 0.5420 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:484 LWLockAcquireCommon
00966b65 31349 0.5281 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:504 PushActiveSnapshot
006d1381 31257 0.5265 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:551 list_delete_cell
005410c4 30352 0.5113 /home/digoal/rds_pg_9400/src/backend/catalog/../../../src/include/nodes/pg_list.h:90 list_head
007d7c22 29044 0.4892 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:1119 LWLockRelease
006d04c0 28762 0.4845 /home/digoal/rds_pg_9400/src/backend/nodes/../../../src/include/nodes/pg_list.h:90 list_head
0094e3bc 26719 0.4501 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:362 set_sentinel
0090ac93 23368 0.3936 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1509 ScanQueryForLocks
00762040 21120 0.3558 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1580 pgstat_end_function_usage
007eaf66 17927 0.3020 /home/digoal/rds_pg_9400/src/backend/tcop/postgres.c:3232 check_stack_depth
00701246 17608 0.2966 /home/digoal/rds_pg_9400/src/backend/nodes/read.c:108 pg_strtok
00966abd 16699 0.2813 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:486 FreeSnapshot
006d25c8 16280 0.2742 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:1130 list_free
006d1754 16189 0.2727 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:689 list_delete_first
006d2f40 16120 0.2715 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:420 bms_is_member
00966353 16055 0.2704 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:170 GetTransactionSnapshot
00874ddc 13159 0.2217 /home/digoal/rds_pg_9400/src/backend/utils/adt/name.c:249 namestrcmp
004fd303 13153 0.2216 /home/digoal/rds_pg_9400/src/backend/access/transam/xact.c:711 GetCurrentTransactionNestLevel
006d2a68 11610 0.1956 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:201 bms_free
00953c99 10056 0.1694 /home/digoal/rds_pg_9400/src/backend/utils/resowner/resowner.c:968 ResourceOwnerRememberPlanCacheRef
004fc25c 9293 0.1565 /home/digoal/rds_pg_9400/src/backend/access/transam/transam.c:301 TransactionIdPrecedes
007d7282 8417 0.1418 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:466 LWLockAcquire
005459af 6775 0.1141 /home/digoal/rds_pg_9400/src/backend/catalog/namespace.c:3434 recomputeNamespacePath
00909e3a 6641 0.1119 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:978 choose_custom_plan
004fd438 6545 0.1103 /home/digoal/rds_pg_9400/src/backend/access/transam/xact.c:800 CommandCounterIncrement
0085a8bc 6157 0.1037 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:306 int84ge
00953bcc 5868 0.0988 /home/digoal/rds_pg_9400/src/backend/utils/resowner/resowner.c:939 ResourceOwnerEnlargePlanCacheRefs
006b25f4 5804 0.0978 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:90 list_head
006b2e52 5684 0.0957 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:329 SPI_pop
007afd56 5681 0.0957 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1747 AtEOXact_Buffers
006b2e3d 5531 0.0932 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:322 SPI_push
00930ff1 5360 0.0903 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:824 hash_search_with_hash_value
0085a82c 5310 0.0894 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:279 int84lt
009688b0 5291 0.0891 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:1343 HistoricSnapshotActive
009328c3 4818 0.0812 /home/digoal/rds_pg_9400/src/backend/utils/init/miscinit.c:172 GetUserId
00904def 4604 0.0776 /home/digoal/rds_pg_9400/src/backend/utils/cache/catcache.c:1100 SearchCatCache
0095070c 4350 0.0733 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:626 MemoryContextAllocZeroAligned
0051ada6 4192 0.0706 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:7384 RecoveryInProgress
004fd287 4028 0.0679 /home/digoal/rds_pg_9400/src/backend/access/transam/xact.c:646 GetCurrentCommandId
0050e3d7 3117 0.0525 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:843 XLogInsert ------------------------------------ 单表最大开销位置 ，上面都是多余部分
00966fd9 2771 0.0467 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:694 SnapshotResetXmin
0094fe05 2635 0.0444 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:253 MemoryContextSetParent
0095035b 2349 0.0396 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:518 MemoryContextCreate
0068758e 2278 0.0384 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:4397 ExecInitExpr
00928ee7 2017 0.0340 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:106 fmgr_isbuiltin
007afe05 1806 0.0304 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1790 AtProcExit_Buffers
007ad768 1767 0.0298 /home/digoal/rds_pg_9400/src/backend/storage/buffer/../../../../src/include/storage/s_lock.h:219 tas
00462712 1551 0.0261 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:524 heap_getsysattr
0052cc56 1544 0.0260 /home/digoal/rds_pg_9400/src/backend/catalog/caql/catquery.c:283 cql1
006cb75b 1527 0.0257 /home/digoal/rds_pg_9400/src/backend/nodes/nodeFuncs.c:1595 expression_tree_walker
004a052a 1436 0.0242 /home/digoal/rds_pg_9400/src/backend/access/hash/hashfunc.c:306 hash_any
004d7572 1252 0.0211 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:492 _bt_first
004daec1 1152 0.0194 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtutils.c:744 _bt_preprocess_keys
007ccbda 1116 0.0188 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:695 LockAcquireExtended
0094e80f 1083 0.0182 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:615 AllocSetDelete
006a2f02 1080 0.0182 /home/digoal/rds_pg_9400/src/backend/executor/nodeModifyTable.c:1129 ExecModifyTable
00689d4b 1036 0.0175 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5283 ExecTargetList
006a1601 1028 0.0173 /home/digoal/rds_pg_9400/src/backend/executor/nodeModifyTable.c:171 ExecInsert
004bb5bb 997 0.0168 /home/digoal/rds_pg_9400/src/backend/access/heap/hio.c:219 RelationGetBufferForTuple
00930f97 952 0.0160 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:810 hash_search
006b5f73 919 0.0155 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:1966 _SPI_execute_plan
006d0855 906 0.0153 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:151 lappend
0092aa8f 859 0.0145 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:1052 DirectFunctionCall1Coll
006ff6da 858 0.0145 /home/digoal/rds_pg_9400/src/backend/nodes/readfuncs.c:1290 parseNodeString
0068b30a 846 0.0143 /home/digoal/rds_pg_9400/src/backend/executor/execTuples.c:450 ExecClearTuple
00917ea8 789 0.0133 /home/digoal/rds_pg_9400/src/backend/utils/cache/syscache.c:951 SearchSysCache
007d014f 780 0.0131 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:2449 FastPathUnGrantRelationLock
00745407 775 0.0131 /home/digoal/rds_pg_9400/src/backend/optimizer/util/clauses.c:2338 eval_const_expressions_mutator
0052cabd 764 0.0129 /home/digoal/rds_pg_9400/src/backend/catalog/caql/catquery.c:191 cqclr
00686803 760 0.0128 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:4027 ExecEvalFieldSelect
006ccc16 751 0.0127 /home/digoal/rds_pg_9400/src/backend/nodes/nodeFuncs.c:2113 expression_tree_mutator
0092b721 746 0.0126 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:1342 FunctionCall2Coll
007e1894 746 0.0126 /home/digoal/rds_pg_9400/src/backend/storage/page/../../../../src/include/storage/checksum_impl.h:140 pg_checksum_block
0046ab31 740 0.0125 /home/digoal/rds_pg_9400/src/backend/access/common/tupdesc.c:498 TupleDescInitEntry
007cfe21 737 0.0124 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:2412 FastPathGrantRelationLock
004dbc07 734 0.0124 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtutils.c:1375 _bt_checkkeys
006a3581 720 0.0121 /home/digoal/rds_pg_9400/src/backend/executor/nodeModifyTable.c:1361 ExecInitModifyTable
0067a001 716 0.0121 /home/digoal/rds_pg_9400/src/backend/executor/execMain.c:824 InitPlan
00930f64 694 0.0117 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:768 calc_bucket
004d6f8f 688 0.0116 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:388 _bt_compare
007cea9c 687 0.0116 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:1741 LockRelease
0052b8c2 684 0.0115 /home/digoal/rds_pg_9400/src/backend/catalog/caql/caqlanalyze.c:237 caql_basic_fn_all
004ab68f 680 0.0115 /home/digoal/rds_pg_9400/src/backend/access/heap/heapam.c:2054 heap_insert
00902845 676 0.0114 /home/digoal/rds_pg_9400/src/backend/utils/cache/catcache.c:178 CatalogCacheComputeHashValue
007af1a5 655 0.0110 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1128 PinBuffer
004a08cc 648 0.0109 /home/digoal/rds_pg_9400/src/backend/access/hash/hashfunc.c:511 hash_uint32
0068c81d 639 0.0108 /home/digoal/rds_pg_9400/src/backend/executor/execUtils.c:226 CreateExprContext
004610e4 616 0.0104 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:87 heap_compute_data_size
004c6eca 611 0.0103 /home/digoal/rds_pg_9400/src/backend/access/index/genam.c:315 systable_beginscan
XLogInsert占用0.0525%
阿里云AliCloudDB PostgreSQL 分区表功能性能比社区版提升100倍
2015-12-11
8871
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《
阿里云开发者社区用户服务协议》和
《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写
侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
简介：
相关实践学习
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7月前
|
关系型数据库
Serverless
分布式数据库
【公测】PolarDB PostgreSQL版Serverless功能免费使用！
【公测】PolarDB PostgreSQL版Serverless功能免费使用，公测于2024年3月28日开始，持续三个月，公测期间可以免费使用！
905
7
7
|
4月前
|
缓存
关系型数据库
数据库
|
2月前
|
人工智能
自然语言处理
关系型数据库
阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成
近日，阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成。
121
6
6
|
7月前
|
Cloud Native
关系型数据库
OLAP
云原生数据仓库产品使用合集之阿里云云原生数据仓库AnalyticDB PostgreSQL版的重分布时间主要取决的是什么
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能，并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景，为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述，包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
1399
1
1
|
3月前
|
缓存
关系型数据库
数据库
|
2月前
|
存储
关系型数据库
MySQL
四种数据库对比MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
四种数据库对比 MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
261
0
0
|
3月前
|
缓存
关系型数据库
数据库
|
7月前
|
关系型数据库
Serverless
分布式数据库
PolarDB PostgreSQL版Serverless功能上线公测啦，公测期间免费使用！
Serverless数据库能够使得数据库集群资源随客户业务负载动态弹性扩缩，将客户从复杂的业务资源评估和运维工作中解放出来。PolarDB PostgreSQL版 Serverless提供了CPU、内存、存储、网络资源的实时弹性能力，构建计算与存储分离架构下的 PolarDB PostgreSQL版产品新形态。
817
9
9
|
4月前
|
缓存
关系型数据库
数据库
|
4月前
|
关系型数据库
Java
数据库
热门文章
最新文章
1
德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
2
PostgreSQL 如何实现批量更新、删除、插入
3
MongoDB 存储引擎 WiredTiger 原理解析
4
PostgreSQL 与基友们的故事之 - Redis (无限缓存,实时标签...)
5
关于MongoDB Sharding，你应该知道的
6
PostgreSQL 金融行业高可用和容灾解决方案
7
鉴黄、视频、图片去重、图像搜索业务分析与实践
8
PostgreSQL 分库分表 插件之一 pg_shard
9
弱水三千,只取一瓢,当图像搜索遇见PostgreSQL(Haar wavelet)
10
PostgreSQL MySQL 数据类型映射
1
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
13
2
【赵渝强老师】PostgreSQL的数据文件
9
3
【赵渝强老师】PostgreSQL的物理存储结构
13
4
【赵渝强老师】PostgreSQL的段、区和块
82
5
【赵渝强老师】PostgreSQL中的模式
34
6
【赵渝强老师】PostgreSQL的表空间
17
7
Sybase数据恢复—Sybase数据库常见问题之数据库无法启动的恢复案例
19
8
【赵渝强老师】PostgreSQL的数据库
28
9
Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
18
10
【赵渝强老师】PostgreSQL的数据库集群
27