pg中插件pg_prewarm预热对查询的影响
洪富
插件btree_gin任意字段查询走索引
create extension btree_gin;
create index idx_t_gin3_1on t_gin3 using gin(c1, c2, c3, c4, c5, c6, c7, c8, c9);
postgres=# \d t_gin3
数据表 "public.t_gin3"
栏位 | 类型 | 校对规则 | 可空的 | 预设
------+---------+----------+--------+------
id | integer | | |
c1 | integer | | |
c2 | integer | | |
c3 | integer | | |
c4 | integer | | |
c5 | integer | | |
c6 | integer | | |
c7 | integer | | |
c8 | integer | | |
c9 | integer | | |
索引：
"idx_t_gin3_1" gin (c1, c2, c3, c4, c5, c6, c7, c8, c9)
在无预热情况下的查询结果，（Buffers: shared hit=20 read=847）表示在共享内存中直接读到20个块，从磁盘中读到847块
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin3 where c1=1 or c2=1 and c3=1 or c4=1 and (c6=1 or c7=2) or c8=9 or c9=10;
Bitmap Heap Scan on public.t_gin3 (cost=222.28..1435.75 rows=12370 width=40) (actual time=65.655..162.791 rows=12450 loops=1)
Output: id, c1, c2, c3, c4, c5, c6, c7, c8, c9
Recheck Cond: ((t_gin3.c1 = 1) OR ((t_gin3.c2 = 1) AND (t_gin3.c3 = 1)) OR (((t_gin3.c4 = 1) AND (t_gin3.c6 = 1)) OR ((t_gin3.c4 = 1) AND (t_gin3.c7 = 2))) OR (t_gin3.c8 = 9) OR (t_gin3
.c9 = 10))
Heap Blocks: exact=834
Buffers: shared hit=20 read=847
-> BitmapOr (cost=222.28..222.28 rows=12649 width=0) (actual time=56.955..56.959 rows=0 loops=1)
Buffers: shared hit=20 read=13
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..99.43 rows=10057 width=0) (actual time=25.424..25.424 rows=10136 loops=1)
Index Cond: (t_gin3.c1 = 1)
Buffers: shared hit=1 read=5
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..21.64 rows=164 width=0) (actual time=18.058..18.058 rows=166 loops=1)
Index Cond: ((t_gin3.c2 = 1) AND (t_gin3.c3 = 1))
Buffers: shared hit=6 read=2
-> BitmapOr (cost=46.97..46.97 rows=78 width=0) (actual time=9.147..9.149 rows=0 loops=1)
Buffers: shared hit=9 read=4
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..20.43 rows=43 width=0) (actual time=6.767..6.767 rows=39 loops=1)
Index Cond: ((t_gin3.c4 = 1) AND (t_gin3.c6 = 1))
Buffers: shared hit=3 read=3
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..20.35 rows=35 width=0) (actual time=2.372..2.372 rows=30 loops=1)
Index Cond: ((t_gin3.c4 = 1) AND (t_gin3.c7 = 2))
Buffers: shared hit=6 read=1
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..21.52 rows=1270 width=0) (actual time=2.367..2.367 rows=1231 loops=1)
Index Cond: (t_gin3.c8 = 9)
Buffers: shared hit=2 read=1
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..20.10 rows=1080 width=0) (actual time=1.847..1.847 rows=1120 loops=1)
Index Cond: (t_gin3.c9 = 10)
Buffers: shared hit=2 read=1
Planning Time: 170.316 ms
Execution Time: 181.734 ms缓存预热使用pg_prewarm
select pg_prewarm('t_gin3','buffer','main');
查出表在内存中的数据块的数量。
postgres=# select relname,count(*) as buffers from pg_buffercache b inner join pg_class c on
b.relfilenode = pg_relation_filenode(c.oid) and b.reldatabase in (0,(select oid from pg_database
where datname=current_database())) group by c.relname order by 2 desc;
relname | buffers
-----------------------------------------+---------
t_gin3 | 834
pg_attribute | 32
idx_t_gin3_1 | 14
pg_class | 13
pg_operator | 13
pg_proc | 9
pg_attribute_relid_attnum_index | 9
pg_statistic | 7
pg_amop | 7
pg_proc_oid_index | 7
pg_statistic_relid_att_inh_index | 5
pg_class_relname_nsp_index | 5
pg_index | 5
pg_amproc_fam_proc_index | 4
pg_amproc | 4
pg_amop_opr_fam_index | 4
pg_proc_proname_args_nsp_index | 4
pg_class_oid_index | 4
pg_type_typname_nsp_index | 3
pg_type_oid_index | 3
pg_opclass | 3
pg_description_o_c_o_index | 3
pg_amop_fam_strat_index | 3
pg_type | 3
pg_operator_oprname_l_r_n_index | 3
pg_operator_oid_index | 3
pg_aggregate_fnoid_index | 2
pg_opclass_oid_index | 2
pg_index_indrelid_index | 2
pg_toast_2619_index | 2
pg_tablespace_oid_index | 2
pg_cast_source_target_index | 2
pg_cast | 2
预热下的查询结果（Buffers: shared hit=867）表示在共享内存中直接读到867个块
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin3 where c1=1 or c2=1 and c3=1 or c4=1 and (c6=1 or c7=2) or c8=9 or c9=10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------
Bitmap Heap Scan on public.t_gin3 (cost=222.28..1435.75 rows=12370 width=40) (actual time=1.726..3.759 rows=12450 loops=1)
Output: id, c1, c2, c3, c4, c5, c6, c7, c8, c9
Recheck Cond: ((t_gin3.c1 = 1) OR ((t_gin3.c2 = 1) AND (t_gin3.c3 = 1)) OR (((t_gin3.c4 = 1) AND (t_gin3.c6 = 1)) OR ((t_gin3.c4 = 1) AND (t_gin3.c7 = 2))) OR (t_gin3.c8 = 9) OR (t_gin3
.c9 = 10))
Heap Blocks: exact=834
Buffers: shared hit=867
-> BitmapOr (cost=222.28..222.28 rows=12649 width=0) (actual time=1.558..1.561 rows=0 loops=1)
Buffers: shared hit=33
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..99.43 rows=10057 width=0) (actual time=0.764..0.764 rows=10136 loops=1)
Index Cond: (t_gin3.c1 = 1)
Buffers: shared hit=6
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..21.64 rows=164 width=0) (actual time=0.283..0.283 rows=166 loops=1)
Index Cond: ((t_gin3.c2 = 1) AND (t_gin3.c3 = 1))
Buffers: shared hit=8
-> BitmapOr (cost=46.97..46.97 rows=78 width=0) (actual time=0.300..0.301 rows=0 loops=1)
Buffers: shared hit=13
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..20.43 rows=43 width=0) (actual time=0.153..0.153 rows=39 loops=1)
Index Cond: ((t_gin3.c4 = 1) AND (t_gin3.c6 = 1))
Buffers: shared hit=6
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..20.35 rows=35 width=0) (actual time=0.146..0.147 rows=30 loops=1)
Index Cond: ((t_gin3.c4 = 1) AND (t_gin3.c7 = 2))
Buffers: shared hit=7
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..21.52 rows=1270 width=0) (actual time=0.093..0.093 rows=1231 loops=1)
Index Cond: (t_gin3.c8 = 9)
Buffers: shared hit=3
-> Bitmap Index Scan on idx_t_gin3_1 (cost=0.00..20.10 rows=1080 width=0) (actual time=0.111..0.111 rows=1120 loops=1)
Index Cond: (t_gin3.c9 = 10)
Buffers: shared hit=3
Planning Time: 0.118 ms
Execution Time: 4.213 ms
在预热的情况下时间缩小到4.213ms
类似资料：