【openGauss/PostgreSQL】openGauss/PostgreSQL构造一个结果集的三种方法对比

    explain(analyze true,verbose true,costs true,buffers true,timing true,format text)
    with tmp as (
    select 'TZQ202407310001' as tzq_num union all
    select 'TZQ202407310002' as tzq_num union all
    select 'TZQ202407310003' as tzq_num union all
    select 'TZQ202407310004' as tzq_num union all
    select 'TZQ202407310005' as tzq_num union all
    select 'TZQ202407310006' as tzq_num union all
    select 'TZQ202407310007' as tzq_num union all
    select 'TZQ202407310008' as tzq_num union all
    select 'TZQ202407310009' as tzq_num union all
    select 'TZQ202407310010' as tzq_num)
    select tmp.tzq_num from tmp;
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
    
    代码解读

    Result  (cost=0.00..0.20 rows=10 width=32) (actual time=0.002..0.009 rows=10 loops=1)
      Output: ('TZQ202407310001'::text)
      ->  Append  (cost=0.00..0.20 rows=10 width=32) (actual time=0.001..0.007 rows=10 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310001'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310002'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310003'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310004'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310005'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310006'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310007'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
              Output: 'TZQ202407310008'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310009'::text
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
              Output: 'TZQ202407310010'::text
    Total runtime: 0.066 ms
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    
    代码解读

    -- SET explain_perf_mode=normal;
    explain(analyze true,verbose true,costs true,buffers true,timing true,format text)
    SELECT * 
      FROM (VALUES 
     ('TZQ202407310001')
    ,('TZQ202407310002')
    ,('TZQ202407310003')
    ,('TZQ202407310004')
    ,('TZQ202407310005')
    ,('TZQ202407310006')
    ,('TZQ202407310007')
    ,('TZQ202407310008')
    ,('TZQ202407310009')
    ,('TZQ202407310010')
    ) AS t;
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    
    代码解读

    Values Scan on "*VALUES*"  (cost=0.00..0.12 rows=10 width=32) (actual time=0.002..0.003 rows=10 loops=1)
      Output: "*VALUES*".column1
    Total runtime: 0.035 ms
    
    
      
      
      
    
    代码解读

    -- SET explain_perf_mode=normal;
    explain(analyze true,verbose true,costs true,buffers true,timing true,format text)
    with tmp as (
    select unnest(ARRAY[
     'TZQ202407310001'
    ,'TZQ202407310002'
    ,'TZQ202407310003'
    ,'TZQ202407310004'
    ,'TZQ202407310005'
    ,'TZQ202407310006'
    ,'TZQ202407310007'
    ,'TZQ202407310008'
    ,'TZQ202407310009'
    ,'TZQ202407310010']) as tzq_num)
    select tmp.tzq_num from tmp;
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    
    代码解读

    Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.012..0.015 rows=10 loops=1)
      Output: unnest('{TZQ202407310001,TZQ202407310002,TZQ202407310003,TZQ202407310004,TZQ202407310005,TZQ202407310006,TZQ202407310007,TZQ202407310008,TZQ202407310009,TZQ202407310010}'::text[])
    Total runtime: 0.051 ms
    
    
      
      
      
    
    代码解读