postgresql - 如何理解 PostgreSQL 中的嵌套循环解释?
问题描述
请回答,非常感谢。
Q1:为什么查询条件是a.id = b.id,但开头只扫描了a.id的索引?但是循环的数量这么大?
Q2:解释中的“嵌套循环”节点有什么作用?
happydb=# EXPLAIN (ANALYZE,VERBOSE) SELECT b.name FROM a,b WHERE a.id = b.id AND b.id < 10000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
-------------------
Gather (cost=1000.57..174222.54 rows=18002 width=13) (actual time=5.881..3276.311 rows=19998 loops=1)
Output: b.name
Workers Planned: 5
Workers Launched: 5
-> Nested Loop (cost=0.56..171422.34 rows=3600 width=13) (actual time=3.189..3258.998 rows=3333 loops=6)
Output: b.name
Worker 0: actual time=2.591..3259.895 rows=1850 loops=1
Worker 1: actual time=0.180..3251.631 rows=4081 loops=1
Worker 2: actual time=1.344..3261.433 rows=555 loops=1
Worker 3: actual time=8.603..3262.411 rows=3330 loops=1
Worker 4: actual time=0.821..3259.297 rows=4623 loops=1
-> Parallel Seq Scan on public.b (cost=0.00..141721.20 rows=3600 width=17) (actual time=1.020..3223.285 rows=3333 loops
=6)
Output: b.id, b.name
Filter: (b.id < 10000)
Rows Removed by Filter: 2663334
Worker 0: actual time=0.054..3237.921 rows=1850 loops=1
Worker 1: actual time=0.049..3215.862 rows=4081 loops=1
Worker 2: actual time=0.102..3236.592 rows=555 loops=1
Worker 3: actual time=0.296..3235.327 rows=3330 loops=1
Worker 4: actual time=0.055..3188.732 rows=4623 loops=1
-> Index Only Scan using idx_rock_id on public.a (cost=0.56..8.24 rows=1 width=4) (actual time=0.008..0.010 r
ows=1 loops=19998)
Output: a.id
Index Cond: (a.id = b.id)
Heap Fetches: 19998
Worker 0: actual time=0.011..0.011 rows=1 loops=1850
Worker 1: actual time=0.008..0.008 rows=1 loops=4081
Worker 2: actual time=0.044..0.044 rows=1 loops=555
Worker 3: actual time=0.007..0.007 rows=1 loops=3330
Worker 4: actual time=0.006..0.015 rows=1 loops=4623
Planning Time: 0.579 ms
Execution Time: 3277.727 ms
(31 rows)
最后,根据 Laurenz Albe 的指导,添加索引后的执行计划要好很多,执行时间也大大减少了。
music=# CREATE INDEX idx_b_id ON b(id);
WARNING: concurrent insert in progress within table "b"
CREATE INDEX
music=# set max_parallel_workers_per_gather = 6;
SET
music=# EXPLAIN (ANALYZE,VERBOSE) SELECT b.name FROM a,b WHERE a.id = b.id AND b.id < 10000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
-------------------
Gather (cost=1388.64..108804.88 rows=20599 width=13) (actual time=1.316..35.770 rows=19998 loops=1)
Output: b.name
Workers Planned: 3
Workers Launched: 3
-> Nested Loop (cost=388.64..105744.98 rows=6645 width=13) (actual time=0.341..27.475 rows=5000 loops=4)
Output: b.name
Worker 0: actual time=0.095..26.337 rows=5365 loops=1
Worker 1: actual time=0.091..26.641 rows=5753 loops=1
Worker 2: actual time=0.101..28.578 rows=3145 loops=1
-> Parallel Bitmap Heap Scan on public.b (cost=388.08..51229.82 rows=6645 width=17) (actual time=0.298..1.265 rows=5000 loops=4)
Output: b.id, b.name
Recheck Cond: (b.id < 10000)
Heap Blocks: exact=31
Worker 0: actual time=0.044..1.111 rows=5365 loops=1
Worker 1: actual time=0.044..1.205 rows=5753 loops=1
Worker 2: actual time=0.049..0.681 rows=3145 loops=1
-> Bitmap Index Scan on idx_b_id (cost=0.00..382.93 rows=20599 width=0) (actual time=1.012..1.012 rows=19998 loops=1)
Index Cond: (b.id < 10000)
-> Index Only Scan using idx_para_select_id on public.a (cost=0.56..8.19 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=19998)
Output: a.id
Index Cond: (a.id = b.id)
Heap Fetches: 19998
Worker 0: actual time=0.004..0.004 rows=1 loops=5365
Worker 1: actual time=0.003..0.004 rows=1 loops=5753
Worker 2: actual time=0.004..0.004 rows=1 loops=3145
Planning Time: 0.264 ms
Execution Time: 37.025 ms
(27 rows)
解决方案
嵌套循环连接的工作方式如下:
在这种情况下,PostgreSQL 会扫描外部表
b
。对于在外部表中找到的每一行,PostgreSQL 扫描内部表,在这种情况下
a
,用于匹配行。由于内部表的连接条件上有一个索引,因此 PostgreSQL 在那里使用索引扫描。
因此,对于嵌套循环连接,只能使用内表上连接条件的索引。
外表的扫描还有一个条件(b.id < 10000
),但与join无关。您似乎在该列上没有索引,因此使用了顺序扫描。
内部循环的大量执行可以通过发现扫描外部表的 19998 行来解释:对于这些行中的每一行,都会扫描内部表。
几乎所有的执行时间都花在了并行顺序扫描上,并且大部分行都被丢弃了,所以我假设以下索引将使查询变得更快:
CREATE INDEX ON b (id);
推荐阅读
- swift - SwiftUI 无法创建导航视图
- android - 我将为此 StoreBuilder 使用什么样的密钥
- c# - CsvWriter,CS1503 参数 2:无法转换 CultureInfo
- r - R中同名data.frame的子集
- javascript - 错误:函数组件不能有引用。你的意思是使用 React.forwardRef() 吗?
- c - 从 Rust 进行 FFI 时如何处理 C 宏?
- reactjs - 无论如何使用 npm start 从前缀 URL(例如 /client/static)提供 /static?
- firebase - Firebase 动态链接在运行 iOS13.3 的设备上无法正常工作,但在模拟器 (iOS13.2) 和运行 iOS12 的 iPhone 上正常工作
- javascript - 然后阻止中的意外令牌
- arrays - VB代码选择除前两个以外的所有工作表