前言

openGauss中的常见Join方式有Nested Loop，Hash Join和Merge Join，但Nested Loop分Nested Loop与Index nest loop，前者循环时不使用索引
如果两个表关联方式为循环，然后不使用索引，想当于多次全表查询，会对性能有很大影响

环境

orcl=> select version();
                                                                        version                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 6.0.1 build 84c20a90) compiled at 2025-01-17 17:31:02 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 10.3.0, 64-bit
(1 row)

现象

为了模拟Nested Loop性能问题，我们禁用Hash Join和Merge Join
如下所示，对表t_tables有多次访问，但没使用索引，两表关联后过滤
Join Filter: ((o.owner = t.owner) AND (o.object_name = t.table_name))

orcl=> set enable_hashjoin=off;
SET
orcl=> set enable_mergejoin=off;
SET
orcl=> EXPLAIN ANALYZE
orcl-> SELECT *
orcl->   FROM scott.t_objects o
orcl->  INNER JOIN scott.t_tables t
orcl->     ON t.owner = o.owner
orcl->    AND t.table_name = o.object_name;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2800336.57 rows=1371 width=1013) (actual time=0.051..28583.875 rows=2553 loops=1)
   Join Filter: ((o.owner = t.owner) AND (o.object_name = t.table_name))
   Rows Removed by Join Filter: 159850307
   ->  Seq Scan on t_objects o  (cost=0.00..2760.27 rows=73327 width=324) (actual time=0.006..36.659 rows=73327 loops=1)
   ->  Materialize  (cost=0.00..156.70 rows=2180 width=689) (actual time=4.475..6708.224 rows=159852860 loops=73327)
         ->  Seq Scan on t_tables t  (cost=0.00..145.80 rows=2180 width=689) (actual time=0.004..0.550 rows=2180 loops=1)
 Total runtime: 28584.747 ms

优化

方式一：Hint

orcl=> EXPLAIN ANALYZE
orcl-> SELECT /*+ set(enable_hashjoin on) set(enable_nestloop off) */ *
orcl->   FROM scott.t_objects o
orcl->  INNER JOIN scott.t_tables t
orcl->     ON t.owner = o.owner
orcl->    AND t.table_name = o.object_name;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=178.50..3685.75 rows=1371 width=1013) (actual time=2.039..32.035 rows=2553 loops=1)
   Hash Cond: ((o.owner = t.owner) AND (o.object_name = t.table_name))
   ->  Seq Scan on t_objects o  (cost=0.00..2760.27 rows=73327 width=324) (actual time=0.006..10.089 rows=73327 loops=1)
   ->  Hash  (cost=145.80..145.80 rows=2180 width=689) (actual time=1.865..1.865 rows=2180 loops=1)
          Buckets: 32768  Batches: 1  Memory Usage: 1020kB
         ->  Seq Scan on t_tables t  (cost=0.00..145.80 rows=2180 width=689) (actual time=0.004..0.512 rows=2180 loops=1)
 Total runtime: 32.448 ms
(7 rows)

方式二：更改参数

禁用 enable_nestloop，开启 enable_hashjoin

orcl=> set enable_nestloop = off;
SET
orcl=> set enable_hashjoin = on;
SET
orcl=> EXPLAIN ANALYZE
orcl-> SELECT *
orcl->   FROM scott.t_objects o
orcl->  INNER JOIN scott.t_tables t
orcl->     ON t.owner = o.owner
orcl->    AND t.table_name = o.object_name;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=178.50..3685.75 rows=1371 width=1013) (actual time=1.445..24.302 rows=2553 loops=1)
   Hash Cond: ((o.owner = t.owner) AND (o.object_name = t.table_name))
   ->  Seq Scan on t_objects o  (cost=0.00..2760.27 rows=73327 width=324) (actual time=0.004..8.017 rows=73327 loops=1)
   ->  Hash  (cost=145.80..145.80 rows=2180 width=689) (actual time=1.085..1.085 rows=2180 loops=1)
          Buckets: 32768  Batches: 1  Memory Usage: 1020kB
         ->  Seq Scan on t_tables t  (cost=0.00..145.80 rows=2180 width=689) (actual time=0.006..0.364 rows=2180 loops=1)
 Total runtime: 24.631 ms
(7 rows)

总结

Nest loop方式28583毫秒，改为hash后只有24毫秒，使用Nested loop 而非 Index nested loop 的Join方式大多会有严重性能问题，建议关闭enable_nestloop

set enable_nestloop = off;
set enable_hashjoin = on;
set enable_index_nestloop = on;