本文共 35967 字，大约阅读时间需要 119 分钟。
阿里云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 postgrespsql (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 TABLEpostgres=# \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 | digoalpostgres=# \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: testpostgres=# insert into test select generate_series (19990000,21000000);INSERT 0 1010001Time: 4850.703 mspostgres=# insert into test select generate_series (19919990000,19921000000);INSERT 0 1010001Time: 4774.363 ms在主表创建索引，会自动在分区表创建对应的索引。 postgres=# create index idx_test_id on test(id);CREATE INDEXTime: 2003.232 mspostgres=# \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: testpostgres=# select * from test where id=19919990000; id | info | crt_time -------------+------+---------- 19919990000 | | (1 row)Time: 93.481 mspostgres=# select * from test where id=19990000; id | info | crt_time ----------+------+---------- 19990000 | | (1 row)Time: 89.114 mspostgres=# 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 mspostgres=# 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 TABLETime: 12.851 mspostgres=# create index idx_test1_id on test1(id);CREATE INDEXTime: 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 0Time: 387710.425 mspostgres=# insert into test1 select generate_series (19919990000,19921000000);INSERT 0 0Time: 764688.154 ms测试查询性能postgres=# select * from test1 where id=19919990000; id | info | crt_time -------------+------+---------- 19919990000 | | (1 row)Time: 98.881 mspostgres=# select * from test1 where id=19990000; id | info | crt_time ----------+------+---------- 19990000 | | (1 row)Time: 96.142 mspostgres=# 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 mspostgres=# 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 $$declarebegin 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 FUNCTIONTime: 55.847 ms建立触发器postgres=# create trigger tg1 before insert on test2 for each row execute procedure tg1();CREATE TRIGGERTime: 10.472 ms测试插入性能postgres=# insert into test2 select generate_series (19990000,21000000);INSERT 0 0Time: 132572.550 mspostgres=# insert into test2 select generate_series (19919990000,19921000000);INSERT 0 0Time: 5686336.622 ms测试查询性能postgres=# select * from test2 where id=19919990000; id | info | crt_time -------------+------+---------- 19919990000 | | (1 row)Time: 83.109 mspostgres=# select * from test2 where id=19990000; id | info | crt_time ----------+------+---------- 19990000 | | (1 row)Time: 79.735 mspostgres=# 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 mspostgres=# 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 1010001Time: 9012.904 mspostgres=# 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.gztar -zxvf oprofile-1.1.0.tar.gzcd oprofile-1.1.0yum install -y binutils-devel./configuremakemake install1.1. AliCloudDB for PostgreSQL#operf --system-wide --lazy-conversionpostgres=# insert into test select generate_series (19999000000,19999100000);INSERT 0 100001Time: 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 100000vma samples % linenr info symbol name0050e3d7 3501 15.5600 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:843 XLogInsert004d6f8f 1695 7.5333 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:388 _bt_compare007d6e1c 779 3.4622 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/../../../../src/include/storage/s_lock.h:219 tas004d6d48 520 2.3111 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:283 _bt_binsrch00930ff1 413 1.8356 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:824 hash_search_with_hash_value007e026b 409 1.8178 /home/digoal/rds_pg_9400/src/backend/storage/page/bufpage.c:182 PageAddItem007d7c22 373 1.6578 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:1119 LWLockRelease004d66ec 367 1.6311 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:60 _bt_search004c945a 352 1.5644 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtcompare.c:130 btint8cmp004ca949 321 1.4267 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtinsert.c:713 _bt_insertonpg0092b721 305 1.3556 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:1342 FunctionCall2Coll007ad768 302 1.3422 /home/digoal/rds_pg_9400/src/backend/storage/buffer/../../../../src/include/storage/s_lock.h:219 tas007d72e3 292 1.2978 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:484 LWLockAcquireCommon0094f01f 289 1.2844 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:936 AllocSetFree0094e950 288 1.2800 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:654 AllocSetAlloc00689d4b 276 1.2267 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5283 ExecTargetList0050fe98 272 1.2089 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:1881 XLogBytePosToRecPtr007b0081 271 1.2044 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1893 BufferGetBlockNumber004ab68f 255 1.1333 /home/digoal/rds_pg_9400/src/backend/access/heap/heapam.c:2054 heap_insert004d6acb 254 1.1289 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:179 _bt_moveright004a052a 253 1.1244 /home/digoal/rds_pg_9400/src/backend/access/hash/hashfunc.c:306 hash_any006823a6 251 1.1156 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1666 ExecMakeFunctionResult009509e9 246 1.0933 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:690 pfree007b19fc 241 1.0711 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:2788 LockBuffer最大的开销在XLogInsert，和插入单表的开销基本一致。相比规则和触发器少了很多开销。1.2. 社区版，基于规则#operf --system-wide --lazy-conversionpostgres=# insert into test1 select generate_series (19999000000,19999100000);INSERT 0 0Time: 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 whenthe initial sample rate is too high, causing an excessive number ofinterrupts. Decrease the sampling frequency. Check the directory/home/oprofile_data/samples/current/stats/throttledfor 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 100000vma samples % linenr info symbol name00682b07 121678 13.3298 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1977 ExecMakeFunctionResultNoSets00689d4b 72127 7.9015 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5283 ExecTargetList00689beb 63456 6.9516 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5169 ExecQual0085c4db 55855 6.1189 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:1441 generate_series_step_int800680455 46819 5.1290 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:665 ExecEvalScalarVarFast006823a6 43900 4.8092 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1666 ExecMakeFunctionResult0085a79c 42942 4.7043 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:249 int8ge00463c7d 40070 4.3896 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:1156 slot_getattr0085a70c 36748 4.0257 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:222 int8lt00680df4 35229 3.8593 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1082 ExecEvalConst0068a5d3 32706 3.5829 /home/digoal/rds_pg_9400/src/backend/executor/execScan.c:111 ExecScan0068b30a 28203 3.0896 /home/digoal/rds_pg_9400/src/backend/executor/execTuples.c:450 ExecClearTuple0068a0e2 27186 2.9782 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5425 ExecProject0067f870 25356 2.7777 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:90 list_head0067f84c 23400 2.5635 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/utils/palloc.h:92 MemoryContextSwitchTo00761e17 22180 2.4298 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1508 pgstat_init_function_usage007eaf66 21910 2.4002 /home/digoal/rds_pg_9400/src/backend/tcop/postgres.c:3232 check_stack_depth00762040 21459 2.3508 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1580 pgstat_end_function_usage0068b426 19698 2.1579 /home/digoal/rds_pg_9400/src/backend/executor/execTuples.c:499 ExecStoreVirtualTuple0067f183 19529 2.1394 /home/digoal/rds_pg_9400/src/backend/executor/execProcnode.c:356 ExecProcNode0068a448 18984 2.0797 /home/digoal/rds_pg_9400/src/backend/executor/execScan.c:40 ExecScanFetch0092e0f7 17211 1.8855 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/funcapi.c:130 per_MultiFuncCall0094fb5d 13472 1.4758 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:131 MemoryContextReset006a5f2c 9489 1.0395 /home/digoal/rds_pg_9400/src/backend/executor/nodeResult.c:68 ExecResult006aba50 9145 1.0018 /home/digoal/rds_pg_9400/src/backend/executor/nodeSubqueryscan.c:47 SubqueryNext0085a8bc 4853 0.5316 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:306 int84ge0085a82c 4654 0.5098 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:279 int84lt0050e3d7 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_int8004d6f8f 2033 0.2227 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:388 _bt_compare007d6e1c 1036 0.1135 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/../../../../src/include/storage/s_lock.h:219 tas0094e950 922 0.1010 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:654 AllocSetAllocXLogInsert只占了0.3374%，大量的开销耗费在executor1.3. 社区版，基于触发器#operf --system-wide --lazy-conversionpostgres=# insert into test2 select generate_series (19999000000,19999100000);INSERT 0 0Time: 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 whenthe initial sample rate is too high, causing an excessive number ofinterrupts. Decrease the sampling frequency. Check the directory/home/oprofile_data/samples/current/stats/throttledfor 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 100000vma samples % linenr info symbol name0094e950 481019 8.1028 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:654 AllocSetAlloc00680f09 277442 4.6735 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1134 ExecEvalParamExtern00682b07 270488 4.5564 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1977 ExecMakeFunctionResultNoSets009509e9 256627 4.3229 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:690 pfree0094f01f 252116 4.2469 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:936 AllocSetFree007c0ba0 246408 4.1508 /home/digoal/rds_pg_9400/src/backend/storage/ipc/procarray.c:1359 GetSnapshotData0094e308 231822 3.9051 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:318 AllocSetFreeIndex006840b9 210097 3.5391 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:2804 ExecEvalAnd0094e658 174083 2.9324 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:553 AllocSetReset0067f870 167061 2.8142 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:90 list_head006b5803 150638 2.5375 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:1654 SPI_plan_get_cached_plan00908c9c 142255 2.3963 /home/digoal/rds_pg_9400/src/backend/utils/cache/../../../../src/include/nodes/pg_list.h:90 list_head0094f788 125489 2.1139 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:1292 AllocSetCheck00461c7b 120680 2.0329 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:326 nocachegetattr00761e17 100771 1.6975 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1508 pgstat_init_function_usage006d0599 100441 1.6919 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:57 check_list_invariants007d6e1c 87467 1.4734 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/../../../../src/include/storage/s_lock.h:219 tas005452d9 85166 1.4346 /home/digoal/rds_pg_9400/src/backend/catalog/namespace.c:3146 OverrideSearchPathMatchesCurrent00680df4 84555 1.4243 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:1082 ExecEvalConst009508b3 83698 1.4099 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:665 palloc0006d3891 83184 1.4012 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:812 bms_first_member0090aa27 79325 1.3362 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1412 AcquireExecutorLocks006d263e 74906 1.2618 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:1157 list_copy0090abc8 69602 1.1724 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1482 AcquirePlannerLocks00909a7b 67500 1.1370 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:771 CheckCachedPlan00966d16 65457 1.1026 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:566 PopActiveSnapshot009507f3 63620 1.0717 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:646 palloc0085a79c 59801 1.0074 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:249 int8ge006e174c 58969 0.9933 /home/digoal/rds_pg_9400/src/backend/nodes/../../../src/include/nodes/pg_list.h:90 list_head006b3d44 58768 0.9900 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:824 SPI_fnumber005410a0 55070 0.9277 /home/digoal/rds_pg_9400/src/backend/catalog/../../../src/include/utils/palloc.h:92 MemoryContextSwitchTo0094e395 54245 0.9138 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:352 wipe_mem00953d23 52013 0.8762 /home/digoal/rds_pg_9400/src/backend/utils/resowner/resowner.c:979 ResourceOwnerForgetPlanCacheRef006eb9cf 51246 0.8632 /home/digoal/rds_pg_9400/src/backend/nodes/equalfuncs.c:2546 equal0094e3dd 50881 0.8571 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:372 sentinel_ok00950530 49418 0.8324 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:574 MemoryContextAlloc0090a324 49256 0.8297 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1213 ReleaseCachedPlan006d335e 49138 0.8277 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:590 bms_is_empty00966932 48470 0.8165 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:431 CopySnapshot009095f3 46243 0.7790 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:546 RevalidateCachedQuery00545190 44756 0.7539 /home/digoal/rds_pg_9400/src/backend/catalog/namespace.c:3094 GetOverrideSearchPath006d2854 44481 0.7493 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:111 bms_copy0085a70c 44293 0.7461 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:222 int8lt0090a00a 40777 0.6869 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1095 GetCachedPlan006b2614 39817 0.6707 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:102 list_length006b42b6 39070 0.6581 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:898 SPI_getbinval006b47a3 38320 0.6455 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:954 SPI_gettypeid0094fb5d 38250 0.6443 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:131 MemoryContextReset006d0752 37950 0.6393 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:86 new_list006d2548 37676 0.6347 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:1100 list_free_private006eb665 35464 0.5974 /home/digoal/rds_pg_9400/src/backend/nodes/equalfuncs.c:2457 _equalList007d72e3 32177 0.5420 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:484 LWLockAcquireCommon00966b65 31349 0.5281 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:504 PushActiveSnapshot006d1381 31257 0.5265 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:551 list_delete_cell005410c4 30352 0.5113 /home/digoal/rds_pg_9400/src/backend/catalog/../../../src/include/nodes/pg_list.h:90 list_head007d7c22 29044 0.4892 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:1119 LWLockRelease006d04c0 28762 0.4845 /home/digoal/rds_pg_9400/src/backend/nodes/../../../src/include/nodes/pg_list.h:90 list_head0094e3bc 26719 0.4501 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:362 set_sentinel0090ac93 23368 0.3936 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:1509 ScanQueryForLocks00762040 21120 0.3558 /home/digoal/rds_pg_9400/src/backend/postmaster/pgstat.c:1580 pgstat_end_function_usage007eaf66 17927 0.3020 /home/digoal/rds_pg_9400/src/backend/tcop/postgres.c:3232 check_stack_depth00701246 17608 0.2966 /home/digoal/rds_pg_9400/src/backend/nodes/read.c:108 pg_strtok00966abd 16699 0.2813 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:486 FreeSnapshot006d25c8 16280 0.2742 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:1130 list_free006d1754 16189 0.2727 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:689 list_delete_first006d2f40 16120 0.2715 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:420 bms_is_member00966353 16055 0.2704 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:170 GetTransactionSnapshot00874ddc 13159 0.2217 /home/digoal/rds_pg_9400/src/backend/utils/adt/name.c:249 namestrcmp004fd303 13153 0.2216 /home/digoal/rds_pg_9400/src/backend/access/transam/xact.c:711 GetCurrentTransactionNestLevel006d2a68 11610 0.1956 /home/digoal/rds_pg_9400/src/backend/nodes/bitmapset.c:201 bms_free00953c99 10056 0.1694 /home/digoal/rds_pg_9400/src/backend/utils/resowner/resowner.c:968 ResourceOwnerRememberPlanCacheRef004fc25c 9293 0.1565 /home/digoal/rds_pg_9400/src/backend/access/transam/transam.c:301 TransactionIdPrecedes007d7282 8417 0.1418 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lwlock.c:466 LWLockAcquire005459af 6775 0.1141 /home/digoal/rds_pg_9400/src/backend/catalog/namespace.c:3434 recomputeNamespacePath00909e3a 6641 0.1119 /home/digoal/rds_pg_9400/src/backend/utils/cache/plancache.c:978 choose_custom_plan004fd438 6545 0.1103 /home/digoal/rds_pg_9400/src/backend/access/transam/xact.c:800 CommandCounterIncrement0085a8bc 6157 0.1037 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:306 int84ge00953bcc 5868 0.0988 /home/digoal/rds_pg_9400/src/backend/utils/resowner/resowner.c:939 ResourceOwnerEnlargePlanCacheRefs006b25f4 5804 0.0978 /home/digoal/rds_pg_9400/src/backend/executor/../../../src/include/nodes/pg_list.h:90 list_head006b2e52 5684 0.0957 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:329 SPI_pop007afd56 5681 0.0957 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1747 AtEOXact_Buffers006b2e3d 5531 0.0932 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:322 SPI_push00930ff1 5360 0.0903 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:824 hash_search_with_hash_value0085a82c 5310 0.0894 /home/digoal/rds_pg_9400/src/backend/utils/adt/int8.c:279 int84lt009688b0 5291 0.0891 /home/digoal/rds_pg_9400/src/backend/utils/time/snapmgr.c:1343 HistoricSnapshotActive009328c3 4818 0.0812 /home/digoal/rds_pg_9400/src/backend/utils/init/miscinit.c:172 GetUserId00904def 4604 0.0776 /home/digoal/rds_pg_9400/src/backend/utils/cache/catcache.c:1100 SearchCatCache0095070c 4350 0.0733 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:626 MemoryContextAllocZeroAligned0051ada6 4192 0.0706 /home/digoal/rds_pg_9400/src/backend/access/transam/xlog.c:7384 RecoveryInProgress004fd287 4028 0.0679 /home/digoal/rds_pg_9400/src/backend/access/transam/xact.c:646 GetCurrentCommandId0050e3d7 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 SnapshotResetXmin0094fe05 2635 0.0444 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:253 MemoryContextSetParent0095035b 2349 0.0396 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/mcxt.c:518 MemoryContextCreate0068758e 2278 0.0384 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:4397 ExecInitExpr00928ee7 2017 0.0340 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:106 fmgr_isbuiltin007afe05 1806 0.0304 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1790 AtProcExit_Buffers007ad768 1767 0.0298 /home/digoal/rds_pg_9400/src/backend/storage/buffer/../../../../src/include/storage/s_lock.h:219 tas00462712 1551 0.0261 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:524 heap_getsysattr0052cc56 1544 0.0260 /home/digoal/rds_pg_9400/src/backend/catalog/caql/catquery.c:283 cql1006cb75b 1527 0.0257 /home/digoal/rds_pg_9400/src/backend/nodes/nodeFuncs.c:1595 expression_tree_walker004a052a 1436 0.0242 /home/digoal/rds_pg_9400/src/backend/access/hash/hashfunc.c:306 hash_any004d7572 1252 0.0211 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:492 _bt_first004daec1 1152 0.0194 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtutils.c:744 _bt_preprocess_keys007ccbda 1116 0.0188 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:695 LockAcquireExtended0094e80f 1083 0.0182 /home/digoal/rds_pg_9400/src/backend/utils/mmgr/aset.c:615 AllocSetDelete006a2f02 1080 0.0182 /home/digoal/rds_pg_9400/src/backend/executor/nodeModifyTable.c:1129 ExecModifyTable00689d4b 1036 0.0175 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:5283 ExecTargetList006a1601 1028 0.0173 /home/digoal/rds_pg_9400/src/backend/executor/nodeModifyTable.c:171 ExecInsert004bb5bb 997 0.0168 /home/digoal/rds_pg_9400/src/backend/access/heap/hio.c:219 RelationGetBufferForTuple00930f97 952 0.0160 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:810 hash_search006b5f73 919 0.0155 /home/digoal/rds_pg_9400/src/backend/executor/spi.c:1966 _SPI_execute_plan006d0855 906 0.0153 /home/digoal/rds_pg_9400/src/backend/nodes/list.c:151 lappend0092aa8f 859 0.0145 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:1052 DirectFunctionCall1Coll006ff6da 858 0.0145 /home/digoal/rds_pg_9400/src/backend/nodes/readfuncs.c:1290 parseNodeString0068b30a 846 0.0143 /home/digoal/rds_pg_9400/src/backend/executor/execTuples.c:450 ExecClearTuple00917ea8 789 0.0133 /home/digoal/rds_pg_9400/src/backend/utils/cache/syscache.c:951 SearchSysCache007d014f 780 0.0131 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:2449 FastPathUnGrantRelationLock00745407 775 0.0131 /home/digoal/rds_pg_9400/src/backend/optimizer/util/clauses.c:2338 eval_const_expressions_mutator0052cabd 764 0.0129 /home/digoal/rds_pg_9400/src/backend/catalog/caql/catquery.c:191 cqclr00686803 760 0.0128 /home/digoal/rds_pg_9400/src/backend/executor/execQual.c:4027 ExecEvalFieldSelect006ccc16 751 0.0127 /home/digoal/rds_pg_9400/src/backend/nodes/nodeFuncs.c:2113 expression_tree_mutator0092b721 746 0.0126 /home/digoal/rds_pg_9400/src/backend/utils/fmgr/fmgr.c:1342 FunctionCall2Coll007e1894 746 0.0126 /home/digoal/rds_pg_9400/src/backend/storage/page/../../../../src/include/storage/checksum_impl.h:140 pg_checksum_block0046ab31 740 0.0125 /home/digoal/rds_pg_9400/src/backend/access/common/tupdesc.c:498 TupleDescInitEntry007cfe21 737 0.0124 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:2412 FastPathGrantRelationLock004dbc07 734 0.0124 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtutils.c:1375 _bt_checkkeys006a3581 720 0.0121 /home/digoal/rds_pg_9400/src/backend/executor/nodeModifyTable.c:1361 ExecInitModifyTable0067a001 716 0.0121 /home/digoal/rds_pg_9400/src/backend/executor/execMain.c:824 InitPlan00930f64 694 0.0117 /home/digoal/rds_pg_9400/src/backend/utils/hash/dynahash.c:768 calc_bucket004d6f8f 688 0.0116 /home/digoal/rds_pg_9400/src/backend/access/nbtree/nbtsearch.c:388 _bt_compare007cea9c 687 0.0116 /home/digoal/rds_pg_9400/src/backend/storage/lmgr/lock.c:1741 LockRelease0052b8c2 684 0.0115 /home/digoal/rds_pg_9400/src/backend/catalog/caql/caqlanalyze.c:237 caql_basic_fn_all004ab68f 680 0.0115 /home/digoal/rds_pg_9400/src/backend/access/heap/heapam.c:2054 heap_insert00902845 676 0.0114 /home/digoal/rds_pg_9400/src/backend/utils/cache/catcache.c:178 CatalogCacheComputeHashValue007af1a5 655 0.0110 /home/digoal/rds_pg_9400/src/backend/storage/buffer/bufmgr.c:1128 PinBuffer004a08cc 648 0.0109 /home/digoal/rds_pg_9400/src/backend/access/hash/hashfunc.c:511 hash_uint320068c81d 639 0.0108 /home/digoal/rds_pg_9400/src/backend/executor/execUtils.c:226 CreateExprContext004610e4 616 0.0104 /home/digoal/rds_pg_9400/src/backend/access/common/heaptuple.c:87 heap_compute_data_size004c6eca 611 0.0103 /home/digoal/rds_pg_9400/src/backend/access/index/genam.c:315 systable_beginscanXLogInsert占用0.0525%
转载地址：http://bpzma.baihongyu.com/