标签
PostgreSQL , PostGIS , preload , shared_preload_libraries , session_preload_libraries
背景
PostgreSQL是一个可扩展的数据库，如果你的扩展是C扩展，代码在动态库中。那么在使用时需要加载这个动态库来实现一些功能例如创建类型，创建函数，创建操作符等。
PostgreSQL允许多种加载动态库的方法，例如数据库启动时加载，会话连接（启动backend_process）时加载，或者在会话中调用到对应的动态库时再加载。
那么到底选择哪种加载方法呢？
1、数据库启动时加载，所有会话共享，对于常用的动态库（每个会话都会调用到的），建议使用这种方式加载。
2、会话启动或即用即加载的方法，不共享，并且每次加载需要一定的时间，每个会话需要本地存储一份库（更消耗内存一点）。对于不常用的动态库，可以使用这种方法。
PostGIS是PG的一个空间数据库扩展，同样也面临以上的选择，到底怎么选呢？根据上面的建议来。
下面给出一个例子
预加载带来的好处
如果我们设置了预加载，那么在数据库启动时，会自动加载以下动态库
postgres=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------------------------------------------------------
pg_stat_statements,postgis-2.4.so,postgis_topology-2.4.so
(1 row)
第一次执行的时间，与第二次执行的时间相仿。没有太大差异。
执行PostGIS的空间函数或类型等，不需要加载动态库。
postgres=# explain analyze select * from car where rest_sites=sites order by pos<->gen_pos() for update limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.41..1.45 rows=1 width=94) (actual time=0.276..0.276 rows=1 loops=1)
-> LockRows (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.274..0.274 rows=1 loops=1)
-> Index Scan using idx_car_pos_2 on car (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.250..0.250 rows=1 loops=1)
Order By: (pos <-> '0101000020E610000032569BFF57EE5C4062670A9DD7203940'::geometry)
Filter: (rest_sites = sites)
Planning time: 1.365 ms
Execution time: 0.340 ms
(7 rows)
postgres=# explain analyze select * from car where rest_sites=sites order by pos<->gen_pos() for update limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.41..1.45 rows=1 width=94) (actual time=0.162..0.162 rows=1 loops=1)
-> LockRows (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.161..0.161 rows=1 loops=1)
-> Index Scan using idx_car_pos_2 on car (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.152..0.152 rows=1 loops=1)
Order By: (pos <-> '0101000020E61000003B71395E81905C404A404CC285383B40'::geometry)
Filter: (rest_sites = sites)
Planning time: 0.314 ms
Execution time: 0.189 ms
(7 rows)
而如果我们不使用postgis预加载，那么性能又会怎么样呢？
第一次和第二次相差很大，需要加载动态库。
postgres=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------------------------------------------------------
pg_stat_statements
(1 row)
postgres=# explain analyze select * from car where rest_sites=sites order by pos<->gen_pos() for update limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.41..1.45 rows=1 width=94) (actual time=0.271..0.271 rows=1 loops=1)
-> LockRows (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.268..0.268 rows=1 loops=1)
-> Index Scan using idx_car_pos_2 on car (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.241..0.241 rows=1 loops=1)
Order By: (pos <-> '0101000020E6100000E61E12BEF7775D404DF910548DEA3940'::geometry)
Filter: (rest_sites = sites)
Planning time: 52.728 ms
Execution time: 0.346 ms
(7 rows)
postgres=# explain analyze select * from car where rest_sites=sites order by pos<->gen_pos() for update limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.41..1.45 rows=1 width=94) (actual time=0.174..0.174 rows=1 loops=1)
-> LockRows (cost=0.41..50620.34 rows=48776 width=94) (actual time=0.173..0.173 rows=1 loops=1)
-> Index Scan using idx_car_pos_2 on car (cost=0.41..50132.58 rows=48776 width=94) (actual time=0.161..0.161 rows=1 loops=1)
Order By: (pos <-> '0101000020E6100000F986C267EBBA5C4012FB04508CEC3940'::geometry)
Filter: (rest_sites = sites)
Planning time: 0.266 ms
Execution time: 0.199 ms
(7 rows)
参考
为了提高postgis插件的装载速度，应该将so文件配置为数据库启动时自动加载。
使用数据库启动时自动加载，还有一个好处，内存使用量也大大减少。
对于短连接业务，提升特别明显。因为短连接用户，每次发起新的连接后，第一次查询PLAN都会更慢，需要加载lib库。