PostgreSQL 数据库性能 影响开销值的两个因素

1 开销值计算

drop table IF exists emp1;
create table emp1 as select * from sott.emp;
insert into emp1 select *from emp1;
explian (analyze,buffers,verbose) select ename,detpno,sal from emp1;
show cpu_tuple_cost
show cpu_operator_cost
show seq_page_cost

2 统计信息

• 关闭autovacauum 自动统计收集

# 数据准备
drop table IF EXISTS emp1;
create table emp1 as select * from sott.emp;
# 查询统计信息

select RELNAME,RELPAGES,RELTUPLES from SYS_CLASS  where relname ='emp1';
# 未优化的SQL
explian (analyze,buffers,verbose) select ename,detpno,sal from emp1;
analyze emp1;
# 优化后的SQL
explian (analyze,buffers,verbose) select ename,detpno,sal from emp1;

3 死亡元组

• 关闭autovacuum 自动清理

# 数据准备
drop table IF EXISTS emp1;
create table emp1 as select * from sott.emp;
insert into emp1 select * from emp1 ;
# 未优化的SQL
select RELNAME,RELPAGES,RELTUPLES from SYS_CLASS  where relname ='emp1';
explian (analyze,buffers,verbose) select ename,detpno,sal from emp1;
vacuum emp1;
# 优化后的SQL
select RELNAME,RELPAGES,RELTUPLES from SYS_CLASS  where relname ='emp1';

explian (analyze,buffers,verbose) select ename,detpno,sal from emp1;

4 执行计划

1. 查看执行计划

explain select * from scott.emp;

2. 实际执行计划

explain analyze select * from scott.emp;

3. 查看命中率

explain (analyze,buffers) select * from scott.emp;

4. 更多信息

explain (analyze,buffers,verbose) select * from scott.emp;