PostgreSQL查询优化

EXPLAIN语法
EXPLAIN代价因子校准
- cpu_tuple_cost因子
- random_page_cost因子校准 cpu_index_tuple_cst因子校准 cpu_operator_cost因子校准
auto_explain？not found

EXPLAIN语法

ANALYZE[boolean]：执行statement，得到真是运行时间和统计信息
VERBOSE[boolean]：详细信息
COSTS[boolean]：cost值，默认打开
BUFFERS[boolean]：本次QUERY shared或local buffer的信息，包括命中未命中，脏写
TIMING[boolean]：输出时间开销
FORMAT{TEXT|XML|JSON|YAML}：输出格式

EXPLAIN代价因子校准

cpu_tuple_cost因子

创建测试表

1	create table tbl_cost_align(id int, info text, crt_time timestamp);

插入测试数据

1
2

insert into tbl_cost_align select(random() * 2000000000)::int, md5(random()::text), clock_timestamp() frmo gnerate_series(1, 10000000);
insert into tbl_cost_align select(random() * 2000000000)::int, md5(random()::text), clock_timestamp() frmo gnerate_series(1, 1000000000);

分析表，获得统计信息
1
analyze tbl_cost_align;

可以查看到占用的数据块个数

1	select relpages from pg_class where relname='tbl_cost_align';

执行checkpoint后关闭数据库，为了得到纯粹的物理磁盘的连续io请求cost常量，不能有shared buffer干扰
1
checkpoint;
1
$ pg_ctl stop -m fast
同时不能有OS Cache干扰，清除系统cachce
1
$ sync; echo 3 > /proc/sys/vm/drop_caches
使用systemtab来脚本分析

为了增加IO响应时间的柱状图输出，首先启动数据库，CPU亲和设置为1
1
2
$ taskset -c 1 postgres >/dev/null 2&>1
$ psql

指定启动脚本
1
$ taskset -c 7 stap -e `...`

在psql执行explain analyze

1	explain (analyze, verbose, costs, buffers, timing) select * from tbl_cost_align;

通过数据求得
1
-- cpu_tuple_cost：total time - actual time = shared read * 块处理时间 + rows * x(xpu_tuple_cost)
random_page_cost因子校准 cpu_index_tuple_cst因子校准 cpu_operator_cost因子校准

为了保证公式准确性方面验证，把所有常亮设置为1

set random_page_cost = 1;
set cpu_tuple_cost = 1;
set cpu_index_tuple_cost = 1;
set cpu_operator_cost = 1;

QUERY PLAN

1
2
3

set enable_seqscan = off; 
set enable_bitmapscan = off;
explain (analyze, verbose, costs, buffers, timing) select * from tbl_cost_align where id > 1998999963;

计算公式为：

-- = blocks * random_page_cost + cpu_tuple_cost * rows + cpu_index_tuple_cost * rows + cpu_operator_cost * ?
-- 其中cpu_index_tuple_cost和cpu_operator_cost的比例默认是2:1
-- 需要设置
set cpu_operator_cost = 2;

cao，略

auto_explain？not found

1 2	-- 例如，以下设置将执行超过10ms的记录写到日志 set auto_explain.log_min_duration=10ms;

如果需要配置到文件，则写入，然后重启：

1 2	shared_reload_libraries = 'pg_stat_statements, auto_explain' auto_explain.log_min_duration = 100ms

EXPLAIN语法

EXPLAIN代价因子校准

cpu_tuple_cost因子

random_page_cost因子校准 cpu_index_tuple_cst因子校准 cpu_operator_cost因子校准

auto_explain？not found