阿里云AliCloudDB PostgreSQL 分区表功能性能比社区版提升100倍

阿里云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%