GaussDB(DWS)性能调优：json_object_field_text获同一字多个key值的优化方法
【摘要】 针对同一个字段多次调用json_object_field_text获取不同key的value值的场景，提供了一个简单的性能优化方法
1. 原始语句性能分析
原始SQL
SELECT
vin,
acq_time,
json_object_field_text(can_data::json,'PMS_Ig') AS PMS_Ig ,
json_object_field_text(can_data::json,'BMS_Ch') AS BMS_Ch ,
json_object_field_text(can_data::json,'IC_To') AS IC_To ,
json_object_field_text(can_data::json,'BMS_Ba') AS BMS_Ba ,
json_object_field_text(can_data::json,'MCU_Mo') AS MCU_Mo ,
json_object_field_text(can_data::json,'MCU_MC') AS MCU_MC ,
json_object_field_text(can_data::json,'PMS_Ve') AS PMS_Ve ,
json_object_field_text(can_data::json,'MCU_MS') AS MCU_MS ,
json_object_field_text(can_data::json,'MCU_MA') AS MCU_MA ,
json_object_field_text(can_data::json,'GCU_MD') AS GCU_MD ,
json_object_field_text(can_data::json,'BMS_BS') AS BMS_BS ,
json_object_field_text(can_data::json,'BMS_SO') AS BMS_SO ,
json_object_field_text(can_data::json,'BMS_Bl') AS BMS_Bl ,
json_object_field_text(can_data::json,'BMS_BC') AS BMS_BC
FROM dwd.dwd_h8m_h7eh8e_can t
WHERE t.acq_time>='2024-07-15' AND t.acq_time<'2024-07-16';详细的执行信息，分析执行信息发现表scan慢
- 进一步分析详细的performance，发现projection部分（projection time=15299.727）耗时慢，也就输出列计算慢。
- 分析输出列发现字段can_data上存在14个json_object_field_text调用
- 获取字段can_data的样本数据，发现长度在20KB左右
推测是因为can_data字段往json的类型转换（14次）以及函数json_object_field_text调用比较耗时（14次），需要尝试降低这部分的开销
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+------------------------------------------------------------------------+------------------------+--------+----------+------------+----------------+----------+---------+---------+------------
1 | -> Row Adapter | 16826.563 | 100000 | 100000 | | 148KB | | | 1062 | 2448.25
2 | -> Vector Limit | 16807.103 | 100000 | 100000 | | 8KB | | | 1062 | 2448.25
3 | -> Vector Streaming (type: GATHER) | 16806.734 | 100445 | 600000 | | 991KB | | | 1062 | 14669.53
4 | -> Vector Limit | [16992.606, 17602.429] | 113445 | 600000 | | [8KB, 8KB] | 1MB | | 1062 | 11644.02
5 | -> Vector Partition Iterator | [16992.540, 17602.352] | 113445 | 76858492 | | [32KB, 32KB] | 1MB | | 1062 | 1491569.97
6 | -> Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can t | [16992.354, 17602.151] | 113445 | 76858492 | | [308MB, 326MB] | 1MB | | 1062 | 146546.32
Predicate Information (identified by plan id)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --Vector Partition Iterator
Iterations: 1
6 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can t
Filter: ((t.acq_time >= '2024-07-15 00:00:00'::timestamp without time zone) AND (t.acq_time < '2024-07-16 00:00:00'::timestamp without time zone))
Pushdown Predicate Filter: ((t.acq_time >= '2024-07-15 00:00:00'::timestamp without time zone) AND (t.acq_time < '2024-07-16 00:00:00'::timestamp without time zone))
Partitions Selected by Static Prune: 7
Memory Information (identified by plan id)
-------------------------------------------------------------------------
Coordinator Query Peak Memory:
Query Peak Memory: 8MB
DataNode Query Peak Memory
dn_6001_6002 Query Peak Memory: 310MB
dn_6003_6004 Query Peak Memory: 310MB
dn_6005_6006 Query Peak Memory: 327MB
dn_6007_6008 Query Peak Memory: 328MB
dn_6009_6010 Query Peak Memory: 316MB
dn_6011_6012 Query Peak Memory: 324MB
1 --Row Adapter
Peak Memory: 148KB, Estimate Memory: 2048MB
2 --Vector Limit
Peak Memory: 8KB
3 --Vector Streaming (type: GATHER)
Peak Memory: 991KB
4 --Vector Limit
dn_6001_6002 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6003_6004 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6005_6006 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6007_6008 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6009_6010 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6011_6012 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6001_6002 Stream Send time: 3.254; Data Serialize time: 15.733
dn_6003_6004 Stream Send time: 2.271; Data Serialize time: 16.494
dn_6005_6006 Stream Send time: 1.143; Data Serialize time: 14.785
dn_6007_6008 Stream Send time: 1.217; Data Serialize time: 15.060
dn_6009_6010 Stream Send time: 1.255; Data Serialize time: 15.085
dn_6011_6012 Stream Send time: 1.243; Data Serialize time: 14.796
5 --Vector Partition Iterator
dn_6001_6002 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6003_6004 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6005_6006 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6007_6008 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6009_6010 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6011_6012 Peak Memory: 32KB, Estimate Memory: 1024KB
6 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can t
dn_6001_6002 Peak Memory: 316413KB, Estimate Memory: 1024KB
dn_6003_6004 Peak Memory: 316281KB, Estimate Memory: 1024KB
dn_6005_6006 Peak Memory: 334130KB, Estimate Memory: 1024KB
dn_6007_6008 Peak Memory: 334669KB, Estimate Memory: 1024KB
dn_6009_6010 Peak Memory: 322605KB, Estimate Memory: 1024KB
dn_6011_6012 Peak Memory: 330674KB, Estimate Memory: 1024KB
Targetlist Information (identified by plan id)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 --Row Adapter
Output: vin, acq_time, (json_object_field_text((can_data)::json, 'PMS_Ig'::text)), (json_object_field_text((can_data)::json, 'BMS_Ch'::text)), (json_object_field_text((can_data)::json, 'IC_To'::text)), (json_object_field_text((can_data)::json, 'BMS_Ba'::text)), (json_object_field_text((can_data)::json, 'MCU_Mo'::text)), (json_object_field_text((can_data)::json, 'MCU_MC'::text)), (json_object_field_text((can_data)::json, 'PMS_Ve'::text)), (json_object_field_text((can_data)::json, 'MCU_MS'::text)), (json_object_field_text((can_data)::json, 'MCU_MA'::text)), (json_object_field_text((can_data)::json, 'GCU_MD'::text)), (json_object_field_text((can_data)::json, 'BMS_BS'::text)), (json_object_field_text((can_data)::json, 'BMS_SO'::text)), (json_object_field_text((can_data)::json, 'BMS_Bl'::text)), (json_object_field_text((can_data)::json, 'BMS_BC'::text))
2 --Vector Limit
Output: vin, acq_time, (json_object_field_text((can_data)::json, 'PMS_Ig'::text)), (json_object_field_text((can_data)::json, 'BMS_Ch'::text)), (json_object_field_text((can_data)::json, 'IC_To'::text)), (json_object_field_text((can_data)::json, 'BMS_Ba'::text)), (json_object_field_text((can_data)::json, 'MCU_Mo'::text)), (json_object_field_text((can_data)::json, 'MCU_MC'::text)), (json_object_field_text((can_data)::json, 'PMS_Ve'::text)), (json_object_field_text((can_data)::json, 'MCU_MS'::text)), (json_object_field_text((can_data)::json, 'MCU_MA'::text)), (json_object_field_text((can_data)::json, 'GCU_MD'::text)), (json_object_field_text((can_data)::json, 'BMS_BS'::text)), (json_object_field_text((can_data)::json, 'BMS_SO'::text)), (json_object_field_text((can_data)::json, 'BMS_Bl'::text)), (json_object_field_text((can_data)::json, 'BMS_BC'::text))
3 --Vector Streaming (type: GATHER)
Output: vin, acq_time, (json_object_field_text((can_data)::json, 'PMS_Ig'::text)), (json_object_field_text((can_data)::json, 'BMS_Ch'::text)), (json_object_field_text((can_data)::json, 'IC_To'::text)), (json_object_field_text((can_data)::json, 'BMS_Ba'::text)), (json_object_field_text((can_data)::json, 'MCU_Mo'::text)), (json_object_field_text((can_data)::json, 'MCU_MC'::text)), (json_object_field_text((can_data)::json, 'PMS_Ve'::text)), (json_object_field_text((can_data)::json, 'MCU_MS'::text)), (json_object_field_text((can_data)::json, 'MCU_MA'::text)), (json_object_field_text((can_data)::json, 'GCU_MD'::text)), (json_object_field_text((can_data)::json, 'BMS_BS'::text)), (json_object_field_text((can_data)::json, 'BMS_SO'::text)), (json_object_field_text((can_data)::json, 'BMS_Bl'::text)), (json_object_field_text((can_data)::json, 'BMS_BC'::text))
Node/s: All datanodes
4 --Vector Limit
Output: vin, acq_time, (json_object_field_text((can_data)::json, 'PMS_Ig'::text)), (json_object_field_text((can_data)::json, 'BMS_Ch'::text)), (json_object_field_text((can_data)::json, 'IC_To'::text)), (json_object_field_text((can_data)::json, 'BMS_Ba'::text)), (json_object_field_text((can_data)::json, 'MCU_Mo'::text)), (json_object_field_text((can_data)::json, 'MCU_MC'::text)), (json_object_field_text((can_data)::json, 'PMS_Ve'::text)), (json_object_field_text((can_data)::json, 'MCU_MS'::text)), (json_object_field_text((can_data)::json, 'MCU_MA'::text)), (json_object_field_text((can_data)::json, 'GCU_MD'::text)), (json_object_field_text((can_data)::json, 'BMS_BS'::text)), (json_object_field_text((can_data)::json, 'BMS_SO'::text)), (json_object_field_text((can_data)::json, 'BMS_Bl'::text)), (json_object_field_text((can_data)::json, 'BMS_BC'::text))
5 --Vector Partition Iterator
Output: vin, acq_time, json_object_field_text((can_data)::json, 'PMS_Ig'::text), json_object_field_text((can_data)::json, 'BMS_Ch'::text), json_object_field_text((can_data)::json, 'IC_To'::text), json_object_field_text((can_data)::json, 'BMS_Ba'::text), json_object_field_text((can_data)::json, 'MCU_Mo'::text), json_object_field_text((can_data)::json, 'MCU_MC'::text), json_object_field_text((can_data)::json, 'PMS_Ve'::text), json_object_field_text((can_data)::json, 'MCU_MS'::text), json_object_field_text((can_data)::json, 'MCU_MA'::text), json_object_field_text((can_data)::json, 'GCU_MD'::text), json_object_field_text((can_data)::json, 'BMS_BS'::text), json_object_field_text((can_data)::json, 'BMS_SO'::text), json_object_field_text((can_data)::json, 'BMS_Bl'::text), json_object_field_text((can_data)::json, 'BMS_BC'::text)
6 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can t
Output: vin, acq_time, json_object_field_text((can_data)::json, 'PMS_Ig'::text), json_object_field_text((can_data)::json, 'BMS_Ch'::text), json_object_field_text((can_data)::json, 'IC_To'::text), json_object_field_text((can_data)::json, 'BMS_Ba'::text), json_object_field_text((can_data)::json, 'MCU_Mo'::text), json_object_field_text((can_data)::json, 'MCU_MC'::text), json_object_field_text((can_data)::json, 'PMS_Ve'::text), json_object_field_text((can_data)::json, 'MCU_MS'::text), json_object_field_text((can_data)::json, 'MCU_MA'::text), json_object_field_text((can_data)::json, 'GCU_MD'::text), json_object_field_text((can_data)::json, 'BMS_BS'::text), json_object_field_text((can_data)::json, 'BMS_SO'::text), json_object_field_text((can_data)::json, 'BMS_Bl'::text), json_object_field_text((can_data)::json, 'BMS_BC'::text)
Distribute Key: vin, acq_time
Datanode Information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 --Row Adapter
(actual time=1811.854..16826.563 rows=100000 loops=1)
(CPU: ex c/r=15, ex row=100000, ex cyc=1525774, inc cyc=1682209510)
2 --Vector Limit
(actual time=1811.730..16807.103 rows=100000 loops=1)
(CPU: ex c/r=0, ex row=100000, ex cyc=37459, inc cyc=1680683736)
3 --Vector Streaming (type: GATHER)
(actual time=1811.723..16806.734 rows=100445 loops=1)
(Buffers: shared hit=1)
(CPU: ex c/r=16732, ex row=100445, ex cyc=1680646277, inc cyc=1680646277)
4 --Vector Limit
dn_6001_6002 (actual time=1513.554..17513.662 rows=18272 loops=1)
dn_6003_6004 (actual time=1196.983..17301.490 rows=19006 loops=1)
dn_6005_6006 (actual time=1210.535..17073.324 rows=19000 loops=1)
dn_6007_6008 (actual time=1363.734..17567.276 rows=19059 loops=1)
dn_6009_6010 (actual time=1187.904..16992.606 rows=18734 loops=1)
dn_6011_6012 (actual time=1211.220..17602.429 rows=19374 loops=1)
dn_6001_6002 (CPU: ex c/r=0, ex row=18272, ex cyc=6971, inc cyc=1751338239)
dn_6003_6004 (CPU: ex c/r=0, ex row=19006, ex cyc=6707, inc cyc=1730121848)
dn_6005_6006 (CPU: ex c/r=0, ex row=19000, ex cyc=7273, inc cyc=1707300086)
dn_6007_6008 (CPU: ex c/r=0, ex row=19059, ex cyc=6608, inc cyc=1756694578)
dn_6009_6010 (CPU: ex c/r=0, ex row=18734, ex cyc=6686, inc cyc=1699239916)
dn_6011_6012 (CPU: ex c/r=0, ex row=19374, ex cyc=7235, inc cyc=1760221198)
5 --Vector Partition Iterator
dn_6001_6002 (actual time=1513.546..17513.588 rows=18272 loops=1)
dn_6003_6004 (actual time=1196.975..17301.425 rows=19006 loops=1)
dn_6005_6006 (actual time=1210.526..17073.248 rows=19000 loops=1)
dn_6007_6008 (actual time=1363.726..17567.208 rows=19059 loops=1)
dn_6009_6010 (actual time=1187.898..16992.540 rows=18734 loops=1)
dn_6011_6012 (actual time=1211.213..17602.352 rows=19374 loops=1)
dn_6001_6002 (CPU: ex c/r=1, ex row=18272, ex cyc=23825, inc cyc=1751331268)
dn_6003_6004 (CPU: ex c/r=1, ex row=19006, ex cyc=19782, inc cyc=1730115141)
dn_6005_6006 (CPU: ex c/r=1, ex row=19000, ex cyc=20605, inc cyc=1707292813)
dn_6007_6008 (CPU: ex c/r=1, ex row=19059, ex cyc=21376, inc cyc=1756687970)
dn_6009_6010 (CPU: ex c/r=1, ex row=18734, ex cyc=18894, inc cyc=1699233230)
dn_6011_6012 (CPU: ex c/r=1, ex row=19374, ex cyc=20510, inc cyc=1760213963)
6 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can t
LLVM Optimized
dn_6001_6002 (actual time=1513.396..17513.351 rows=18272 loops=1) (filter time=0.107 projection time=15299.727) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59533, totalDeadRows: 0)
dn_6003_6004 (actual time=1196.852..17301.223 rows=19006 loops=1) (filter time=0.099 projection time=15252.721) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59555, totalDeadRows: 0)
dn_6005_6006 (actual time=1210.401..17073.047 rows=19000 loops=1) (filter time=0.095 projection time=15079.983) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59555, totalDeadRows: 0)
dn_6007_6008 (actual time=1363.600..17566.999 rows=19059 loops=1) (filter time=0.105 projection time=15345.704) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59556, totalDeadRows: 0)
dn_6009_6010 (actual time=1187.787..16992.354 rows=18734 loops=1) (filter time=0.117 projection time=15011.768) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59561, totalDeadRows: 0)
dn_6011_6012 (actual time=1211.097..17602.151 rows=19374 loops=1) (filter time=0.106 projection time=15555.788) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59550, totalDeadRows: 0)
dn_6001_6002 (Buffers: shared hit=40 read=13)
dn_6003_6004 (Buffers: shared hit=40 read=13)
dn_6005_6006 (Buffers: shared hit=40 read=13 written=1)
dn_6007_6008 (Buffers: shared hit=40 read=13)
dn_6009_6010 (Buffers: shared hit=40 read=13)
dn_6011_6012 (Buffers: shared hit=40 read=13)
dn_6001_6002 (CStore Buffers: shared hit=51 read=6)
dn_6003_6004 (CStore Buffers: shared hit=54 read=6)
dn_6005_6006 (CStore Buffers: shared hit=51 read=6)
dn_6007_6008 (CStore Buffers: shared hit=54 read=6)
dn_6009_6010 (CStore Buffers: shared hit=51 read=6)
dn_6011_6012 (CStore Buffers: shared hit=54 read=6)
dn_6001_6002 (CPU: ex c/r=95846, ex row=18272, ex cyc=1751307443, inc cyc=1751307443)
dn_6003_6004 (CPU: ex c/r=91028, ex row=19006, ex cyc=1730095359, inc cyc=1730095359)
dn_6005_6006 (CPU: ex c/r=89856, ex row=19000, ex cyc=1707272208, inc cyc=1707272208)
dn_6007_6008 (CPU: ex c/r=92169, ex row=19059, ex cyc=1756666594, inc cyc=1756666594)
dn_6009_6010 (CPU: ex c/r=90702, ex row=18734, ex cyc=1699214336, inc cyc=1699214336)
dn_6011_6012 (CPU: ex c/r=90853, ex row=19374, ex cyc=1760193453, inc cyc=1760193453)
User Define Profiling
-------------------------------------------------------------------------
Plan Node id: 3 Track name: coordinator get datanode connection
cn_5001 (time=0.023 total_calls=1 loops=1)
Plan Node id: 3 Track name: coordinator begin transaction
cn_5001 (time=0.003 total_calls=1 loops=1)
Plan Node id: 3 Track name: coordinator send command
cn_5001 (time=0.145 total_calls=6 loops=1)
Plan Node id: 3 Track name: coordinator get the first tuple
cn_5001 (time=16.136 total_calls=11 loops=1)
Plan Node id: 3 Track name: coordinator handle data from all connections
cn_5001 (time=17.262 total_calls=303 loops=1)
Plan Node id: 6 Track name: load CU description
dn_6001_6002 (time=0.478 total_calls=19 loops=1)
dn_6003_6004 (time=0.466 total_calls=20 loops=1)
dn_6005_6006 (time=0.552 total_calls=19 loops=1)
dn_6007_6008 (time=0.403 total_calls=20 loops=1)
dn_6009_6010 (time=0.432 total_calls=19 loops=1)
dn_6011_6012 (time=0.455 total_calls=20 loops=1)
Plan Node id: 6 Track name: min/max check
dn_6001_6002 (time=0.017 total_calls=19 loops=1)
dn_6003_6004 (time=0.012 total_calls=20 loops=1)
dn_6005_6006 (time=0.016 total_calls=19 loops=1)
dn_6007_6008 (time=0.012 total_calls=20 loops=1)
dn_6009_6010 (time=0.013 total_calls=19 loops=1)
dn_6011_6012 (time=0.015 total_calls=20 loops=1)
Plan Node id: 6 Track name: fill vector batch
dn_6001_6002 (time=153.968 total_calls=19 loops=1)
dn_6003_6004 (time=153.576 total_calls=20 loops=1)
dn_6005_6006 (time=262.433 total_calls=19 loops=1)
dn_6007_6008 (time=351.067 total_calls=20 loops=1)
dn_6009_6010 (time=171.260 total_calls=19 loops=1)
dn_6011_6012 (time=140.640 total_calls=20 loops=1)
Plan Node id: 6 Track name: get CU data
dn_6001_6002 (time=153.020 total_calls=19 loops=1)
dn_6003_6004 (time=153.166 total_calls=20 loops=1)
dn_6005_6006 (time=262.062 total_calls=19 loops=1)
dn_6007_6008 (time=350.683 total_calls=20 loops=1)
dn_6009_6010 (time=170.877 total_calls=19 loops=1)
dn_6011_6012 (time=140.249 total_calls=20 loops=1)
Plan Node id: 6 Track name: uncompress CU data
dn_6001_6002 (time=824.737 total_calls=6 loops=1)
dn_6003_6004 (time=830.728 total_calls=6 loops=1)
dn_6005_6006 (time=751.962 total_calls=6 loops=1)
dn_6007_6008 (time=757.941 total_calls=6 loops=1)
dn_6009_6010 (time=790.272 total_calls=6 loops=1)
dn_6011_6012 (time=789.675 total_calls=6 loops=1)
Plan Node id: 6 Track name: apply projection and filter
dn_6001_6002 (time=17358.637 total_calls=19 loops=1)
dn_6003_6004 (time=17146.923 total_calls=20 loops=1)
dn_6005_6006 (time=16809.805 total_calls=19 loops=1)
dn_6007_6008 (time=17215.258 total_calls=20 loops=1)
dn_6009_6010 (time=16820.321 total_calls=19 loops=1)
dn_6011_6012 (time=17460.810 total_calls=20 loops=1)
Plan Node id: 6 Track name: fill later vector batch
dn_6001_6002 (time=1492.940 total_calls=19 loops=1)
dn_6003_6004 (time=1339.380 total_calls=20 loops=1)
dn_6005_6006 (time=1183.658 total_calls=19 loops=1)
dn_6007_6008 (time=1300.543 total_calls=20 loops=1)
dn_6009_6010 (time=1251.519 total_calls=19 loops=1)
dn_6011_6012 (time=1333.769 total_calls=20 loops=1)
Plan Node id: 6 Track name: get cu data for later read
dn_6001_6002 (time=1476.197 total_calls=38 loops=1)
dn_6003_6004 (time=1325.341 total_calls=40 loops=1)
dn_6005_6006 (time=1169.378 total_calls=38 loops=1)
dn_6007_6008 (time=1284.860 total_calls=40 loops=1)
dn_6009_6010 (time=1236.233 total_calls=38 loops=1)
dn_6011_6012 (time=1317.646 total_calls=40 loops=1)
====== Query Summary =====
------------------------------------------------------------------------------------
Datanode executor start time [dn_6011_6012, dn_6001_6002]: [3.009 ms,5.311 ms]
Datanode executor run time [dn_6009_6010, dn_6011_6012]: [17009.214 ms,17618.714 ms]
Datanode executor end time [dn_6009_6010, dn_6003_6004]: [28.822 ms,36.064 ms]
Remote query poll time: 16765.292 ms, Deserialze time: 15.682 ms
System available mem: 21560524KB
Query Max mem: 22364160KB
Query estimated mem: 3072KB
Initial DOP: 1
Avail(CPU/IO)/Max core: (0.09/0.12)/16.00
CPU/IO/Task util: 93.00/0.00/0
Running/Active/Max statement: 4/21/21474836
Final DOP: 1
Enqueue time: 0.005 ms
Coordinator executor start time: 1.802 ms
Coordinator executor run time: 16836.487 ms
Coordinator executor end time: 854.147 ms
Parser runtime: 0.137 ms
Planner runtime: 0.849 ms
Query Id: 74309395329874572
Total runtime: 17693.440 ms
(236 rows)2. SQL优化
尝试把can_data字段往json的类型转换和函数json_object_field_text的调用拆开，把can_data字段往json的类型转换作为子查询，然后父查询中调用json_object_field_text函数，从而避免can_data字段往json的类型的多次转换
优化后SQL如下
SELECT /*+ no merge(t)*/
vin,
acq_time,
jsonb_object_field_text(can_data, 'PMS_Ig') AS PMS_Ig ,
jsonb_object_field_text(can_data, 'BMS_Ch') AS BMS_Ch ,
jsonb_object_field_text(can_data, 'IC_To') AS IC_To ,
jsonb_object_field_text(can_data, 'BMS_Ba') AS BMS_Ba ,
jsonb_object_field_text(can_data, 'MCU_Mo') AS MCU_Mo ,
jsonb_object_field_text(can_data, 'MCU_MC') AS MCU_MC ,
jsonb_object_field_text(can_data, 'PMS_Ve') AS PMS_Ve ,
jsonb_object_field_text(can_data, 'MCU_MS') AS MCU_MS ,
jsonb_object_field_text(can_data, 'MCU_MA') AS MCU_MA ,
jsonb_object_field_text(can_data, 'GCU_MD') AS GCU_MD ,
jsonb_object_field_text(can_data, 'BMS_BS') AS BMS_BS ,
jsonb_object_field_text(can_data, 'BMS_SO') AS BMS_SO ,
jsonb_object_field_text(can_data, 'BMS_Bl') AS BMS_Bl ,
jsonb_object_field_text(can_data, 'BMS_BC') AS BMS_BC
FROM (SELECT vin, acq_time, can_data::jsonb AS can_data from dwd.dwd_h8m_h7eh8e_can) t
WHERE t.acq_time>='2024-07-15' AND t.acq_time<'2024-07-16';优化后执行时间下降到不到4s，具体的执行信息如下
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+-------------------------------------------------------------------------+----------------------+--------+----------+------------+--------------+----------+---------+---------+-----------
1 | -> Row Adapter | 3570.961 | 100000 | 100000 | | 148KB | | | 58 | 1088.11
2 | -> Vector Limit | 3552.765 | 100000 | 100000 | | 8KB | | | 58 | 1088.11
3 | -> Vector Streaming (type: GATHER) | 3552.361 | 100445 | 600000 | | 991KB | | | 58 | 6508.68
4 | -> Vector Limit | [3569.571, 3638.850] | 114445 | 600000 | | [8KB, 8KB] | 1MB | | 58 | 6144.02
5 | -> Vector Subquery Scan on t | [3569.500, 3638.778] | 114445 | 76858492 | | [3MB, 3MB] | 1MB | | 58 | 787033.77
6 | -> Vector Partition Iterator | [3466.127, 3534.138] | 114445 | 76858492 | | [32KB, 32KB] | 1MB | | 1062 | 210595.07
7 | -> Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can | [3465.865, 3533.926] | 114445 | 76858492 | | [48MB, 53MB] | 1MB | | 1062 | 146546.32
Predicate Information (identified by plan id)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 --Vector Partition Iterator
Iterations: 1
7 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can
Filter: ((dwd_h8m_h7eh8e_can.acq_time >= '2024-07-15 00:00:00'::timestamp without time zone) AND (dwd_h8m_h7eh8e_can.acq_time < '2024-07-16 00:00:00'::timestamp without time zone))
Pushdown Predicate Filter: ((dwd_h8m_h7eh8e_can.acq_time >= '2024-07-15 00:00:00'::timestamp without time zone) AND (dwd_h8m_h7eh8e_can.acq_time < '2024-07-16 00:00:00'::timestamp without time zone))
Partitions Selected by Static Prune: 7
Memory Information (identified by plan id)
------------------------------------------------------------------------
Coordinator Query Peak Memory:
Query Peak Memory: 7MB
DataNode Query Peak Memory
dn_6001_6002 Query Peak Memory: 53MB
dn_6003_6004 Query Peak Memory: 53MB
dn_6005_6006 Query Peak Memory: 56MB
dn_6007_6008 Query Peak Memory: 58MB
dn_6009_6010 Query Peak Memory: 57MB
dn_6011_6012 Query Peak Memory: 55MB
1 --Row Adapter
Peak Memory: 148KB, Estimate Memory: 2048MB
2 --Vector Limit
Peak Memory: 8KB
3 --Vector Streaming (type: GATHER)
Peak Memory: 991KB
4 --Vector Limit
dn_6001_6002 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6003_6004 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6005_6006 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6007_6008 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6009_6010 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6011_6012 Peak Memory: 8KB, Estimate Memory: 1024KB
dn_6001_6002 Stream Send time: 2.425; Data Serialize time: 9.378
dn_6003_6004 Stream Send time: 3.924; Data Serialize time: 6.680
dn_6005_6006 Stream Send time: 0.967; Data Serialize time: 5.191
dn_6007_6008 Stream Send time: 1.301; Data Serialize time: 6.325
dn_6009_6010 Stream Send time: 1.506; Data Serialize time: 7.087
dn_6011_6012 Stream Send time: 1.321; Data Serialize time: 6.306
5 --Vector Subquery Scan on t
dn_6001_6002 Peak Memory: 4041KB, Estimate Memory: 1024KB
dn_6003_6004 Peak Memory: 4041KB, Estimate Memory: 1024KB
dn_6005_6006 Peak Memory: 4041KB, Estimate Memory: 1024KB
dn_6007_6008 Peak Memory: 4041KB, Estimate Memory: 1024KB
dn_6009_6010 Peak Memory: 4041KB, Estimate Memory: 1024KB
dn_6011_6012 Peak Memory: 4041KB, Estimate Memory: 1024KB
6 --Vector Partition Iterator
dn_6001_6002 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6003_6004 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6005_6006 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6007_6008 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6009_6010 Peak Memory: 32KB, Estimate Memory: 1024KB
dn_6011_6012 Peak Memory: 32KB, Estimate Memory: 1024KB
7 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can
dn_6001_6002 Peak Memory: 49677KB, Estimate Memory: 1024KB
dn_6003_6004 Peak Memory: 49494KB, Estimate Memory: 1024KB
dn_6005_6006 Peak Memory: 51989KB, Estimate Memory: 1024KB
dn_6007_6008 Peak Memory: 54828KB, Estimate Memory: 1024KB
dn_6009_6010 Peak Memory: 53719KB, Estimate Memory: 1024KB
dn_6011_6012 Peak Memory: 51280KB, Estimate Memory: 1024KB
Targetlist Information (identified by plan id)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 --Row Adapter
Output: t.vin, t.acq_time, (jsonb_object_field_text(t.can_data, 'PMS_Ig'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ch'::text)), (jsonb_object_field_text(t.can_data, 'IC_To'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ba'::text)), (jsonb_object_field_text(t.can_data, 'MCU_Mo'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MC'::text)), (jsonb_object_field_text(t.can_data, 'PMS_Ve'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MS'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MA'::text)), (jsonb_object_field_text(t.can_data, 'GCU_MD'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BS'::text)), (jsonb_object_field_text(t.can_data, 'BMS_SO'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Bl'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BC'::text))
2 --Vector Limit
Output: t.vin, t.acq_time, (jsonb_object_field_text(t.can_data, 'PMS_Ig'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ch'::text)), (jsonb_object_field_text(t.can_data, 'IC_To'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ba'::text)), (jsonb_object_field_text(t.can_data, 'MCU_Mo'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MC'::text)), (jsonb_object_field_text(t.can_data, 'PMS_Ve'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MS'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MA'::text)), (jsonb_object_field_text(t.can_data, 'GCU_MD'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BS'::text)), (jsonb_object_field_text(t.can_data, 'BMS_SO'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Bl'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BC'::text))
3 --Vector Streaming (type: GATHER)
Output: t.vin, t.acq_time, (jsonb_object_field_text(t.can_data, 'PMS_Ig'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ch'::text)), (jsonb_object_field_text(t.can_data, 'IC_To'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ba'::text)), (jsonb_object_field_text(t.can_data, 'MCU_Mo'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MC'::text)), (jsonb_object_field_text(t.can_data, 'PMS_Ve'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MS'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MA'::text)), (jsonb_object_field_text(t.can_data, 'GCU_MD'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BS'::text)), (jsonb_object_field_text(t.can_data, 'BMS_SO'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Bl'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BC'::text))
Node/s: All datanodes
4 --Vector Limit
Output: t.vin, t.acq_time, (jsonb_object_field_text(t.can_data, 'PMS_Ig'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ch'::text)), (jsonb_object_field_text(t.can_data, 'IC_To'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Ba'::text)), (jsonb_object_field_text(t.can_data, 'MCU_Mo'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MC'::text)), (jsonb_object_field_text(t.can_data, 'PMS_Ve'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MS'::text)), (jsonb_object_field_text(t.can_data, 'MCU_MA'::text)), (jsonb_object_field_text(t.can_data, 'GCU_MD'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BS'::text)), (jsonb_object_field_text(t.can_data, 'BMS_SO'::text)), (jsonb_object_field_text(t.can_data, 'BMS_Bl'::text)), (jsonb_object_field_text(t.can_data, 'BMS_BC'::text))
5 --Vector Subquery Scan on t
Output: t.vin, t.acq_time, jsonb_object_field_text(t.can_data, 'PMS_Ig'::text), jsonb_object_field_text(t.can_data, 'BMS_Ch'::text), jsonb_object_field_text(t.can_data, 'IC_To'::text), jsonb_object_field_text(t.can_data, 'BMS_Ba'::text), jsonb_object_field_text(t.can_data, 'MCU_Mo'::text), jsonb_object_field_text(t.can_data, 'MCU_MC'::text), jsonb_object_field_text(t.can_data, 'PMS_Ve'::text), jsonb_object_field_text(t.can_data, 'MCU_MS'::text), jsonb_object_field_text(t.can_data, 'MCU_MA'::text), jsonb_object_field_text(t.can_data, 'GCU_MD'::text), jsonb_object_field_text(t.can_data, 'BMS_BS'::text), jsonb_object_field_text(t.can_data, 'BMS_SO'::text), jsonb_object_field_text(t.can_data, 'BMS_Bl'::text), jsonb_object_field_text(t.can_data, 'BMS_BC'::text)
6 --Vector Partition Iterator
Output: dwd_h8m_h7eh8e_can.vin, dwd_h8m_h7eh8e_can.acq_time, (dwd_h8m_h7eh8e_can.can_data)::jsonb
7 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can
Output: dwd_h8m_h7eh8e_can.vin, dwd_h8m_h7eh8e_can.acq_time, (dwd_h8m_h7eh8e_can.can_data)::jsonb
Distribute Key: dwd_h8m_h7eh8e_can.vin, dwd_h8m_h7eh8e_can.acq_time
Datanode Information (identified by plan id)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 --Row Adapter
(actual time=432.003..3570.961 rows=100000 loops=1)
(CPU: ex c/r=14, ex row=100000, ex cyc=1400831, inc cyc=356670863)
2 --Vector Limit
(actual time=431.882..3552.765 rows=100000 loops=1)
(CPU: ex c/r=0, ex row=100000, ex cyc=40317, inc cyc=355270032)
3 --Vector Streaming (type: GATHER)
(actual time=431.874..3552.361 rows=100445 loops=1)
(Buffers: shared hit=1)
(CPU: ex c/r=3536, ex row=100445, ex cyc=355229715, inc cyc=355229715)
4 --Vector Limit
dn_6001_6002 (actual time=354.267..3619.120 rows=20272 loops=1)
dn_6003_6004 (actual time=463.758..3634.597 rows=20006 loops=1)
dn_6005_6006 (actual time=668.670..3623.577 rows=15000 loops=1)
dn_6007_6008 (actual time=418.364..3569.571 rows=19059 loops=1)
dn_6009_6010 (actual time=391.297..3618.566 rows=20734 loops=1)
dn_6011_6012 (actual time=535.419..3638.850 rows=19374 loops=1)
dn_6001_6002 (CPU: ex c/r=0, ex row=20272, ex cyc=7864, inc cyc=361906490)
dn_6003_6004 (CPU: ex c/r=0, ex row=20006, ex cyc=7238, inc cyc=363453369)
dn_6005_6006 (CPU: ex c/r=0, ex row=15000, ex cyc=6049, inc cyc=362350806)
dn_6007_6008 (CPU: ex c/r=0, ex row=19059, ex cyc=7113, inc cyc=356950269)
dn_6009_6010 (CPU: ex c/r=0, ex row=20734, ex cyc=7913, inc cyc=361852039)
dn_6011_6012 (CPU: ex c/r=0, ex row=19374, ex cyc=7272, inc cyc=363880340)
5 --Vector Subquery Scan on t
dn_6001_6002 (actual time=354.259..3619.044 rows=20272 loops=1) (projection time=114.775)
dn_6003_6004 (actual time=463.750..3634.522 rows=20006 loops=1) (projection time=111.116)
dn_6005_6006 (actual time=668.662..3623.517 rows=15000 loops=1) (projection time=94.534)
dn_6007_6008 (actual time=418.356..3569.500 rows=19059 loops=1) (projection time=103.018)
dn_6009_6010 (actual time=391.289..3618.486 rows=20734 loops=1) (projection time=115.947)
dn_6011_6012 (actual time=535.411..3638.778 rows=19374 loops=1) (projection time=104.297)
dn_6001_6002 (CPU: ex c/r=567, ex row=20272, ex cyc=11514224, inc cyc=361898626)
dn_6003_6004 (CPU: ex c/r=557, ex row=20006, ex cyc=11148185, inc cyc=363446131)
dn_6005_6006 (CPU: ex c/r=631, ex row=15000, ex cyc=9479543, inc cyc=362344757)
dn_6007_6008 (CPU: ex c/r=542, ex row=19059, ex cyc=10336823, inc cyc=356943156)
dn_6009_6010 (CPU: ex c/r=561, ex row=20734, ex cyc=11631976, inc cyc=361844126)
dn_6011_6012 (CPU: ex c/r=540, ex row=19374, ex cyc=10463736, inc cyc=363873068)
6 --Vector Partition Iterator
dn_6001_6002 (actual time=347.409..3503.901 rows=20272 loops=1)
dn_6003_6004 (actual time=456.398..3523.038 rows=20006 loops=1)
dn_6005_6006 (actual time=661.274..3528.720 rows=15000 loops=1)
dn_6007_6008 (actual time=412.372..3466.127 rows=19059 loops=1)
dn_6009_6010 (actual time=384.710..3502.164 rows=20734 loops=1)
dn_6011_6012 (actual time=528.705..3534.138 rows=19374 loops=1)
dn_6001_6002 (CPU: ex c/r=1, ex row=20272, ex cyc=22781, inc cyc=350384402)
dn_6003_6004 (CPU: ex c/r=0, ex row=20006, ex cyc=19002, inc cyc=352297946)
dn_6005_6006 (CPU: ex c/r=1, ex row=15000, ex cyc=18193, inc cyc=352865214)
dn_6007_6008 (CPU: ex c/r=1, ex row=19059, ex cyc=26676, inc cyc=346606333)
dn_6009_6010 (CPU: ex c/r=1, ex row=20734, ex cyc=22443, inc cyc=350212150)
dn_6011_6012 (CPU: ex c/r=1, ex row=19374, ex cyc=21274, inc cyc=353409332)
7 --Partitioned CStore Scan on dwd.dwd_h8m_h7eh8e_can
LLVM Optimized
dn_6001_6002 (actual time=347.287..3503.676 rows=20272 loops=1) (filter time=0.096 projection time=2118.923) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59533, totalDeadRows: 0)
dn_6003_6004 (actual time=456.287..3522.850 rows=20006 loops=1) (filter time=0.075 projection time=2064.484) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59555, totalDeadRows: 0)
dn_6005_6006 (actual time=661.154..3528.539 rows=15000 loops=1) (filter time=0.080 projection time=1749.674) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59555, totalDeadRows: 0)
dn_6007_6008 (actual time=412.184..3465.865 rows=19059 loops=1) (filter time=0.072 projection time=2024.891) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59556, totalDeadRows: 0)
dn_6009_6010 (actual time=384.568..3501.940 rows=20734 loops=1) (filter time=0.086 projection time=2131.091) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59561, totalDeadRows: 0)
dn_6011_6012 (actual time=528.581..3533.926 rows=19374 loops=1) (filter time=0.069 projection time=2001.491) (RoughCheck CU: CUNone: 0, CUTagNone: 0, CUSome: 126) (CU ScanInfo: smallCu: 1, totalCu: 126, avrCuRow: 59550, totalDeadRows: 0)
dn_6001_6002 (Buffers: shared hit=40 read=13)
dn_6003_6004 (Buffers: shared hit=51 read=2)
dn_6005_6006 (Buffers: shared hit=40 read=13)
dn_6007_6008 (Buffers: shared hit=40 read=13)
dn_6009_6010 (Buffers: shared hit=40 read=13)
dn_6011_6012 (Buffers: shared hit=40 read=13)
dn_6001_6002 (CStore Buffers: shared hit=57 read=6)
dn_6003_6004 (CStore Buffers: shared hit=57 read=6)
dn_6005_6006 (CStore Buffers: shared hit=39 read=6)
dn_6007_6008 (CStore Buffers: shared hit=54 read=6)
dn_6009_6010 (CStore Buffers: shared hit=57 read=6)
dn_6011_6012 (CStore Buffers: shared hit=54 read=6)
dn_6001_6002 (CPU: ex c/r=17283, ex row=20272, ex cyc=350361621, inc cyc=350361621)
dn_6003_6004 (CPU: ex c/r=17608, ex row=20006, ex cyc=352278944, inc cyc=352278944)
dn_6005_6006 (CPU: ex c/r=23523, ex row=15000, ex cyc=352847021, inc cyc=352847021)
dn_6007_6008 (CPU: ex c/r=18184, ex row=19059, ex cyc=346579657, inc cyc=346579657)
dn_6009_6010 (CPU: ex c/r=16889, ex row=20734, ex cyc=350189707, inc cyc=350189707)
dn_6011_6012 (CPU: ex c/r=18240, ex row=19374, ex cyc=353388058, inc cyc=353388058)
User Define Profiling
-------------------------------------------------------------------------
Plan Node id: 3 Track name: coordinator get datanode connection
cn_5001 (time=0.024 total_calls=1 loops=1)
Plan Node id: 3 Track name: coordinator begin transaction
cn_5001 (time=0.003 total_calls=1 loops=1)
Plan Node id: 3 Track name: coordinator send command
cn_5001 (time=0.128 total_calls=6 loops=1)
Plan Node id: 3 Track name: coordinator get the first tuple
cn_5001 (time=1.354 total_calls=3 loops=1)
Plan Node id: 3 Track name: coordinator handle data from all connections
cn_5001 (time=16.577 total_calls=265 loops=1)
Plan Node id: 7 Track name: load CU description
dn_6001_6002 (time=0.498 total_calls=21 loops=1)
dn_6003_6004 (time=0.378 total_calls=21 loops=1)
dn_6005_6006 (time=0.501 total_calls=15 loops=1)
dn_6007_6008 (time=0.468 total_calls=20 loops=1)
dn_6009_6010 (time=0.454 total_calls=21 loops=1)
dn_6011_6012 (time=0.453 total_calls=20 loops=1)
Plan Node id: 7 Track name: min/max check
dn_6001_6002 (time=0.012 total_calls=21 loops=1)
dn_6003_6004 (time=0.016 total_calls=21 loops=1)
dn_6005_6006 (time=0.012 total_calls=15 loops=1)
dn_6007_6008 (time=0.015 total_calls=20 loops=1)
dn_6009_6010 (time=0.013 total_calls=21 loops=1)
dn_6011_6012 (time=0.013 total_calls=20 loops=1)
Plan Node id: 7 Track name: fill vector batch
dn_6001_6002 (time=159.303 total_calls=21 loops=1)
dn_6003_6004 (time=170.993 total_calls=21 loops=1)
dn_6005_6006 (time=242.887 total_calls=15 loops=1)
dn_6007_6008 (time=150.363 total_calls=20 loops=1)
dn_6009_6010 (time=123.807 total_calls=21 loops=1)
dn_6011_6012 (time=192.567 total_calls=20 loops=1)
Plan Node id: 7 Track name: get CU data
dn_6001_6002 (time=158.917 total_calls=21 loops=1)
dn_6003_6004 (time=170.669 total_calls=21 loops=1)
dn_6005_6006 (time=242.577 total_calls=15 loops=1)
dn_6007_6008 (time=149.991 total_calls=20 loops=1)
dn_6009_6010 (time=123.431 total_calls=21 loops=1)
dn_6011_6012 (time=192.226 total_calls=20 loops=1)
Plan Node id: 7 Track name: uncompress CU data
dn_6001_6002 (time=719.850 total_calls=6 loops=1)
dn_6003_6004 (time=720.024 total_calls=6 loops=1)
dn_6005_6006 (time=894.282 total_calls=6 loops=1)
dn_6007_6008 (time=731.974 total_calls=6 loops=1)
dn_6009_6010 (time=721.026 total_calls=6 loops=1)
dn_6011_6012 (time=699.418 total_calls=6 loops=1)
Plan Node id: 7 Track name: apply projection and filter
dn_6001_6002 (time=3343.691 total_calls=21 loops=1)
dn_6003_6004 (time=3351.306 total_calls=21 loops=1)
dn_6005_6006 (time=3285.019 total_calls=15 loops=1)
dn_6007_6008 (time=3314.862 total_calls=20 loops=1)
dn_6009_6010 (time=3377.502 total_calls=21 loops=1)
dn_6011_6012 (time=3340.742 total_calls=20 loops=1)
Plan Node id: 7 Track name: fill later vector batch
dn_6001_6002 (time=1177.574 total_calls=21 loops=1)
dn_6003_6004 (time=1245.262 total_calls=21 loops=1)
dn_6005_6006 (time=1500.927 total_calls=15 loops=1)
dn_6007_6008 (time=1246.968 total_calls=20 loops=1)
dn_6009_6010 (time=1200.303 total_calls=21 loops=1)
dn_6011_6012 (time=1297.791 total_calls=20 loops=1)
Plan Node id: 7 Track name: get cu data for later read
dn_6001_6002 (time=1160.766 total_calls=42 loops=1)
dn_6003_6004 (time=1229.770 total_calls=42 loops=1)
dn_6005_6006 (time=1489.044 total_calls=30 loops=1)
dn_6007_6008 (time=1223.290 total_calls=40 loops=1)
dn_6009_6010 (time=1185.372 total_calls=42 loops=1)
dn_6011_6012 (time=1283.729 total_calls=40 loops=1)
====== Query Summary =====
----------------------------------------------------------------------------------
Datanode executor start time [dn_6001_6002, dn_6003_6004]: [2.982 ms,7.478 ms]
Datanode executor run time [dn_6007_6008, dn_6011_6012]: [3577.436 ms,3646.709 ms]
Datanode executor end time [dn_6005_6006, dn_6001_6002]: [2.791 ms,4.071 ms]
Remote query poll time: 3526.525 ms, Deserialze time: 15.151 ms
System available mem: 20766720KB
Query Max mem: 22364160KB
Query estimated mem: 4096KB
Initial DOP: 1
Avail(CPU/IO)/Max core: (0.16/0.44)/16.00
CPU/IO/Task util: 94.00/0.00/0
Running/Active/Max statement: 1/8/21474836
Final DOP: 1
Enqueue time: 0.004 ms
Coordinator executor start time: 1.511 ms
Coordinator executor run time: 3581.055 ms
Coordinator executor end time: 117.746 ms
Parser runtime: 0.120 ms
Planner runtime: 0.949 ms
Query Id: 74309395329865177
Total runtime: 3701.391 ms
(259 rows)
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）