本文介绍Joining Relations Hint。

要联接两个表时,有三种可能的计划可用于执行联接。

  • 嵌套循环联接 – 对于其他联接表中的每一行,扫描一次表。
  • 合并排序联接 – 在联接开始之前,每个表按联接属性排序。然后,并行扫描两个表,匹配的行将合并以形成联接行。
  • 哈希联接 – 扫描表并使用其联接属性作为哈希键将其联接属性加载到哈希表中。然后扫描另一个联接的表,其联接属性用作哈希键以查找第一个表的匹配行。

下表列出了可用于影响计划程序使用一种类型的联接计划而非另一种联接计划的optimizer hints。

表 1. Join Hints
提示 说明
USE_HASH(table [...]) 对table使用哈希联接。
NO_USE_HASH(table [...]) 不对table使用哈希联接。
USE_MERGE(table [...]) 对table使用合并排序联接。
NO_USE_MERGE(table [...]) 不对table使用合并排序联接。
USE_NL(table [...]) 对table使用嵌套循环联接。
NO_USE_NL(table [...]) 不对table使用嵌套循环联接。

示例

在以下示例中,USE_HASH hint用于 pgbench_branches 和 pgbench_accounts 表的联接。查询计划表明通过从 pgbench_branches 表的联接属性创建哈希表使用哈希联接。

EXPLAIN SELECT /*+ USE_HASH(b) */ b.bid, a.aid, abalance FROM pgbench_branches b, pgbench_accounts a WHERE b.bid = a.bid;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Join  (cost=21.45..81463.06 rows=2014215 width=12)
   Hash Cond: (a.bid = b.bid)
   ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=12)
   ->  Hash  (cost=21.20..21.20 rows=20 width=4)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
(5 rows)

接下来,NO_USE_HASH(a b) hint强制计划程序使用哈希表以外的方法。结果为合并联接。

EXPLAIN SELECT /*+ NO_USE_HASH(a b) */ b.bid, a.aid, abalance FROM pgbench_branches b, pgbench_accounts a WHERE b.bid = a.bid;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Merge Join  (cost=333526.08..368774.94 rows=2014215 width=12)
   Merge Cond: (b.bid = a.bid)
   ->  Sort  (cost=21.63..21.68 rows=20 width=4)
         Sort Key: b.bid
         ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
   ->  Materialize  (cost=333504.45..343575.53 rows=2014215 width=12)
         ->  Sort  (cost=333504.45..338539.99 rows=2014215 width=12)
               Sort Key: a.bid
               ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=12)
(9 rows)

最后,USE_MERGE hint强制计划程序使用合并联接。

EXPLAIN SELECT /*+ USE_MERGE(a) */ b.bid, a.aid, abalance FROM pgbench_branches b, pgbench_accounts a WHERE b.bid = a.bid;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Merge Join  (cost=333526.08..368774.94 rows=2014215 width=12)
   Merge Cond: (b.bid = a.bid)
   ->  Sort  (cost=21.63..21.68 rows=20 width=4)
         Sort Key: b.bid
         ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
   ->  Materialize  (cost=333504.45..343575.53 rows=2014215 width=12)
         ->  Sort  (cost=333504.45..338539.99 rows=2014215 width=12)
               Sort Key: a.bid
               ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=12)
(9 rows)

在这一三个表格的联接示例中,计划程序首先对 pgbench_branches 和 pgbench_history 表执行哈希联接,然后最终对结果和 pgbench_accounts 表执行哈希联接。

EXPLAIN SELECT h.mtime, h.delta, b.bid, a.aid FROM pgbench_history h, pgbench_branches b, pgbench_accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Join  (cost=86814.29..123103.29 rows=500000 width=20)
   Hash Cond: (h.aid = a.aid)
   ->  Hash Join  (cost=21.45..15081.45 rows=500000 width=20)
         Hash Cond: (h.bid = b.bid)
         ->  Seq Scan on pgbench_history h  (cost=0.00..8185.00 rows=500000 width=20)
         ->  Hash  (cost=21.20..21.20 rows=20 width=4)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
   ->  Hash  (cost=53746.15..53746.15 rows=2014215 width=4)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=4)
(9 rows)

此计划使用hints强制执行合并排序联接与哈希联接的组合进行更改。

EXPLAIN SELECT /*+ USE_MERGE(h b) USE_HASH(a) */ h.mtime, h.delta, b.bid, a.aid FROM pgbench_history h, pgbench_branches b, pgbench_accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Hash Join  (cost=152583.39..182562.49 rows=500000 width=20)
   Hash Cond: (h.aid = a.aid)
   ->  Merge Join  (cost=65790.55..74540.65 rows=500000 width=20)
         Merge Cond: (b.bid = h.bid)
         ->  Sort  (cost=21.63..21.68 rows=20 width=4)
               Sort Key: b.bid
               ->  Seq Scan on pgbench_branches b  (cost=0.00..21.20 rows=20 width=4)
         ->  Materialize  (cost=65768.92..68268.92 rows=500000 width=20)
               ->  Sort  (cost=65768.92..67018.92 rows=500000 width=20)
                     Sort Key: h.bid
                     ->  Seq Scan on pgbench_history h  (cost=0.00..8185.00 rows=500000 width=20)
   ->  Hash  (cost=53746.15..53746.15 rows=2014215 width=4)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..53746.15 rows=2014215 width=4)
(13 rows)