本文介绍Joining Relations Hint。

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

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

下表列出了Joining Relations Hint具体的使用语法。

提示 说明
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)