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)