PostgreSQL代价全解及其查询优化

explain语法

下面是postgresql.conf中默认的一些代价因子。


analyze这个关键字是将语句真正的执行。如果只有shared_buffer，说明没有从外部命中数据。有read的话，说明是从磁盘等外部环境读取了数据。

三大连接

在postgresql.conf中开关三大连接

嵌套连接

哈希连接

合并连接

explain成本计算

准备测试数据：


如上图我们可以看到，reltuples代表总共有多少条记录，relpages是表示总共有多少页。

结合上下图，来看看这全表扫描是怎么算的。

再来看看下面这种情况，id<100中的小于号是对应了某个函数的，我们应该在估算成本时，加上函数的开销。

我们来查一查小于符号对应的函数。

看看上图，有那么多函数，到底哪一个是比较int类型的小于号呢？

下面箭头所指函数就是对应函数。

然后查一下这个函数的成本为1

然后看看总的1693是怎么算的？

explain行数评估

从bucket评估行数

我们用真实例子测试一下;
下图框住部分有100个bucket，bucket数量可以在postgresql中设置，默认是100.

然后这个row跟bucket有关。

从MCV(most common values)评估行数

来举一个真实例子，我继续插入10万条数据。

我们来看看现在这个rows是怎么评估的？

现在箭头所指的两个属性有值了。

现在有20万条记录

然后看下面怎么计算rows

从MCV和distinct值中评估行数

继续举例：

然后计算distinct的值：

然后计算单个值的选择性：

最终结果约等于4：

从MCV和bucket中评估行数

继续举例：id>某个bucket的界限

计算单个选择性：

然后计算最终结果：

多个列查询条件的选择性相乘评估

索引扫描

代价因子的校准

校准参数：seq_page_cost(stap), cpu_tuple_cost(公式)

用systemtap工具去测试你的内核，下面是测试这个工具能否正常使用。

准备测试数据：

查看有多少个页。

然后关闭数据库

用root权限将操作系统的缓存清掉 sync; echo 3 > /proc/sys/vm/drop_caches

重启以非0号cpu去启动数据库，因为0号cpu启动会有额外开销，并查询进程id,用于探测。以非0号cpu启动数据库 taskset -c 1 /opt/module/pgsql9/bin/postgres >/dev/null 2>&1
这个/opt/module/pgsql9/bin是你安装postgresql这个软件的目录。

然后用root执行监控脚本：

taskset -c 7 stap -e '
global a
probe process("/opt/module/pgsql9/bin/postgres").mark("query__start") {
delete a
println("query__start ", user_string($arg1), "pid:", pid())
}
probe vfs.read.return {
t = gettimeofday_ns() - @entry(gettimeofday_ns())
# if (execname() == "postgres" && devname != "N/A")
a[pid()] <<< t
}
probe process("/opt/module/pgsql9/bin/postgres").mark("query__done") {
if (@count(a[pid()]))
printdln("**", pid(), @count(a[pid()]), @avg(a[pid()]))
println("query__done ", user_string($arg1), "pid:", pid())
if (@count(a[pid()])) {
println(@hist_log(a[pid()]))
#println(@hist_linear(a[pid()],1024,4096,100))
}
delete a
}' -x 17986

未完待续！！

auto_explain插件的使用

如果你使用gmake world编译安装，那么就直接登录数据库加载即可，甚至不需要手动extension。我这是用postgres_admin超级用户登的