PostgreSQL 内核开发 执行计划存储的位置

select count(*) from pg_statistic     ;
select count(*) from pg_foreign_server;
select count(*) from pg_am            ;
select count(*) from pg_amop          ;
select count(*) from pg_aggregate     ;
select count(*) from pg_cast          ;
select count(*) from pg_constraint    ;
select count(*) from pg_amproc        ;


create table test (id int , info text, crt_time timestamp);  
insert into test select generate_series(1,1000000);  
insert into test select 1 from generate_series(1,10000000);  
create index idx_test on test(id);

PREPARE select_id(number)  as select * from test where id=1;

explain execute plan1(1);
prepare plan1 as select * from test where id = ($1);
explain execute plan1(2);
select * from pg_prepared_statements;

1. 查看shared_buffer 中的内容

SELECT c.relname
 , pg_size_pretty(count(*) * 8192) as buffered
 , round(100.0 * count(*) / ( SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent
 , round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid, c.relname
ORDER BY 3 DESC
LIMIT 10;