postgresql 数据库一个 sql 的几种写法分析

我不是女神ヾ 2022-12-16 11:59 165阅读 0赞

os: centos 7.8.2003  
db: postgresql 13.0

# in #

postgres=# explain ( analyze on ,verbose on ,timing on) 
    with tmp_t0 as (
        select 1 as id union all
    	select 2 as id union all
    	select 3 as id union all
    	select 4 as id
    ),tmp_t1 as (
        select 2 as id union all
    	select 4 as id union all
    	select 5 as id
    )
    select *
    from tmp_t1 t1
    where 1=1
    and t1.id in (select id from tmp_t0 t0)
    ;
    
                                                  QUERY PLAN                                              
    ------------------------------------------------------------------------------------------------------
     Hash Semi Join  (cost=0.11..0.20 rows=3 width=4) (actual time=0.017..0.023 rows=2 loops=1)
       Output: (2)
       Hash Cond: ((2) = (1))
       ->  Append  (cost=0.00..0.04 rows=3 width=4) (actual time=0.002..0.005 rows=3 loops=1)
             ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
                   Output: 2
             ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                   Output: 4
             ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                   Output: 5
       ->  Hash  (cost=0.06..0.06 rows=4 width=4) (actual time=0.008..0.009 rows=4 loops=1)
             Output: (1)
             Buckets: 1024  Batches: 1  Memory Usage: 9kB
             ->  Append  (cost=0.00..0.06 rows=4 width=4) (actual time=0.001..0.005 rows=4 loops=1)
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 1
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 2
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 3
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 4
     Planning Time: 0.390 ms
     Execution Time: 0.054 ms
    (24 rows)

# = any #

postgres=# explain ( analyze on ,verbose on ,timing on) 
    with tmp_t0 as (
        select 1 as id union all
    	select 2 as id union all
    	select 3 as id union all
    	select 4 as id
    ),tmp_t1 as (
        select 2 as id union all
    	select 4 as id union all
    	select 5 as id
    )
    select *
    from tmp_t1 t1
    where 1=1
    and t1.id = any(select id from tmp_t0 t0)
    ;
    
                                                  QUERY PLAN                                              
    ------------------------------------------------------------------------------------------------------
     Hash Semi Join  (cost=0.11..0.20 rows=3 width=4) (actual time=0.032..0.039 rows=2 loops=1)
       Output: (2)
       Hash Cond: ((2) = (1))
       ->  Append  (cost=0.00..0.04 rows=3 width=4) (actual time=0.007..0.010 rows=3 loops=1)
             ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
                   Output: 2
             ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                   Output: 4
             ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                   Output: 5
       ->  Hash  (cost=0.06..0.06 rows=4 width=4) (actual time=0.012..0.013 rows=4 loops=1)
             Output: (1)
             Buckets: 1024  Batches: 1  Memory Usage: 9kB
             ->  Append  (cost=0.00..0.06 rows=4 width=4) (actual time=0.001..0.005 rows=4 loops=1)
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 1
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 2
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 3
                   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                         Output: 4
     Planning Time: 0.458 ms
     Execution Time: 0.073 ms
    (24 rows)

# = any #

postgres=# explain ( analyze on ,verbose on ,timing on) 
    with tmp_t0 as (
        select 1 as id union all
    	select 2 as id union all
    	select 3 as id union all
    	select 4 as id
    ),tmp_t1 as (
        select 2 as id union all
    	select 4 as id union all
    	select 5 as id
    )
    select *
    from tmp_t1 t1
    where 1=1
    and t1.id = any((select array_agg(id) from tmp_t0 t0 )::integer[])
    ;
    
                                                   QUERY PLAN                                               
    --------------------------------------------------------------------------------------------------------
     Append  (cost=0.09..0.16 rows=3 width=4) (actual time=0.029..0.036 rows=2 loops=1)
       InitPlan 1 (returns $0)
         ->  Aggregate  (cost=0.07..0.08 rows=1 width=32) (actual time=0.021..0.022 rows=1 loops=1)
               Output: array_agg((1))
               ->  Append  (cost=0.00..0.06 rows=4 width=4) (actual time=0.001..0.006 rows=4 loops=1)
                     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                           Output: 1
                     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                           Output: 2
                     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                           Output: 3
                     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                           Output: 4
       ->  Result  (cost=0.01..0.02 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=1)
             Output: 2
             One-Time Filter: (2 = ANY ($0))
       ->  Result  (cost=0.01..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
             Output: 4
             One-Time Filter: (4 = ANY ($0))
       ->  Result  (cost=0.01..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
             Output: 5
             One-Time Filter: (5 = ANY ($0))
     Planning Time: 0.353 ms
     Execution Time: 0.085 ms
    (24 rows)

# = any #

postgres=# explain ( analyze on ,verbose on ,timing on) 
    with tmp_t0 as (
        select 1 as id union all
    	select 2 as id union all
    	select 3 as id union all
    	select 4 as id
    ),tmp_t1 as (
        select 2 as id union all
    	select 4 as id union all
    	select 5 as id
    )
    select *
    from tmp_t1 t1
    where 1=1
    and t1.id = any(array(select id from tmp_t0 t0))
    ;
    
                                                QUERY PLAN                                            
    --------------------------------------------------------------------------------------------------
     Append  (cost=0.07..0.14 rows=3 width=4) (actual time=0.011..0.016 rows=2 loops=1)
       InitPlan 1 (returns $0)
         ->  Append  (cost=0.00..0.06 rows=4 width=4) (actual time=0.001..0.004 rows=4 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                     Output: 1
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)
                     Output: 2
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)
                     Output: 3
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)
                     Output: 4
       ->  Result  (cost=0.01..0.02 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)
             Output: 2
             One-Time Filter: (2 = ANY ($0))
       ->  Result  (cost=0.01..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
             Output: 4
             One-Time Filter: (4 = ANY ($0))
       ->  Result  (cost=0.01..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
             Output: 5
             One-Time Filter: (5 = ANY ($0))
     Planning Time: 0.251 ms
     Execution Time: 0.035 ms
    (22 rows)