1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
postgres=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC;
-[ RECORD 1 ]------------------------------------------------------
query | update testmem2 set t7 = random()::text where t1 < ? and t1 > ?;
calls | 1
total_time | 14303.53
rows | 788223
hit_percent | 97.7162513440240383
-[ RECORD 2 ]------------------------------------------------------
query | delete from testmem3 where t1 < ? and t1 > ?;
calls | 1
total_time | 5256.305
rows | 788223
hit_percent | 97.4004941321803582
-[ RECORD 6 ]------------------------------------------------------
query | select * from testmem1 where t1 < ? and t1 > ?;
calls | 1
total_time | 2397.866
rows | 788223
hit_percent | 0.15039714245429336843
使用pgfincore后：通过查询表pg_stat_statments查看三种操作时间（重启机器后，重新建库，重新建表、插数据）：
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC;
-[ RECORD 1 ]------------------------------------------------------
query | update testmem2 set t7 = random()::text where t1 < ? and t1 > ?;
calls | 1
total_time | 6800.237
rows | 788223
hit_percent | 97.6908892215405358
-[ RECORD 2 ]------------------------------------------------------
query | delete from testmem3 where t1 < ? and t1 > ?;
calls | 1
total_time | 3295.312
rows | 788223
hit_percent | 97.3715873996294009
-[ RECORD 3 ]------------------------------------------------------
query | select * from testmem1 where t1 < ? and t1 > ?;
calls | 1
total_time | 891.002
rows | 788223
hit_percent | 0.000000000000000000000000
明显看出当执行select时速度提升明显，其他的时间提升并不明显，这是因为数据还需要进行IO操作，所以在这里速度提升应该仅仅是读入的时间节省掉了。
postgres=# select pgfadvise_willneed('testmem1');
-[ RECORD 1 ]------+-------------------------------------
pgfadvise_willneed | (base/13003/16384,4096,42554,279311)
postgres=# select * from pgfincore('testmem1');
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42554
pages_mem | 42554
group_mem | 1
os_pages_free | 279317
databit |
postgres=# select count(*) from testmem1 where t1 < 99999 and t1 > 12;
-[ RECORD 1 ]
count | 99986
postgres=# insert into testmem1 select generate_series(1,9999), 'wangshuo'||generate_series(1,9999)::text||random()::text, generate_series(1,9999)::text||'sure',generate_series(1,9999), random()::text,random()::text,random()::text,'liuyuanyuan', generate_series(1,9999);
INSERT 0 9999
postgres=# select * from pgfincore('testmem1');-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 42980
pages_mem | 42980
group_mem | 1
os_pages_free | 277433
databit |
postgres=# select pg_relation_size('testmem1');
-[ RECORD 1 ]----+----------
pg_relation_size | 176046080
postgres=# select 42980*4096;
-[ RECORD 1 ]-------
?column? | 176046080
postgres=# insert into testmem1 select generate_series(1,999999), 'wangshuo'||generate_series(1,999999)::text||random()::text, generate_series(1,999999)::text||'sure',generate_series(1,999999), random()::text,random()::text,random()::text,'liuyuanyuan', generate_series(1,999999);
INSERT 0 999999
postgres=# select * from pgfincore('testmem1');
-[ RECORD 1 ]-+-----------------
relpath | base/13003/16384
segment | 0
os_page_size | 4096
rel_os_pages | 85532
pages_mem | 6188
group_mem | 22
os_pages_free | 403050
databit |
postgres=#
上面这个实验验证了willneed会将小部分新加入的数据加载到cache中，能够快速的定位查找，但是当插入数据量较大时，就不会及时的写入到cache中了。
总结：pgfincore目前适用于数据量非常大、更新不频繁、更新量较小的表去加载到缓存中，有助于提高应用效率。
插件函数介绍：
这里会对函数进行介绍，并对所有函数进行操作实验。
1、pgsysconf：
这个函数输出OS block的大小（os_page_size），OS中剩余的page数(os_pages_free)和OS拥有的page总数(os_total_pages)。