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