QUERYPLAN---NestedLoopSemiJoin(cost=0.00..51.84rows=7width=28)->SeqScanonemployeese(cost=0.00..27.00rows=7width=28)Filter:(dept_id=3)->SeqScanondepartments(cost=0.00..24.50rows=1450width=0)(4rows)emp_id|emp_name---+---4|David5|Eve(2rows) 返回的是左表employees中dept_id为3的所有员...
--值得关注的是join的方式是Nested Loop Semi Join,多了个Semi。Semi Join支持支持hash, merge, nestloop几种JOIN方法 --semi Join的操作在EXISTS中有一个返回TRUE的操作即可,所以在有索引的情况下很大概率下并不需要全表扫描 QUERY PLAN --- Aggregate (cost=5560.83..5560.84...
(actual time=5435.964..5454.953 rows=1000000 loops=1) Sort Key: a1.pk_dept Sort Method: quicksort Memory: 79264kB -> Nested Loop Semi Join (cost=1039.46..16098.38 rows=1 width=13) (actual time=0.389..5338.781 rows=1000000 loops=1) Join Filter: ((a1.pk_dept)::text = (t1.orgid)...
例如,PG 优化器选择最左边的 join order 时执行计划如下: postgres=# explain select * from A, B, C, D; QUERY PLAN --- Nested Loop (cost=0.00..8.39 rows=256 width=32) -> Nested Loop (cost=0.00..4.14 rows=64 width=24) -> Nested Loop (cost=0.00..2.29 rows=16 width=16) -> Seq...
.208.42 rows=0 width=0) -> Nested Loop Semi Join (cost=0.15..208.42 rows=367 width=46) -> Seq Scan on student a (cost=0.00..21.00 rows=1100 width=46) -> Index Only Scan using student_pkey on student b (cost=0.15..6.62 rows=367 width=4) Index Cond: (sno < a.sno) insert ...
首先我们看下图中JOIN对应的 SQL语句。 通过 explain 可以查看这6个SQL例子的JOIN类型。 (需要设置 enable_mergejoin, enable_hashagg 为 OFF,否则优化器可能会选择其他查询计划) 下图展示了查询结果,让我们对图中JOIN的作用有个直观的认知。 JOIN 有三种经典的实现算法:Nested Loop、Merge JOIN、Hash Join。他们各...
Delete on tbl_dup (cost=673139.27..683574.38 rows=1000000 width=36) -> Nested Loop (cost=673139.27..683574.38 rows=1000000 width=36) -> Unique (cost=673138.84..683088.84 rows=199 width=36) -> Sort (cost=673138.84..678113.84 rows=1990000 width=36) Sort Key: t.ctid -> Subquery Scan on ...
(c_ajbh);--查看执行计划Hash Semi Join (cost=13712.87..298118.93 rows=113055 width=33) Hash Cond: ((temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh)::text = (ajjbxx.c_bh)::text) -> Nested Loop Semi Join (cost=10074.62..260736.42 rows=113055 width=99) Join Filter: ((temp_cwczxx_db_...
pname = sname = "Nested Loop"; break; case T_MergeJoin: pname = "Merge"; /* "Join" gets added by jointype switch */ sname = "Merge Join"; break; case T_HashJoin: pname = "Hash"; /* "Join" gets added by jointype switch */ sname = "Hash Join"; break; case T_SeqScan...
Nested Loop (cost=0.00..85.89 rows=169 width=8) -- 外连接(Left关键字)已被消除 Output: a.c1, b.c1 -> Seq Scan on public.t_null1 a (cost=0.00..41.88 rows=13 width=4) Output: a.c1 Filter: (a.c1 = 1) -> Materialize (cost=0.00..41.94 rows=13 width=4) ...