Extended_Query_Protocol

Posted on In

当使用简单查询协议时，每个命令（即使重复多次）都必须经历上述所有阶段：

  1. parsing
  2. transformation
  3. planning
  4. execution

但是，一次又一次地解析相同的查询是没有意义的。重复解析仅在常量上不同的查询也没有多大意义——解析树结构仍然保持不变。

简单查询协议的另一个缺点是客户端会立即收到整个结果，无论它可能包含多少行。

一般来说，使用 SQL 命令可以克服这些限制。要处理第一种情况，您可以在运行 EXECUTE 命令之前PREPARE查询；第二个问题可以通过使用 DECLARE 创建游标并通过 FETCH 返回行来解决。但在这种情况下，这些新创建的对象的命名必须由客户端处理，而服务器则需要解析额外命令的额外开销

扩展的客户端—服务器协议提供了一种替代方案，使得可以在协议本身的命令级别上，对各个算子执行阶段进行精确控制。

Preparation

在准备阶段，查询会像往常一样被解析并进行转换，但生成的解析树会保存在后端的内存中。

PostgreSQL 并不存在全局的查询缓存。这种架构的缺点是显而易见的：即使同一条查询已经被其他后端进程解析过，每个后端仍然必须重新解析其接收到的所有查询。但与此同时，这种设计也带来了一些好处。全局缓存由于需要加锁，很容易成为系统瓶颈。一个客户端如果频繁执行大量相似但不完全相同的小查询（例如仅常量不同的查询），会产生大量缓存访问流量，从而对整个实例的性能造成负面影响。在 PostgreSQL 中，查询是在各个后端本地解析的，因此不会对其他进程产生影响。

一条预处理（prepared）的查询可以带参数化。下面是一个使用 SQL 命令的简单示例（尽管这与协议层的预处理不完全相同，但最终效果是一样的）

1
2
=> PREPARE plane(text) AS
SELECT * FROM aircrafts WHERE aircraft_code = $1;

所有命名的预处理语句都显示在 pg_prepared_statements 视图中：

1
2
3
4
5
6
7
=> SELECT name, statement, parameter_types
FROM pg_prepared_statements \gx
−[ RECORD 1 ]−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 
name            | plane
statement       | PREPARE plane(text) AS +
                | SELECT * FROM aircrafts WHERE aircraft_code = $1; 
parameter_types | {text}

这里不会显示任何未命名语句（即使用扩展查询协议或 PL/pgSQL 的语句）。其他后端准备的语句也不会显示：因为无法访问其他会话的内存。

Parameter Binding

在预处理语句被执行之前，必须先绑定实际的参数值。

1
2
3
4
5
=> EXECUTE plane('733');
aircraft_code | model | range 
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−+−−−−−−− 
733            | Boeing 737−300 | 4200
(1 row)

在预处理语句中绑定参数，相比将字面量直接拼接到查询字符串中，其优势在于可以彻底杜绝 SQL 注入：绑定的参数值无法以任何方式修改已经构建完成的解析树。若不使用预处理语句而想达到同等的安全级别，就必须对来自不可信来源的每一个值进行非常谨慎的转义处理。

Planning and Execution

在执行预处理语句时，查询规划会基于实际的参数值来进行；随后生成的执行计划会交由执行器处理。

由于不同的参数值可能对应不同的最优执行计划，因此在规划阶段准确考虑具体参数值是非常重要的。举例来说，在查询价格较高的预订记录时，规划器会假定符合条件的行数不多，从而选择使用索引扫描。

1
2
3
4
5
6
7
8
9
10
11
=> CREATE INDEX ON bookings(total_amount);
=> EXPLAIN SELECT *
FROM bookings
WHERE total_amount > 1000000;
								QUERY PLAN 
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Bitmap Heap Scan on bookings (cost=86.49..9245.82 rows=4395 wid... 
	Recheck Cond: (total_amount > '1000000'::numeric)
	−> Bitmap Index Scan on bookings_total_amount_idx (cost=0.00....
Index Cond: (total_amount > '1000000'::numeric) 
(4 rows)

但如果给定的条件对所有预订记录都成立，那么使用索引就没有意义了，因为最终仍然需要扫描整张表。

1
2
3
4
5
6
7
=> EXPLAIN SELECT *
FROM bookings
WHERE total_amount > 100;
							QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Seq Scan on bookings (cost=0.00..39835.88 rows=2111110 width=21) Filter: (total_amount > '100'::numeric)
(2 rows)

在某些情况下，规划器可能会同时保留解析树和查询计划，以避免重复进行规划。由于这种计划不会考虑具体的参数值，因此被称为通用计划（generic plan），以区别于基于实际参数值生成的定制计划（custom plan）。

参数化预处理语句的前五次执行，优化过程始终依赖于实际的参数值；规划器会基于这些参数值计算定制计划（custom plan）的平均成本。从第六次执行开始，如果通用计划（generic plan）在平均意义上比定制计划更高效（同时考虑到每次都需要重新生成定制计划的额外开销），规划器就会保留通用计划并继续使用它，从而跳过后续的优化阶段。

一个显而易见的场景是：当查询不包含任何参数时，数据库可以使用通用计划而不会对性能造成影响

1
2
3
4
5
6
7
 => EXECUTE plane('763');
=> EXECUTE plane('773');
=> EXPLAIN EXECUTE plane('319');
							QUERY PLAN 
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 
Seq Scan on aircrafts_data ml (cost=0.00..1.39 rows=1 width=52)
Filter: ((aircraft_code)::text = '319'::text) (2 rows)

在第五次执行之后，规划器会切换为通用计划（generic plan）：该计划与之前的定制计划并无差别，成本也相同，但后端只需构建一次即可，并且可以跳过优化阶段，从而降低规划开销。此时，EXPLAIN 命令显示参数是通过位置来引用的，而不再显示其具体取值。

1
2
3
4
5
6
 => EXECUTE plane('320');
=> EXPLAIN EXECUTE plane('321');
							QUERY PLAN 
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 
Seq Scan on aircrafts_data ml (cost=0.00..1.39 rows=1 width=52)
Filter: ((aircraft_code)::text = $1) (2 rows)

“优化阶段”特指每次执行时基于参数值重新生成执行计划的那一部分工作。

我们可以很容易想象这样一种不利情况：前几次生成的 custom plan 比 generic plan 更昂贵；随后可能出现的更高效的 custom plan 却完全不会被考虑。此外，规划器比较的是估算成本而非实际执行成本，这也可能导致误判。

不过，如果规划器的自动决策出现偏差，你可以通过设置 plan_cache_mode 参数来覆盖自动选择，从而强制使用 generic plan 或 custom plan

1
2
3
4
5
6
=> SET plan_cache_mode = 'force_custom_plan';
=> EXPLAIN EXECUTE plane('CN1');
							QUERY PLAN 
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 
Seq Scan on aircrafts_data ml (cost=0.00..1.39 rows=1 width=52)
Filter: ((aircraft_code)::text = 'CN1'::text) (2 rows)

除了其他信息之外，pg_prepared_statements 视图还显示了所选计划的统计信息：

1
2
3
4
5
6
=> SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements;
name   | generic_plans | custom_plans 
−−−−−−−+−−−−−−−−−−−−−−−+−−−−−−−−−−−−−− 
plane  | 1             | 6
(1 row)

Getting the Results

扩展查询协议允许按批次而不是一次性检索数据。SQL 游标（cursor）几乎具有相同效果（唯一的区别是服务器需要做一些额外处理，而且规划器只会优化前 cursor_tuple_fraction 行的获取，而不是整个结果集）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
=> BEGIN;
=> DECLARE cur CURSOR FOR SELECT *
FROM aircrafts
ORDER BY aircraft_code;
=> FETCH 3 FROM cur;
aircraft_code  | model           | range 
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−+−−−−−−−
319            | Airbus A319−100 | 6700
320            | Airbus A320−200 | 5700
321            | Airbus A321−200 | 5600
(3 rows)
=> FETCH 2 FROM cur;
aircraft_code  | model          | range 
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−+−−−−−−− 
733            | Boeing 737−300 | 4200 
763            | Boeing 767−300 | 7900
=> COMMIT;
(2 rows)

如果查询返回大量行，且客户端需要获取所有行，那么系统吞吐量高度依赖于批量大小（batch size）。批量包含的行数越多，每次访问服务器并获取响应时产生的通信开销就越小。然而，随着批量大小继续增加，这种优势会逐渐减弱：例如，将行一条条取出与每批取 10 行的差别非常明显，但将每批取 100 行与每批取 1000 行相比，性能提升就不那么显著了

参考书目

  1. Egor Rogov, PostgreSQL 14 Internals, https://postgrespro.com/community/books/internals