postgresql数据预热

test=# select * from pg_available_extensions where name like '%prewarm%' order by name;
name | default_version | installed_version | comment
-------------+-----------------+-------------------+-----------------------
sys_prewarm | 1.2 | | prewarm relation data
(1 row)

test=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------
dbms_ddl | 1.0 | sys | DBMS_DDL system package
dbms_output | 1.0 | sys | DBMS_OUTPUT system package
dbms_utility | 1.0 | sys | dbms_utility extension package
kdb_cast | 1.0 | sys | kdb_cast extension
kdb_license | 1.0 | pg_catalog | kdb_license extension
kdb_oracle_datatype | 1.5 | sys | kdb_oracle_datatype extension
kdb_tinyint | 1.0 | pg_catalog | Create a new data type tinyint and its functions operators and indexes
kingbase_version | 1.0 | pg_catalog | This is a utility that provides function related to version number, it is used to get the Kingbase version number.
owa_util | 1.0 | sys | owa_util system package
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
plsql | 1.0 | pg_catalog | PL/SQL procedural language
src_restrict | 1.0 | src_restrict | src restrict plugin
sys_anon | 1.0 | anon | provides data masking functionality
sys_buffercache | 1.3 | public | examine the shared buffer cache
sys_freespacemap | 1.2 | sys | examine the free space map (FSM)
sys_hint_plan | 1.3.5 | hint_plan |
sys_kwr | 1.5 | public | KingbaseES auto workload repository and report builder
sys_stat_statements | 1.10 | public | track parsing, planning and execution statistics of all SQL statements executed
sysaudit | 1.0 | sysaudit | provides auditing functionality
sysmac | 1.0 | sysmac | Mac for Kingbase
xlog_record_read | 1.0 | pg_catalog | xlog_record_read functions
(21 rows)

test=# CREATE EXTENSION sys_prewarm;
CREATE EXTENSION
test=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------
dbms_ddl | 1.0 | sys | DBMS_DDL system package
dbms_output | 1.0 | sys | DBMS_OUTPUT system package
dbms_utility | 1.0 | sys | dbms_utility extension package
kdb_cast | 1.0 | sys | kdb_cast extension
kdb_license | 1.0 | pg_catalog | kdb_license extension
kdb_oracle_datatype | 1.5 | sys | kdb_oracle_datatype extension
kdb_tinyint | 1.0 | pg_catalog | Create a new data type tinyint and its functions operators and indexes
kingbase_version | 1.0 | pg_catalog | This is a utility that provides function related to version number, it is used to get the Kingbase version number.
owa_util | 1.0 | sys | owa_util system package
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
plsql | 1.0 | pg_catalog | PL/SQL procedural language
src_restrict | 1.0 | src_restrict | src restrict plugin
sys_anon | 1.0 | anon | provides data masking functionality
sys_buffercache | 1.3 | public | examine the shared buffer cache
sys_freespacemap | 1.2 | sys | examine the free space map (FSM)
sys_hint_plan | 1.3.5 | hint_plan |
sys_kwr | 1.5 | public | KingbaseES auto workload repository and report builder
sys_prewarm | 1.2 | public | prewarm relation data
sys_stat_statements | 1.10 | public | track parsing, planning and execution statistics of all SQL statements executed
sysaudit | 1.0 | sysaudit | provides auditing functionality
sysmac | 1.0 | sysmac | Mac for Kingbase
xlog_record_read | 1.0 | pg_catalog | xlog_record_read functions
(22 rows)

test=# \dx+ sys_prewarm
Objects in extension "sys_prewarm"
Object description
--------------------------------------------------------
function autoprewarm_dump_now()
function autoprewarm_start_worker()
function sys_extend(regclass,bigint)
function sys_prewarm(regclass,text,text,bigint,bigint)
(4 rows)

test=# \sf+ sys_prewarm
CREATE OR REPLACE FUNCTION public.sys_prewarm(regclass, mode text DEFAULT 'buffer'::text, fork text DEFAULT 'main'::text, first_block bigint DEFAULT NULL::bigint, last_block bigint DEFAULT NULL::bigint)
RETURNS bigint
LANGUAGE c
PARALLEL SAFE
1 AS '$libdir/sys_prewarm', $function$sys_prewarm$function$

select pg_size_pretty(pg_total_relation_size('pgbench_accounts'));

--加载数据从硬盘加载到内存

select pg_prewarm(test);

--查看是否缓存
select count(*) from sys_buffercache where relfilenode = (select relfilenode from pg_class where relname = 't');

--需要缓存对象