PostgreSQL用游标优化的一个例子

一位PG社区的朋友提到的一个应用场景&＃xff0c;目前遇到性能问题。
数据结构大概是这样的&＃xff0c;包含一个主键&＃xff0c;一个数组&＃xff0c;一个时间&＃xff0c;其他字段。
请求分析&＃xff1a;
有检索需求&＃xff0c;比较频繁。查找数组中包含某些元素的记录&＃xff0c;并按时间排序输出所有符合条件的记录&＃xff0c;检索到的符合条件的记录可能上万条&＃xff0c;也可能较少。
有插入需求&＃xff0c;量不大。
有更新需求&＃xff0c;一条记录最多一天会被更新一次&＃xff0c;当然也可能不会被更新。
无删除需求。
数据量在千万级别。这个应用场景的不安定因素来自于一些热点值。
例如&＃xff0c;当输出的数据量较大时&＃xff0c;排序对CPU的开销较大。而这些热点值可能也是查询的热点。
对于检索的条件是数组&＃xff0c;这个可以用GIN索引来解决&＃xff0c;只有排序是无法解决的。测试&＃xff0c;生成300万测试记录&＃xff1a;
postgres&＃61;# create table test(id int primary key,info int[],crt_date date);
CREATE TABLE
postgres&＃61;# insert into test select generate_series(1,3000000), (&＃39;{&＃39;||round(random()*1000)||&＃39;,&＃39;||round(random()*1000)||&＃39;,&＃39;||round(random()*1000)||&＃39;}&＃39;)::int[], current_date&＃43;round(random()*1000)::int;
INSERT 0 3000000
postgres&＃61;# create index idx_test_info on test using gin(info);
CREATE INDEX
当输出记录较少时&＃xff0c;效率还是可以的&＃xff0c;例如以下&＃xff1a;
postgres&＃61;# explain (analyze,verbose,buffers,timing) select info,crt_date from test where info &＃64;> &＃39;{1,8}&＃39;::int[] order by crt_date desc;QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------Sort (cost&＃61;101.23..101.29 rows&＃61;22 width&＃61;37) (actual time&＃61;1.668..1.672 rows&＃61;21 loops&＃61;1)Output: info, crt_dateSort Key: test.crt_date DESCSort Method: quicksort Memory: 26kBBuffers: shared hit&＃61;26-> Bitmap Heap Scan on public.test (cost&＃61;16.17..100.74 rows&＃61;22 width&＃61;37) (actual time&＃61;1.609..1.647 rows&＃61;21 loops&＃61;1)Output: info, crt_dateRecheck Cond: (test.info &＃64;> &＃39;{1,8}&＃39;::integer[])Heap Blocks: exact&＃61;21Buffers: shared hit&＃61;26-> Bitmap Index Scan on idx_test_info (cost&＃61;0.00..16.17 rows&＃61;22 width&＃61;0) (actual time&＃61;1.595..1.595 rows&＃61;21 loops&＃61;1)Index Cond: (test.info &＃64;> &＃39;{1,8}&＃39;::integer[])Buffers: shared hit&＃61;5Planning time: 0.224 msExecution time: 1.722 ms
(15 rows)
返回21行&＃xff0c;算上排序需要1.7毫秒。
但是如果返回记录数上万之后&＃xff0c;来看看结果&＃xff1a;
postgres&＃61;# explain (analyze,verbose,buffers,timing) select info,crt_date from test where info &＃64;> &＃39;{1}&＃39;::int[] order by crt_date desc;QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------Sort (cost&＃61;7737.83..7754.58 rows&＃61;6700 width&＃61;37) (actual time&＃61;17.726..18.856 rows&＃61;8896 loops&＃61;1)Output: info, crt_dateSort Key: test.crt_date DESCSort Method: quicksort Memory: 1080kBBuffers: shared hit&＃61;5028-> Bitmap Heap Scan on public.test (cost&＃61;59.93..7312.04 rows&＃61;6700 width&＃61;37) (actual time&＃61;3.722..13.585 rows&＃61;8896 loops&＃61;1)Output: info, crt_dateRecheck Cond: (test.info &＃64;> &＃39;{1}&＃39;::integer[])Heap Blocks: exact&＃61;5025Buffers: shared hit&＃61;5028-> Bitmap Index Scan on idx_test_info (cost&＃61;0.00..58.25 rows&＃61;6700 width&＃61;0) (actual time&＃61;2.620..2.620 rows&＃61;8896 loops&＃61;1)Index Cond: (test.info &＃64;> &＃39;{1}&＃39;::integer[])Buffers: shared hit&＃61;3Planning time: 0.151 msExecution time: 19.637 ms
(15 rows)
返回8896行&＃xff0c;算上排序需要19.6毫秒。&＃xff08;这是返回所有记录的时间&＃xff0c;如果是分页的话&＃xff0c;第一页会很快返回&＃xff09;优化建议。
1. 如果遇到排序带来的CPU负载过高的问题&＃xff0c;可以创建热值partial index
对于热值&＃xff0c;创建partial index。例如以上热值&＃xff1a;
postgres&＃61;# create index idx_test_info_1 on test (crt_date) where info &＃64;> &＃39;{1}&＃39;::int[];
CREATE INDEX
禁止排序
postgres&＃61;# set enable_sort&＃61;off;
SET
postgres&＃61;# explain (analyze,verbose,buffers,timing) select * from test where info &＃64;> &＃39;{1}&＃39;::int[] order by crt_date desc;QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------
-------------Index Scan Backward using idx_test_info_1 on public.test (cost&＃61;0.29..18253.53 rows&＃61;6700 width&＃61;41) (actual time&＃61;0.013..9.147 rows&＃61;8
896 loops&＃61;1)Output: id, info, crt_dateBuffers: shared hit&＃61;8909Planning time: 0.253 msExecution time: 9.911 ms
(5 rows)
当然这么做有很大的弊端&＃xff0c;因为如果热值比较多&＃xff0c;我们要为各种热值相关的查询条件创建很多的索引。2. 因为一条记录一天最多更新一次&＃xff0c;所以完全可以使用应用层缓存&＃xff0c;或者pgmemcache这样的缓存插件&＃xff0c;降低数据库的负担。3. 使用游标&＃xff0c;我们注意到用户使用了分页显示&＃xff0c;但是对于用户来说&＃xff0c;可能只会看第一页或前几页的内容&＃xff0c;所以每次都全部取到程序端是没有必要的&＃xff0c;用游标会更好。&＃xff08;注意不要使用order by limit x offset x这种方式分页&＃xff0c;会冗余扫描多次&＃xff0c;请使用cursor&＃xff0c;但是记得用完关闭。&＃xff09;详见驱动API&＃xff0c;如pg-jdbc。压力测试&＃xff1a;
测量类似分页&＃xff0c;我这里只取第一页的内容(使用热值partial index)。
注意这种用法不是游标的用法。只是方便这里测试的。
vi test.sql
select * from test where info &＃64;> &＃39;{1}&＃39;::int[] order by crt_date desc limit 10;
性能非常可观&＃xff1a;
pg95&＃64;db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -P 1 -c 16 -j 16 -T 30
progress: 1.0 s, 72844.1 tps, lat 0.213 ms stddev 0.119
progress: 2.0 s, 73691.9 tps, lat 0.215 ms stddev 0.019
progress: 3.0 s, 73603.7 tps, lat 0.216 ms stddev 0.018
progress: 4.0 s, 73501.3 tps, lat 0.216 ms stddev 0.063
progress: 5.0 s, 73433.2 tps, lat 0.216 ms stddev 0.049
progress: 6.0 s, 73645.1 tps, lat 0.216 ms stddev 0.023
progress: 7.0 s, 73551.0 tps, lat 0.216 ms stddev 0.060
progress: 8.0 s, 73640.9 tps, lat 0.216 ms stddev 0.018
progress: 9.0 s, 73650.8 tps, lat 0.216 ms stddev 0.027
progress: 10.0 s, 73753.5 tps, lat 0.215 ms stddev 0.068
对比一次取完所有数据的性能&＃xff1a;
pg95&＃64;db-172-16-3-150-> vi test.sql
select * from test where info &＃64;> &＃39;{1}&＃39;::int[] order by crt_date desc;pg95&＃64;db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -P 1 -c 16 -j 16 -T 30
progress: 1.0 s, 219.9 tps, lat 68.165 ms stddev 7.355
progress: 2.0 s, 233.8 tps, lat 67.849 ms stddev 15.181
progress: 3.0 s, 238.4 tps, lat 68.023 ms stddev 10.556
progress: 4.0 s, 233.9 tps, lat 68.030 ms stddev 4.459
progress: 5.0 s, 233.6 tps, lat 68.019 ms stddev 4.131
progress: 6.0 s, 235.5 tps, lat 67.472 ms stddev 3.204
progress: 7.0 s, 237.7 tps, lat 67.627 ms stddev 3.257
progress: 8.0 s, 233.5 tps, lat 67.779 ms stddev 4.815
progress: 9.0 s, 238.7 tps, lat 67.723 ms stddev 7.603
progress: 10.0 s, 232.0 tps, lat 68.098 ms stddev 13.948[参考]
1. http://www.postgresql.org/docs/9.4/static/functions-array.html