postgresql 数据库执行计划 Nested Loop

小鱼儿 2023-02-13 11:08 13阅读 0赞

os: centos 7.4  
db: postgresql 10.11

**Nested Loop** 比较适合两个表的数据量都比较少的情况(最简单的 table join 方式)

1.  外层驱动表为小表，且过滤后的数据量较少。
2.  内层被驱动表的关联列上**最好有高效索引**(主键或者唯一性索引)。

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # su - postgres
    $
    $ psql -c "select version();"
                                                     version                                                  
    ----------------------------------------------------------------------------------------------------------
     PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# create table #

$ psql
    psql (10.11)
    Type "help" for help.
    
    postgres=# 
    postgres=# drop table if exists tmp_t4;
    drop table if exists tmp_t5;
    
    
    postgres=# create table tmp_t4( 
    	id    int8 primary key,
    	name  varchar(100)
    );
    
    create table tmp_t5( 
    	id    int8 primary key,
    	name  varchar(100)
    );
    
    postgres=# insert into tmp_t4 
    select id,
           md5(id::varchar)
      from generate_series(1,1000000) as id;
    
    insert into tmp_t5 
    select id,
           md5(id::varchar)
      from generate_series(1,1000000) as id;
    
    postgres=# vacuum analyze tmp_t4;
    vacuum analyze tmp_t5;

# Nested Loop #

postgres=# set max_parallel_workers_per_gather=0;
    
    postgres=# explain analyze 
    select t4.*,t5.*
      from tmp_t4 t4,
           tmp_t5 t5
     where 1=1
       and t4.id = t5.id
       and t4.id = 499999
    ;
    
                                                              QUERY PLAN                                                          
    ------------------------------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=0.85..16.90 rows=1 width=82) (actual time=0.015..0.016 rows=1 loops=1)
       ->  Index Scan using tmp_t4_pkey on tmp_t4 t4  (cost=0.42..8.44 rows=1 width=41) (actual time=0.008..0.008 rows=1 loops=1)
             Index Cond: (id = 499999)
       ->  Index Scan using tmp_t5_pkey on tmp_t5 t5  (cost=0.42..8.44 rows=1 width=41) (actual time=0.006..0.007 rows=1 loops=1)
             Index Cond: (id = 499999)
     Planning time: 0.079 ms
     Execution time: 0.032 ms
    (7 rows)

执行计划显示： tmp\_t4 作为驱动表 通过 Nested Loop 方式 探测 tmp\_t5 数据，类似 for 循环

for(t4.data in tmp_t4) 
     { 
    	t5.data in tmp_t5 on t5.data = t4.data
     }

参考: