GaussDB(DWS)迁移 - hana迁移 -- 视图传参计算

CREATE TABLE total_mem_history (
    sampletime timestamptz,
    nodename text,
    sessid text,
    sesstype text,
    contextname text,
    level smallint,
    parent text,
    totalsize bigint,
    freesize bigint,
    usedsize bigint,
    ptotalsize text,
    pfreesize text,
    pusedsize text,
    freerate numeric(5,2)
)
WITH (orientation=row, compression=no)      
DISTRIBUTE BY ROUNDROBIN;

CREATE VIEW total_mem_history_last AS
SELECT * FROM total_mem_history
WHERE sampletime > now() - interval '1min'
AND nodename = read_global_var('my.node')::text; /*read_global_var函数的输出要显示转换为对应的类型*/

使用之前要先设置环境变量my.node，否则执行会报错。

postgres=# EXPLAIN PERFORMANCE SELECT * FROM total_mem_history_last;
ERROR:  unrecognized configuration parameter "my.node"

正确的执行方式需要在语句执行之前设置环境变量my.node，然后执行语句，具体如下

postgres=# SET my.node = 'dn_6001_6002';
SET
postgres=# EXPLAIN PERFORMANCE SELECT * FROM total_mem_history_last ORDER BY sampletime;
                                                                                                                                                                                                        QUERY PLAN                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  id |                   operation                    |     A-time     | A-rows | E-rows | E-distinct | Peak Memory  | E-memory | A-width | E-width | E-costs
 ----+------------------------------------------------+----------------+--------+--------+------------+--------------+----------+---------+---------+---------
   1 | ->  Streaming (type: GATHER)                   | 1.994          |      0 |      2 |            | 96KB         |          |         |     302 | 16.24
   2 |    ->  Sort                                    | [0.016, 0.017] |      0 |      2 |            | [40KB, 40KB] | 16MB     |         |     302 | 10.24
   3 |       ->  Seq Scan on public.total_mem_history | [0.001, 0.002] |      0 |      1 |            | [32KB, 32KB] | 1MB      |         |     302 | 10.22

                                                            RunTime Analyze Information
 -------------------------------------------------------------------------------------------------------------------------------------------------
         normal runtime analyze on "public.total_mem_history" times:13.592ms, stats:sync, change:0(0 in xact), alive:0.000000, threshold:50.000000

                                                     Predicate Information (identified by plan id)
 -----------------------------------------------------------------------------------------------------------------------------------------------------
   3 --Seq Scan on public.total_mem_history
         Filter: ((total_mem_history.nodename = read_global_var('my.node'::text)) AND (total_mem_history.sampletime > (now() - '00:01:00'::interval)))

           Memory Information (identified by plan id)
 ---------------------------------------------------------------
 Coordinator Query Peak Memory:
         Query Peak Memory: 0MB
 DataNode Query Peak Memory
         dn_6001_6002 Query Peak Memory: 0MB
         dn_6003_6004 Query Peak Memory: 0MB
   1 --Streaming (type: GATHER)
         Peak Memory: 96KB, Estimate Memory: 32768MB
   2 --Sort
         dn_6001_6002 Peak Memory: 40KB, Estimate Memory: 16MB
         dn_6003_6004 Peak Memory: 40KB, Estimate Memory: 16MB
         dn_6001_6002 Sort Method: quicksort  Memory: 25kB
         dn_6003_6004 Sort Method: quicksort  Memory: 25kB
   3 --Seq Scan on public.total_mem_history
         dn_6001_6002 Peak Memory: 32KB, Estimate Memory: 1024KB
         dn_6003_6004 Peak Memory: 32KB, Estimate Memory: 1024KB

                                                                                                                                                                                      Targetlist Information (identified by plan id)                                                                                    
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: total_mem_history.sampletime, total_mem_history.nodename, total_mem_history.sessid, total_mem_history.sesstype, total_mem_history.contextname, total_mem_history.level, total_mem_history.parent, total_mem_history.totalsize, total_mem_history.freesize, total_mem_history.usedsize, total_mem_history.ptotalsize, total_mem_history.pfreesize, total_mem_history.pusedsize, total_mem_history.freerate
         Merge Sort Key: total_mem_history.sampletime
         Node/s: All datanodes (group_version1, bucket:16384)
   2 --Sort
         Output: total_mem_history.sampletime, total_mem_history.nodename, total_mem_history.sessid, total_mem_history.sesstype, total_mem_history.contextname, total_mem_history.level, total_mem_history.parent, total_mem_history.totalsize, total_mem_history.freesize, total_mem_history.usedsize, total_mem_history.ptotalsize, total_mem_history.pfreesize, total_mem_history.pusedsize, total_mem_history.freerate
         Sort Key: total_mem_history.sampletime
   3 --Seq Scan on public.total_mem_history
         Output: total_mem_history.sampletime, total_mem_history.nodename, total_mem_history.sessid, total_mem_history.sesstype, total_mem_history.contextname, total_mem_history.level, total_mem_history.parent, total_mem_history.totalsize, total_mem_history.freesize, total_mem_history.usedsize, total_mem_history.ptotalsize, total_mem_history.pfreesize, total_mem_history.pusedsize, total_mem_history.freerate

                    Datanode Information (identified by plan id)
 ----------------------------------------------------------------------------------
   1 --Streaming (type: GATHER)
         (actual time=1.994..1.994 rows=0 loops=1)
         (Buffers: shared hit=4)
         (CPU: ex c/r=0, ex row=0, ex cyc=4784992, inc cyc=4784992)
   2 --Sort
         dn_6001_6002 (actual time=0.017..0.017 rows=0 loops=1)
         dn_6003_6004 (actual time=0.016..0.016 rows=0 loops=1)
         dn_6001_6002 (Buffers: 0)
         dn_6003_6004 (Buffers: 0)
         dn_6001_6002 (CPU: ex c/r=0, ex row=0, ex cyc=34844, inc cyc=39684)
         dn_6003_6004 (CPU: ex c/r=0, ex row=0, ex cyc=34022, inc cyc=37928)
   3 --Seq Scan on public.total_mem_history
         dn_6001_6002 (actual time=0.002..0.002 rows=0 loops=1) (filter time=0.000)
         dn_6003_6004 (actual time=0.001..0.001 rows=0 loops=1) (filter time=0.000)
         dn_6001_6002 (Buffers: 0)
         dn_6003_6004 (Buffers: 0)
         dn_6001_6002 (CPU: ex c/r=0, ex row=0, ex cyc=4840, inc cyc=4840)
         dn_6003_6004 (CPU: ex c/r=0, ex row=0, ex cyc=3906, inc cyc=3906)

                      User Define Profiling
 ----------------------------------------------------------------
 Plan Node id: 1  Track name: coordinator get datanode connection
       cn_5001 (time=0.002 total_calls=1 loops=1)
 Plan Node id: 1  Track name: coordinator begin transaction
       cn_5001 (time=0.003 total_calls=1 loops=1)
 Plan Node id: 1  Track name: coordinator send command
       cn_5001 (time=0.036 total_calls=2 loops=1)
 Plan Node id: 1  Track name: coordinator get the first tuple
       cn_5001 (time=0.198 total_calls=1 loops=1)