本文共 7309 字，大约阅读时间需要 24 分钟。
PostgreSQL的全文检索接口是开放API的，所以中文分词的插件也非常多，例如常用的scws分词插件，还有结巴分词的插件。
但是你在使用结巴分词插件的时候，有没有遇到这样的问题。
每个会话，第一次查询会比较慢，接下来的查询就快了。
例如
psql (9.5.3)Type "help" for help.postgres=# \timingTiming is on.postgres=# select * from ts_debug('jiebacfg', '子远e5a1cbb8'); alias | description | token | dictionaries | dictionary | lexemes -------+-------------+----------+--------------+------------+------------ n | noun | 子远 | {jieba_stem} | jieba_stem | {子远} n | noun | e5a1cbb8 | {jieba_stem} | jieba_stem | {e5a1cbb8}(2 rows)Time: 863.777 mspostgres=# select * from ts_debug('jiebacfg', '子远e5a1cbb8'); alias | description | token | dictionaries | dictionary | lexemes -------+-------------+----------+--------------+------------+------------ n | noun | 子远 | {jieba_stem} | jieba_stem | {子远} n | noun | e5a1cbb8 | {jieba_stem} | jieba_stem | {e5a1cbb8}(2 rows)Time: 1.342 ms 第一次加载pg_jieba模块时，需要调用加载字典的动作。
/* * Module load callback */void_PG_init(void){ if (jieba_ctx) return; { const char* dict_path = jieba_get_tsearch_config_filename(DICT_PATH, EXT); const char* hmm_path = jieba_get_tsearch_config_filename(HMM_PATH, EXT); const char* user_dict_path = jieba_get_tsearch_config_filename(USER_DICT, EXT); /* init will take a few seconds to load dicts. */ jieba_ctx = Jieba_New(dict_path, hmm_path, user_dict_path); }} 如果pg_jieba.so没有放在shared_preload_libraries或session_preload_libraries中，那么每个会话启动时，都需要load pg_jieba.so，从而导致了第一次查询速度非常慢。
例子
psql (9.5.3)Type "help" for help.postgres=# \timingTiming is on.postgres=# load 'pg_jieba';LOADTime: 857.098 mspostgres=# select * from ts_debug('jiebacfg', '子远e5a1cbb8'); alias | description | token | dictionaries | dictionary | lexemes -------+-------------+----------+--------------+------------+------------ n | noun | 子远 | {jieba_stem} | jieba_stem | {子远} n | noun | e5a1cbb8 | {jieba_stem} | jieba_stem | {e5a1cbb8}(2 rows)Time: 4.952 ms 知道问题在哪里了，就好解决。
可以将pg_jieba.so配置在shared_preload_libraries或session_preload_libraries中，就能解决以上问题。vi postgresql.conf shared_preload_libraries = 'pg_jieba.so'orsession_preload_libraries = 'pg_jieba.so'
重启数据库
pg_ctl restart -m fast
.1. 未配置
shared_preload_libraries = 'pg_jieba.so'orsession_preload_libraries = 'pg_jieba.so'
session A :
psql (9.5.3)Type "help" for help.postgres=# select pg_backend_pid(); pg_backend_pid ---------------- 12254(1 row)
session B :
psql (9.5.3)Type "help" for help.postgres=# select pg_backend_pid(); pg_backend_pid ---------------- 12261(1 row)
backend process内存使用情况
# smem|grep 12261 PID User Command Swap USS PSS RSS12261 digoal postgres: postgres postgres 0 812 1677 3780 # smem|grep 12254 PID User Command Swap USS PSS RSS12254 digoal postgres: postgres postgres 0 812 1682 3788
在未使用pg_jieba时，通过/proc/12261/smaps 也可以看到没有加载pg_jieba.so。
分别执行加载pg_jieba的模块或执行pg_jieba词法解析后
postgres=# load 'pg_jieba';LOADTime: 872.095 ms
内存飙升
# smem|grep 12254 PID User Command Swap USS PSS RSS12254 digoal postgres: postgres postgres 0 114404 116326 120272 # smem|grep 12261 PID User Command Swap USS PSS RSS12261 digoal postgres: postgres postgres 0 114404 116321 120260
.1. 已配置
shared_preload_libraries = 'pg_jieba.so'orsession_preload_libraries = 'pg_jieba.so'
分别执行QUERY后，backend process进程内存没有独占加载pg_jieba.so的内存，算在共享内存中。
[root@iZ28tqoemgtZ ~]# smem|grep 12410 PID User Command Swap USS PSS RSS12410 digoal postgres: postgres postgres 0 3696 17754 118988 [root@iZ28tqoemgtZ ~]# smem|grep 12412 PID User Command Swap USS PSS RSS12412 digoal postgres: postgres postgres 0 3124 17115 118296
通过/proc/12410/smaps 也可以看到，只是用到pg_jieba.so时算了少量的Pss。
7fb68fe40000-7fb68fe55000 r-xp 00000000 fd:01 1052111 /home/digoal/pgsql9.5/lib/pg_jieba.soSize: 84 kBRss: 48 kBPss: 16 kBShared_Clean: 48 kBShared_Dirty: 0 kBPrivate_Clean: 0 kBPrivate_Dirty: 0 kBReferenced: 48 kBAnonymous: 0 kBAnonHugePages: 0 kBSwap: 0 kBKernelPageSize: 4 kBMMUPageSize: 4 kBLocked: 0 kBVmFlags: rd ex mr mw me 7fb68fe55000-7fb690054000 ---p 00015000 fd:01 1052111 /home/digoal/pgsql9.5/lib/pg_jieba.soSize: 2044 kBRss: 0 kBPss: 0 kBShared_Clean: 0 kBShared_Dirty: 0 kBPrivate_Clean: 0 kBPrivate_Dirty: 0 kBReferenced: 0 kBAnonymous: 0 kBAnonHugePages: 0 kBSwap: 0 kBKernelPageSize: 4 kBMMUPageSize: 4 kBLocked: 0 kBVmFlags: mr mw me 7fb690054000-7fb690055000 r--p 00014000 fd:01 1052111 /home/digoal/pgsql9.5/lib/pg_jieba.soSize: 4 kBRss: 4 kBPss: 0 kBShared_Clean: 0 kBShared_Dirty: 4 kBPrivate_Clean: 0 kBPrivate_Dirty: 0 kBReferenced: 4 kBAnonymous: 4 kBAnonHugePages: 0 kBSwap: 0 kBKernelPageSize: 4 kBMMUPageSize: 4 kBLocked: 0 kBVmFlags: rd mr mw me ac 7fb690055000-7fb690056000 rw-p 00015000 fd:01 1052111 /home/digoal/pgsql9.5/lib/pg_jieba.so...
postgres=# alter function to_tsvector(regconfig,text) volatile;ALTER FUNCTIONpostgres=# explain (buffers,timing,costs,verbose,analyze) select to_tsvector('jiebacfg','中华人民共和国万岁，如何加快PostgreSQL结巴分词加载速度') from generate_series(1,1000000); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series (cost=0.00..260.00 rows=1000 width=0) (actual time=100.054..13943.166 rows=1000000 loops=1) Output: to_tsvector('jiebacfg'::regconfig, '中华人民共和国万岁，如何加快PostgreSQL结巴分词加载速度'::text) Function Call: generate_series(1, 1000000) Buffers: temp read=1710 written=1709 Planning time: 0.040 ms Execution time: 14175.527 ms(6 rows)Time: 14176.044 mspostgres=# select to_tsvector('jiebacfg','中华人民共和国万岁，如何加快PostgreSQL结巴分词加载速度'); to_tsvector ------------------------------------------------------------------------------------------ 'postgresql':6 '万岁':2 '中华人民共和国':1 '分词':8 '加快':5 '加载':9 '结巴':7 '速度':10(1 row)Time: 0.522 mspostgres=# select 8*1000000/14.175527; ?column? --------------------- 564352.916120860974(1 row)Time: 0.743 ms 祝大家玩得开心，欢迎随时来 阿里云促膝长谈 业务需求 ，恭候光临。
阿里云的小伙伴们加油，努力做 最贴地气的云数据库 。
转载地址：http://uhaex.baihongyu.com/