找不到将使用嵌套循环连接的表

Question

我觉得我会在这里得到很多反对票，但让我们试一试吧。

我试着用真实的例子向我的学生解释嵌套循环、散列和合并连接。但是，我很难找到可以使用嵌套循环连接的表(我尝试了许多不同的大小、索引设置等)。Postgres总是使用散列连接，而不管表的大小、索引等。

有没有人可以给出一个表(包含数据)的例子，这些表在没有事先显式运行set enable_hashjoin = true;的情况下会使用嵌套循环进行连接？

Answer 1

下面的代码为我在Postgres 10.5上执行了一个嵌套循环(没有禁用hashjoin

create table one (id integer primary key, some_ts timestamp, some_value integer);
insert into one values (1, clock_timestamp(), 42),(2, clock_timestamp(), 42);

create table two (id integer primary key, one_id integer not null references one, some_ts timestamp);
insert into two 
select i, 1, clock_timestamp()
from generate_series(1,10) i;

insert into two 
select i, 2, clock_timestamp()
from generate_series(11,20) i;

create index on two (one_id);

explain (analyze)
select one.*, two.id, two.some_ts
from one 
  join two on one.id = two.one_id
where one.id = 1;

结果如下：

QUERY PLAN                                                                                                         
-------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=0.15..4.23 rows=1 width=28) (actual time=0.029..0.033 rows=10 loops=1)                          
  ->  Index Scan using one_pkey on one  (cost=0.15..3.16 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=1)
        Index Cond: (id = 1)                                                                                       
  ->  Seq Scan on two  (cost=0.00..1.07 rows=1 width=16) (actual time=0.011..0.014 rows=10 loops=1)                
        Filter: (one_id = 1)                                                                                       
        Rows Removed by Filter: 10                                                                                 
Planning time: 0.130 ms                                                                                            
Execution time: 0.058 ms                                                                                           

在线示例：http://rextester.com/CXZZ12304

Answer 2

创建一些表：

CREATE TABLE a (
   a_id integer PRIMARY KEY,
   a_val text NOT NULL
);
CREATE TABLE b (
   b_id integer PRIMARY KEY,
   a_id integer REFERENCES a(a_id) NOT NULL,
   b_val text NOT NULL
);
-- *never* forget an index on the foreign key column
CREATE INDEX ON b(a_id);

添加一些示例数据：

INSERT INTO a
   SELECT i, 'value ' || i FROM generate_series(1, 1000) i;
INSERT INTO b
   SELECT i, (i + 1) / 2, 'value ' || i FROM generate_series(1, 2000) i;

分析表格以获得良好的统计数据：

ANALYZE a;
ANALYZE b;

让我们运行一个示例查询：

EXPLAIN SELECT a.a_val, b.b_val FROM a JOIN b USING (a_id) WHERE a_id = 42;

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Nested Loop  (cost=0.55..16.62 rows=2 width=19)
   ->  Index Scan using a_pkey on a  (cost=0.28..8.29 rows=1 width=13)
         Index Cond: (a_id = 42)
   ->  Index Scan using b_a_id_idx on b  (cost=0.28..8.31 rows=2 width=14)
         Index Cond: (a_id = 42)

(5行)