PostgreSQL学习笔记
查询处理
解析器(Parser)
解析器以纯文本行式从SQL语句生成分析树。
分析器(Analyzer/Analyser)
分析器对分析树进行语义分析并生成查询树。
重写器(rewriter)
如果存在规则,则重写器使用存储在规则系统中的规则转换查询树。
规划器(Planner)
规划器从查询树生成最有效的执行计划树。
执行器(Executor)
按照计划树中的顺序访问表和索引执行相应查询。
计划树
PostgreSQL Planner执行三个步骤,如下所示:
(1)预处理
(2)通过估算所有可能的访问路径的成本,获得最廉价的访问路径
(3)从最廉价的路径创建计划树
代价估算
总代价 =启动代价 + 运行代价
顺序扫描
顺序扫描的成功由函数 cost_seqscan()来估算。
顺序扫描,由于是扫描所有数据页,不需要准备工作,start-up的成本为0,run的成本计算公式为:
‘run cost’ = ‘cpu run cost’ + ‘disk run cost’
=(cpu_tuple_cost + cpu_operator_cost) × Ntuple + seq_page_cost × Npage
其中seq_page_cost,cpu_tuple_cost,cpu_operator_cost的值在配置文件postgresql.conf中设置,默认值为 1.0, 0.01, and 0.0025。
Ntuple,Npage可在pg_class中查询出来。
索引扫描
索引扫描的成本由函数cost_index()来估算。
start-up成本
索引扫描start-up成本指读取索引页拿到目标表第一个tuple的成本,其估算公式为:
‘start-up cost’={ceil(log2(Nindex,tuple)) + (Hindex + 1) x 50} x cpu_operator_cost
其中Hindex为索引树的高度。可以通过bt_metap获取索引高度
run成本
索引扫描的run成本指表和索引cpu成本、IO成本之和:
‘run cost’=(‘index cpu cost’ + ‘table cpu cost’) + (‘index IO cost’ + ‘table IO cost’)
注:仅索引扫描不需估算 ‘table cpu cost’ 和 ‘table IO cost’。
其中三个成本估算公式如下:
‘index cpu cost’=Selectivity x Nindex,tuple x (cpu_index_tuple_cost+qual_op_cost),
‘table cpu cost’=Selectivity x Ntuple x cpu_tuple_cost,
‘index IO cost’=ceil(Selectivity x Nindex,page) x random_page_cost
其中 cpu_index_tuple_cost, cpu_tuple_cost, random_page_cost 在配置文件postgresql.conf的默认值分别为 0.005, 0.01, 4.0。qual_op_cost,
粗略的来讲,是评估索引的成本,这里为0.0025。Selectivity 指where子句的索引的搜索范围的比例,
它是从0到1的浮点数,如 (Selectivity x Ntuple) 指读取表tuple的数量, (Selectivity x Nindex,page) 指读取索引页的数量。
Selectivity
查询谓词的选择性使用 histogram_bounds 或 MCV(Most Common Value) 来估算,这两者可在pg_stats中查询出来。
外部数据包装器
创建查询树
analyzer/analyser使用外部表的定义创建输入SQL的查询树,在使用CREATE FOREIGN TABLE或
IMPORT FOREIGN SCHEMA命令时, 这些表的相关信息存储在了pg_catalog.pg_class和pg_catalog.pg_foreign_table中.
连接远程服务器
要连接到远程服务器, 规划器 (或执行器) 使用特定库连接到远程数据库服务器。例如, 要连接到远程PostgreSQL服务器, postgres_fdw使用 libpq。
逆解析
若要生成计划树, 规划器将从外部表的计划树扫描路径创建纯文本 SQL语句。
发送 SQL 语句和接收结果
在执行逆解析之后,执行器将deparsed SQL语句发送到远程服务器并接收结果。
Postgres_fdw扩展如何执行
postgres_fdw扩展是一个特殊的模块,由PostgreSQL全球开发小组正式维护,其源代码包含在PostgreSQL源代码树中。
多表查询
要执行多表查询,postgres_fdw使用单表SELECT语句获取每个外部表,然后在本地服务器上将它们连接起来。
在9.5或更早版本中,即使外部表存储在同一个远程服务器中,postgres_fdw也会分别获取它们并连接它们。
在版本9.6或更高版本中,postgres_fdw得到改进,并且当外部表都同一服务器上时,而且use_remote_estimate选项打开时,可以在远程服务器上执行远程连接操作。
排序操作
在9.5或更早版本中,在本地服务器上处理排序操作(例如ORDER BY),即本地服务器在排序操作之前从远程服务器获取所有目标行。
在版本9.6或更高版本中,postgres_fdw尽可能的在远程服务器上使用ORDER BY子句执行SELECT语句。
聚合函数
在版本9.6或更早版本中,类似于前一小节中提到的排序操作,AVG()和cont() 等聚合函数将在本地服务器上处理。
在版本10或更高版本中,postgres_fdw将尽可能的在远程服务器上执行带有聚合函数的SELECT语句。