[译]理解PG如何执行一个查询-2

yzs87

已于 2022-06-08 21:49:49 修改

阅读量596

点赞数

文章标签：数据库 java mysql 大数据 python

于 2022-04-12 22:34:40 首次发布

原文链接：https://mp.weixin.qq.com/s?__biz=MzU1OTgxMjA4OA==&mid=2247484743&idx=1&sn=56f75a6fb96573b60847bae3b07f1ebe&chksm=fc10d9d0cb6750c642715a5abfc4fd782b25dd5c7d8ffb091f9ff70867baed87056465f5b975&scene=126&&sessionid=0

版权

Limit

Limit算子用于限制结果集的大小。PG使用limit算子进行limit和offset处理。Limit算子将输入集前x行去掉，返回接着的y行，再将剩下的丢弃。如果查询中包括offset，x表示offset的数量，否则x为0.如果查询中包含Limit，y表示limit数量，否则y是输入集的大小。

输入集的顺序对LIMIT运算符并不重要，但它通常对整个查询计划很重要。例如，此查询的查询计划：

perf=# EXPLAIN SELECT * FROM recalls LIMIT 5;

NOTICE: QUERY PLAN:

Limit (cost=0.00..0.10 rows=5 width=1917)

-> Seq Scan on recalls (cost=0.00..9217.41 rows=39241 width=1917)

Limit算子仅返回Seq Scan的前5条记录。另外下面这个查询：

perf=# EXPLAIN ANALYZE SELECT * FROM recalls ORDER BY yeartxt LIMIT 5;

NOTICE: QUERY PLAN:

Limit (cost=0.00..0.10 rows=5 width=1917)

->Sort (cost=145321.51..145321.51 rows=39241 width=1911)

->Seq Scan on recalls (cost=0.00..9217.41 rows=39241 width=1917)

这个limit算子返回的是Sort的结果集，也就是一个有序的输入集和的前5条。Limit算子不会删除结果集中的列，但是显然他会删除行，实际上并不是从表中真正删除。

如果一个查询中包含limit或offset或者2者，那么计划器/优化器会使用一个limit算子。如果查询中仅包含limit，limit算子在处理整个集合前会先返回第一行记录。

Aggregate

当查询中包含聚合函数时计划器/优化器会产生一个Aggregate算子。下面是聚合函数：AVG(),COUNT(),MAX(),MIN(),STDDEV(),SUM()和VARIANCE()。

Aggregate通过读取输入集中的所有行，然后计算出聚合值。如果输入集没有分组，那么就产生一个结果行。例如：

movies=# EXPLAIN SELECT COUNT(*) FROM customers;

Aggregate (cost=22.50..22.50 rows=1 width=0)

-> Seq Scan on customers (cost=0.00..20.00 rows=1000 width=0)

如果输入集分组了，就对每个组产生一个结果行：

movies=# EXPLAIN

movies-# SELECT COUNT(*), EXTRACT( DECADE FROM birth_date )

movies-# FROM customers

movies-# GROUP BY EXTRACT( DECADE FROM birth_date );

NOTICE: QUERY PLAN:

Aggregate (cost=69.83..74.83 rows=100 width=4)

-> Group (cost=69.83..72.33 rows=1000 width=4)

-> Sort (cost=69.83..69.83 rows=1000 width=4)

-> Seq Scan on customers (cost=0.00..20.00 rows=1000 width=4)

注意，未分组的聚合值始终估计是1个，分组的聚合值个数估计是输入集大小的1/10.

Append

Append算子用于实现union。该算子将有2个或多个输入集。Append返回第一个输入集中的所有行，然后返回第2个输入集的所有行，以此类推，直到所有输入集中的所有行都处理。这是一个显示Append算子的查询计划：

perf=# EXPLAIN

perf-# SELECT * FROM recalls WHERE mfgname = 'FORD'

perf-# UNION

perf=# SELECT * FROM recalls WHERE yeartxt = '1983';

Unique

->Sort

->Append

->Subquery Scan *SELECT* 1

->Seq Scan on recalls

->Subquery Scan *SELECT* 2

->Seq Scan on recalls

Append算子的成本估算只是所有输入集的成本估算的总和。Append算子可以在处理所有输入行之前返回其第一行。每当遇到UNION子句时，规划器/优化器都会使用Append算子。当您从涉及继承层次结构的表中进行选择时，也会使用Append。

dvds表继承自video，tapes表也是。如果您从dvds或video中选择，PostgreSQL 将使用一个简单的查询计划进行响应：

movies=# EXPLAIN SELECT * FROM dvds;

Seq Scan on dvds (cost=0.00..20.00 rows=1000 width=122)

movies=# EXPLAIN SELECT * FROM tapes;

Seq Scan on tapes (cost=0.00..20.00 rows=1000 width=86)

请记住，由于继承层次结构。dvd是一个video，tape也是一个video。如果从video中select，预期看到素有dvds和所有tapes，所有videos。下面的查询计划反应了整个继承层次：

movies=# EXPLAIN SELECT * FROM video;

Result(cost=0.00..60.00 rows=3000 width=86)

->Append(cost=0.00..60.00 rows=3000 width=86)

->Seq Scan on video (cost=0.00..20.00 rows=1000 width=86)

->Seq Scan on tapes video (cost=0.00..20.00 rows=1000 width=86)

->Seq Scan on dvds video (cost=0.00..20.00 rows=1000 width=86)

仔细查看前面成本估算中的width。如果从dvds表中select，width是每行122个字节。如果从tapes表select，每行是86字节。如果从video，所有行都预期是86字节。下面是创建tapes和dvds表的命令：

movies=# CREATE TABLE tapes ( ) INHERITS( video );

movies=# CREATE TABLE dvds

movies-# (

movies(# region_id INTEGER,

movies(# audio_tracks VARCHAR[]

movies(# ) INHERITS ( video );

可以看到tapes表中的行和video中一样，期望他们都是相同的86字节。dvds表中包video所有列，再加上额外列，因此期望比video行大。当从video表select时，你想要所有videos。PG丢弃没有从video表继承的所有列。

Result

Result算子在3种情况下使用。

1）首先result算子用于执行不需要从表种检索数据的查询：

movies=# EXPLAIN SELECT timeofday();

Result

在这种形式种，Result算子只计算给定的表达式并返回结果。

2）WHERE子句不依赖表中任何数据，用于评估这样的WHERE：

movies=# EXPLAIN SELECT * FROM tapes WHERE 1 <> 1;

Result

->Seq Scan on tapes

这看起来像也给愚蠢的查询，但一些客户端应用程序会生成这种形式的查询，作为检索表的元数据的简单方法。在这种形式种，Result算子先计算WHERE子句的常量部分。如果表达式计算结果是false，则不需要进一步处理，并且算子运算完成。如果表达式计算结果true，则result算子将返回其输入集。

3）如果查询计划种顶部节点是Append算子，则计划器/优化器还会生成一个Reuslt算子。这是一个相当模糊的规则，对性能没有影响；它恰好使得PG开发任意维护查询计划器和执行器更简单些。

Nested Loop

Nexted loop算子用于在2个表之间执行连接。需要2个输入集（鉴于连接2个表，这很有意义）。工作原理是从一个输入集（外表）种获取每个元组，对于外表的每一行，在另一个输入（内表）种搜索满足连接条件的行。这是一个例子：

perf=# EXPLAIN

perf-# SELECT * FROM customers, rentals

perf=# WHERE customers.customer_id = rentals.customer_id;

Nested Loop

-> Seq Scan on rentals

-> Index Scan using customer_id on customers

外表在执行计划种总是列在最前面（rentals是外表）。为了执行这个执行计划，nested loop算子将读取rentals表中每一行，对于每个rentals 行，该算子使用一个索引customer_id读取customers种对应的行。实际上嵌套循环只读取那些满足查询条件的行。嵌套循环算子可用于执行内连接、左外连接和联合。因为不处理整个内表，所以它不能用于其他连接类型：全连接和右连接。

Merge Join

Merge Join算子也是连接2个表。需要2个输入集：一个外表和一个内表。每个输入集必须按连接列排序。看下前面的查询，这次作为Merge Join执行：

perf=# EXPLAIN

perf-# SELECT * FROM customers, rentals

perf=# WHERE customers.customer_id = rentals.customer_id;

Merge Join

-> Sort

-> Seq Scan on rentals

-> Index Scan using customer_id on customers

Merge Join开始从每个表种读取第一行：

如果连接列相当，如本例所示，Merge join会创建一个新行，其中包含来自每个输入表的必要列并返回新行。然后，merge join移动外表种的下一行，并将其与内表相应行连接：

接下来，merge join读取外表第3行：

现在merge join必须将内表推进2次，才能创建另一个结果行：

在为customer_id=3生成结果行后，merge join移动外表到最后一行，然后将内表推进到匹配行：

Merge join通过生成最终结果行（customer_id=4）来完成。您可以看到merge join的工作原理是遍历2个已排好序的表并找到匹配项。诀窍在于保持指针同步。此示例显示了一个内连接，但merge join算子可以通过以不同方式遍历排序的输入集来用于其他连接类型。Merge join可以做内连接、外连接、联合。

Hash和Hash Join

Hash和Hash Join算子一起工作。需要2个输入集，也称为外表和内表。这是一个使用hash join算子的查询计划：

movies=# EXPLAIN

movies-# SELECT * FROM customers, rentals

movies-# WHERE rentals.customer_id = customers.customer_id;

Hash Join

-> Seq Scan on customers

-> Hash

-> Seq Scan on rentals

与其他算子不同，hash join不需要任何一个输入集按连接列排序。相反，内表始终是hash表，外表的顺序不重要。首先使用Hash算法创建内表。Hash算子创建一个临时hash索引，该索引覆盖内表的连接列。创建hash表后，hash join会读取外表每一行，对连接列（从外表）进行hash，并在临时hash索引种搜索匹配值。Hash join算子可用于执行内连接、左外连接和联合。

Group

Group算子用于满足group by子句。Group算子需要一个输入集。并且必须按分组列排序。Group可以在两种不同模式下工作。如果正在计算分组聚合，group将返回其输入集种每一行，每个分组后面都右一个NULL行以指示该组结束（NULL不会显示在最终结果集种，仅用于内部标记）：

movies=# EXPLAIN

movies-# SELECT COUNT(*), EXTRACT( DECADE FROM birth_date )

movies-# FROM customers

movies-# GROUP BY EXTRACT( DECADE FROM birth_date );

NOTICE: QUERY PLAN:

Aggregate (cost=69.83..74.83 rows=100 width=4)

-> Group (cost=69.83..72.33 rows=1000 width=4)

-> Sort (cost=69.83..69.83 rows=1000 width=4)

-> Seq Scan on customers (cost=0.00..20.00 rows=1000 width=4)

注意，group算子的成本估算种的行数和输入集相同。如果不计算分组聚合，则group将为其输入集种每个组返回一行：

movies=# EXPLAIN

movies-# SELECT EXTRACT( DECADE FROM birth_date ) FROM customers

movies-# GROUP BY EXTRACT( DECADE FROM birth_date );

Group (cost=69.83..69,83 rows=100 width=4)

-> Sort (cost=69.83..69.83 rows=1000 width=4)

-> Seq Scan on customers (cost=0.00..20.00 rows=1000 width=4)

Group算子的评估行数是输入集的1/10.

Subquery Scan和Subplan

Subquery Scan算子用于union子句；subplan用于子选择。这些算子扫描他们的输入集，将每一行添加到结果集种。这些算子用于内部标记目的，不会影响整个查询计划，可以忽略。

为了让您知道何时可能使用它们，这里有两个示例查询计划：

perf=# EXPLAIN

perf-# SELECT * FROM recalls WHERE mfgname = 'FORD'

perf-# UNION

perf=# SELECT * FROM recalls WHERE yeartxt = '1983';

Unique

->Sort

->Append

->Subquery Scan *SELECT* 1

->Seq Scan on recalls

->Subquery Scan *SELECT* 2

->Seq Scan on recalls

movies=# EXPLAIN

movies-# SELECT * FROM customers

movies-# WHERE customer_id IN

movies-# (

movies(# SELECT customer_id FROM rentals

movies(# );

NOTICE: QUERY PLAN:

Seq Scan on customers (cost=0.00..3.66 rows=2 width=47)

SubPlan

-> Seq Scan on rentals (cost=0.00..1.04 rows=4 width=4)

Tid Scan

Tid Scan算子很少使用。一个元组大致相当于一行。每个元组都有一个在表中的唯一标识，元组ID。当选择一行时，可以询问该行的元组ID：

movies=# SELECT ctid, customer_id, customer_name FROM customers;

ctid | customer_id | customer_name

-------+-------------+----------------------

(0,1) | 1 | Jones, Henry

(0,2) | 2 | Rubin, William

(0,3) | 3 | Panky, Henry

(0,4) | 4 | Wonderland, Alice N.

(0,5) | 8 | Wink Wankel

“ctid”是一个特殊的列（类似于oid），自动称为每一行的一部分。元组ID由块号、块内的元组号组成。上一个示例种所有行都存储在块0（表文件的第一个块）种。customers种“panky,Henry”行存储在块0的元组3种。知道一行的元组ID后，可以使用其ID再次请求该行：

movies=# SELECT customer_id, customer_name FROM customers

movies-# WHERE ctid = '(0,3)';

customer_id | customer_name

-------------+---------------

3 | Panky, Henry

元组ID就像书签一样工作。但是仅在事务种有效。事务完成后，不应使用元组ID。每当计划器/优化器遇到ctid=expression或expression=ctid形式的约束时，都会使用Tid Scan算子。检索行最快的方式是通过其元组ID。当按元组ID选择时，Tid Scan算子读取元组ID指向的块并返回请求的元组。

物化

Materialize算子用于某些子选择操作。计划器/优化器可能觉得物化一个子选择一次比重复子选择工作代价要低。也可以用于一些merge join连接操作。特别是，如果merge join算子内部输入集不是由Seq Scan、Index Scan、Sort或Materialize算子生成，则计划器/优化器将在计划种插入物化算子。这个规则背后的原因并不明显--它更多与其他算子的能力有关而不是数据相关结构或者性能有关。Merge Join算子很复杂，要求输入集必须按连接列排序。另一个要求内部输入集必须可重新定位。也就是说merge join需要在输入集种前后移动。并非所有有序算子都可以前后启动。如果内部输入集由不可重新定位的算子生成，则计划器/优化器将插入Materialize算子。

Setop (Intersect, Intersect All, Except, Except All)

有4个Setop算子：Setop Intersect、Setop Intersect All、Setop except和Setop except All。仅当规划器/优化器分别遇到INTERSECT、INTERSECT ALL、EXCEPT或EXCEPT ALL子句时，才会生成这些算子。

所有Setop算子都需要两个输入集。Setop算子首先将输入集组合成一个排序列表，然后识别相同行的组。对于每个组，Setop算子计算每个输入集贡献的行数。最后，每个Setop算子使用计数来确定要添加到结果集中的行数。

我认为通过看一个例子会更容易理解。这是两个查询；第一个选择所有1960 年代出生的客户：

movies=# SELECT * FROM customers

movies-# WHERE EXTRACT( DECADE FROM birth_date ) = 196;

customer_id | customer_name | phone | birth_date | balance

-------------+----------------------+----------+------------+---------

3 | Panky, Henry | 555-1221 | 1968-01-21 | 0.00

4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 | 3.00

第二个选择balance大于0的所有行：

movies=# SELECT * FROM customers WHERE balance > 0;

customer_id | customer_name | phone | birth_date | balance

-------------+----------------------+----------+------------+---------

2 | Rubin, William | 555-2211 | 1972-07-10 | 15.00

4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 | 3.00

现在使用INTERSECT语句组合这2个查询：

movies=# EXPLAIN

movies-# SELECT * FROM customers

movies-# WHERE EXTRACT( DECADE FROM birth_date ) = 196

movies-# INTERSECT

movies-# SELECT * FROM customers WHERE balance > 0;

SetOp Intersect

-> Sort

-> Append

-> Subquery Scan *SELECT* 1

-> Seq Scan on customers

-> Subquery Scan *SELECT* 2

-> Seq Scan on customers

查询执行器首先执行两个子查询，然后将结果组合成一个排序列表。添加了一个额外的列，指示哪个输入集贡献了每一行：

customer_id | customer_name | birth_date | balance | input set

-------------+----------------------+------------+---------+----------

2 | Rubin, William | 1972-07-10 | 15.00 | inner

3 | Panky, Henry | 1968-01-21 | 0.00 | outer

4 | Wonderland, Alice N. | 1969-03-05 | 3.00 | outer

4 | Wonderland, Alice N. | 1969-03-05 | 3.00 | inner

SetOp运算符查找重复行组（忽略输入集伪列）。对于每个组，SetOp计算每个输入集贡献的行数。外部集贡献的行数称为count(outer)。内部结果集贡献的行数称为count(inner)。

以下是对每组进行计数后样本的外观：

customer_id | customer_name | birth_date | balance | input set

-------------+----------------------+------------+---------+----------

2 | Rubin, William | 1972-07-10 | 15.00 | inner

count(outer) = 0

count(inner) = 1

3 | Panky, Henry | 1968-01-21 | 0.00 | outer

count(outer) = 1

count(inner) = 0

4 | Wonderland, Alice N. | 1969-03-05 | 3.00 | outer

4 | Wonderland, Alice N. | 1969-03-05 | 3.00 | inner

count(outer) = 1

count(inner) = 1

第一组包含单行，由内部输入集提供。第二组包含单行，由外部输入集提供。最后一组包含两行，每一行由每个输入集贡献。

当SetOp到达一组重复行的末尾时，它根据以下规则确定要写入结果集中的副本数：

1）INTERSECT：如果count(outer) > 0且count(inner) > 0，则将该行的一份副本写入结果集；否则，该行不包含在结果集中。

2）INTERSECT ALL：如果count(outer) > 0且count(inner) > 0 ，则将该行的n 个副本写入结果集；其中n是较大的count(outer)和count(inner)。

3）EXCEPT：如果count(outer) > 0并且count(inner) = 0，则将该行的一份副本写入结果集。

4）EXCEPT ALL：如果count(inner) >= count(outer)，则将该行的n个副本写入结果集；其中n是count(outer) - count(inner)。

原文

https://etutorials.org/SQL/Postgresql/Part+I+General+PostgreSQL+Use/Chapter+4.+Performance/Understanding+How+PostgreSQL+Executes+a+Query/

yzs87

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
[译]理解PG如何执行一个查询-2

LimitLimit算子用于限制结果集的大小。PG使用limit算子进行limit和offset处理。Limit算子将输入集前x行去掉，返回接着的y行，再将剩下的丢弃。如果查询中包括offset，x表示offset的数量，否则x为0.如果查询中包含Limit，y表示limit数量，否则y是输入集的大小。输入集的顺序对LIMIT运算符并不重要，但它通常对整个查询计划很重要。......
复制链接

扫一扫