说明
近日，开发反馈公司分布式数据库一条SQL语句运行几个小时都没有结果，希望能够帮忙定位下问题
定位阶段
找出当前正在运行的SQL
nebula_kcb=# select * from pg_stat_activity where state='active' and usename <> 'omm';
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------------------------------------
datid | 25915815
datname | nebula_kcb
pid | 140342934370048
usesysid | 16574
usename | bdetladmin
application_name | Data Studio
client_addr | 180.7.5.7
client_hostname | 180.7.5.7
client_port | 52489
backend_start | 2020-03-12 14:04:21.541388+08
xact_start | 2020-03-12 14:04:21.56654+08
query_start | 2020-03-12 14:04:28.365907+08
state_change | 2020-03-12 14:04:28.365885+08
waiting | f
enqueue |
state | active
resource_pool | default_pool
query_id | 26511571
query | SELECT
| Entry_Date
| ,Acct_ID
| ,Sec_Code
| ,Tran_Maint_Code
| ,Order_No
| ,RANK () over( partition BY Entry_Date ,Sec_Code ORDER BY Last_Upd_Time ASC ,Last_Upd_Time_Dec ASC ,Order_No ASC ) Order_Rank
| ,Last_Upd_Time
| ,Last_Upd_Time_Dec
| ,Credit_Type
| ,Trade_Dir
| ,Order_Type
| ,Order_Price
| ,Order_Vol
| ,( CASE WHEN Order_Price = 0 THEN( CASE WHEN Trade_Dir = 'B' THEN Max_Trade_Price
| WHEN Trade_Dir = 'S' THEN Min_Trade_Price
| ELSE NULL
| END ) * Order_Vol
| ELSE Order_Price * Order_Vol
| END ) AS Order_Amt
| ,CAST( Trade_Amt * 1.000 / NULLIF( Order_Vol - Order_Bal ,0 ) AS DECIMAL( 18 ,3 ) ) AS Trade_Price
| ,Order_Vol - Order_Bal AS Trade_Vol
| ,Trade_Amt
| ,( CASE WHEN Trade_Dir = 'B' THEN Max_Trade_Price
| WHEN Trade_Dir = 'S' THEN Min_Trade_Price
| ELSE NULL
| END )
查看数据库中问题SQL的等待情况
基于pg_stat_activity找出的SQL的query_id，这个ID是分布式数据库一条SQL语句运行的唯一标识可以定位这个SQL语句的各个分布式线程的运行状况
nebula_kcb=# select * from pgxc_thread_wait_status where query_id=26511571;
node_name | db_name | thread_name | query_id | tid | lwtid | ptid | tlevel | smpid | wait_status
--------------+------------+-------------+----------+-----------------+--------+--------+--------+-------+------------------------------
dn_6001_6002 | nebula_kcb | Data Studio | 26511571 | 140694161192704 | 10552 | 16767 | 5 | 0 | net flush data: dn_6155_6156
dn_6001_6002 | nebula_kcb | Data Studio | 26511571 | 140693263611648 | 16767 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6003_6004 | nebula_kcb | Data Studio | 26511571 | 140179490731776 | 10556 | 16772 | 5 | 0 | net flush data: dn_6155_6156
dn_6003_6004 | nebula_kcb | Data Studio | 26511571 | 140158733121280 | 16772 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6005_6006 | nebula_kcb | Data Studio | 26511571 | 140289670903552 | 10560 | 16768 | 5 | 0 | net flush data: dn_6155_6156
dn_6005_6006 | nebula_kcb | Data Studio | 26511571 | 140277440308992 | 16768 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6025_6026 | nebula_kcb | Data Studio | 26511571 | 139835809462016 | 18832 | 18635 | 5 | 0 | net flush data: dn_6155_6156
dn_6025_6026 | nebula_kcb | Data Studio | 26511571 | 139835511666432 | 18635 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6029_6030 | nebula_kcb | Data Studio | 26511571 | 139879258253056 | 18834 | 18640 | 5 | 0 | net flush data: dn_6155_6156
dn_6029_6030 | nebula_kcb | Data Studio | 26511571 | 139864351696640 | 18640 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6031_6032 | nebula_kcb | Data Studio | 26511571 | 139658935662336 | 18833 | 18639 | 5 | 0 | net flush data: dn_6155_6156
dn_6031_6032 | nebula_kcb | Data Studio | 26511571 | 139650882598656 | 18639 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6033_6034 | nebula_kcb | Data Studio | 26511571 | 139845959677696 | 31803 | 25951 | 5 | 0 | net flush data: dn_6155_6156
dn_6033_6034 | nebula_kcb | Data Studio | 26511571 | 139837961139968 | 25951 | | 0 | 0 | wait node: dn_6005_6006(5)
cn_5001 | nebula_kcb | Data Studio | 26511571 | 140342934370048 | 6797 | | 0 | 0 | wait node: dn_6207_6208
dn_6011_6012 | nebula_kcb | Data Studio | 26511571 | 140611403380480 | 10557 | 16773 | 5 | 0 | net flush data: dn_6155_6156
dn_6011_6012 | nebula_kcb | Data Studio | 26511571 | 140599235704576 | 16773 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6097_6098 | nebula_kcb | Data Studio | 26511571 | 140342112282368 | 6134 | 21337 | 5 | 0 | net flush data: dn_6155_6156
dn_6097_6098 | nebula_kcb | Data Studio | 26511571 | 140332675098368 | 21337 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6111_6112 | nebula_kcb | Data Studio | 26511571 | 139922149209856 | 6138 | 21343 | 5 | 0 | net flush data: dn_6155_6156
dn_6111_6112 | nebula_kcb | Data Studio | 26511571 | 139917451589376 | 21343 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6105_6106 | nebula_kcb | Data Studio | 26511571 | 140316942268160 | 6139 | 21344 | 5 | 0 | net flush data: dn_6155_6156
dn_6105_6106 | nebula_kcb | Data Studio | 26511571 | 140311913297664 | 21344 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6193_6194 | nebula_kcb | Data Studio | 26511571 | 140358860142336 | 4101 | 45230 | 5 | 0 | net flush data: dn_6155_6156
dn_6193_6194 | nebula_kcb | Data Studio | 26511571 | 140353000695552 | 45230 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6195_6196 | nebula_kcb | Data Studio | 26511571 | 140562023839488 | 4102 | 45223 | 5 | 0 | net flush data: dn_6155_6156
dn_6195_6196 | nebula_kcb | Data Studio | 26511571 | 140554994185984 | 45223 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6197_6198 | nebula_kcb | Data Studio | 26511571 | 139773419190016 | 4105 | 45225 | 5 | 0 | net flush data: dn_6155_6156
dn_6197_6198 | nebula_kcb | Data Studio | 26511571 | 139767941428992 | 45225 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6199_6200 | nebula_kcb | Data Studio | 26511571 | 139742347781888 | 4100 | 45224 | 5 | 0 | net flush data: dn_6155_6156
dn_6199_6200 | nebula_kcb | Data Studio | 26511571 | 139735888557824 | 45224 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6201_6202 | nebula_kcb | Data Studio | 26511571 | 139967007291136 | 4122 | 45226 | 5 | 0 | net flush data: dn_6155_6156
dn_6201_6202 | nebula_kcb | Data Studio | 26511571 | 139962729101056 | 45226 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6203_6204 | nebula_kcb | Data Studio | 26511571 | 139977216227072 | 4099 | 45227 | 5 | 0 | net flush data: dn_6155_6156
dn_6203_6204 | nebula_kcb | Data Studio | 26511571 | 139960627750656 | 45227 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6007_6008 | nebula_kcb | Data Studio | 26511571 | 139698970294016 | 10551 | 16769 | 5 | 0 | net flush data: dn_6155_6156
dn_6007_6008 | nebula_kcb | Data Studio | 26511571 | 139685133281024 | 16769 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6131_6132 | nebula_kcb | Data Studio | 26511571 | 139626731796224 | 79295 | 73770 | 5 | 0 | net flush data: dn_6155_6156
dn_6131_6132 | nebula_kcb | Data Studio | 26511571 | 139620603918080 | 73770 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6129_6130 | nebula_kcb | Data Studio | 26511571 | 139960682280704 | 79297 | 73767 | 5 | 0 | net flush data: dn_6155_6156
dn_6129_6130 | nebula_kcb | Data Studio | 26511571 | 139942135068416 | 73767 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6133_6134 | nebula_kcb | Data Studio | 26511571 | 139995486615296 | 79294 | 73771 | 5 | 0 | net flush data: dn_6155_6156
dn_6133_6134 | nebula_kcb | Data Studio | 26511571 | 139991225202432 | 73771 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6137_6138 | nebula_kcb | Data Studio | 26511571 | 140133286278912 | 79292 | 73768 | 5 | 0 | net flush data: dn_6155_6156
dn_6137_6138 | nebula_kcb | Data Studio | 26511571 | 140114520962816 | 73768 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6141_6142 | nebula_kcb | Data Studio | 26511571 | 140677644023552 | 79296 | 73772 | 5 | 0 | net flush data: dn_6155_6156
dn_6141_6142 | nebula_kcb | Data Studio | 26511571 | 140671881033472 | 73772 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6009_6010 | nebula_kcb | Data Studio | 26511571 | 139619421124352 | 10554 | 16770 | 5 | 0 | net flush data: dn_6155_6156
dn_6009_6010 | nebula_kcb | Data Studio | 26511571 | 139616212481792 | 16770 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6013_6014 | nebula_kcb | Data Studio | 26511571 | 139881271523072 | 10553 | 16775 | 5 | 0 | net flush data: dn_6155_6156
dn_6013_6014 | nebula_kcb | Data Studio | 26511571 | 139860895594240 | 16775 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6015_6016 | nebula_kcb | Data Studio | 26511571 | 140062981351168 | 10558 | 16774 | 5 | 0 | net flush data: dn_6155_6156
dn_6015_6016 | nebula_kcb | Data Studio | 26511571 | 140068543002368 | 16774 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6017_6018 | nebula_kcb | Data Studio | 26511571 | 140691954988800 | 18828 | 18632 | 5 | 0 | net flush data: dn_6155_6156
dn_6017_6018 | nebula_kcb | Data Studio | 26511571 | 140686917629696 | 18632 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6019_6020 | nebula_kcb | Data Studio | 26511571 | 139858840385280 | 18827 | 18634 | 5 | 0 | net flush data: dn_6155_6156
dn_6019_6020 | nebula_kcb | Data Studio | 26511571 | 139853681391360 | 18634 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6021_6022 | nebula_kcb | Data Studio | 26511571 | 140586128504576 | 18830 | 18633 | 5 | 0 | net flush data: dn_6155_6156
dn_6021_6022 | nebula_kcb | Data Studio | 26511571 | 140579275011840 | 18633 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6023_6024 | nebula_kcb | Data Studio | 26511571 | 140054966040320 | 18829 | 18638 | 5 | 0 | net flush data: dn_6155_6156
dn_6023_6024 | nebula_kcb | Data Studio | 26511571 | 140042538317568 | 18638 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6027_6028 | nebula_kcb | Data Studio | 26511571 | 140564368455424 | 18831 | 18637 | 5 | 0 | net flush data: dn_6155_6156
dn_6027_6028 | nebula_kcb | Data Studio | 26511571 | 140550724384512 | 18637 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6035_6036 | nebula_kcb | Data Studio | 26511571 | 139913970317056 | 31809 | 25959 | 5 | 0 | net flush data: dn_6155_6156
dn_6035_6036 | nebula_kcb | Data Studio | 26511571 | 139905980167936 | 25959 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6037_6038 | nebula_kcb | Data Studio | 26511571 | 140207517067008 | 31805 | 25952 | 5 | 0 | net flush data: dn_6155_6156
dn_6037_6038 | nebula_kcb | Data Studio | 26511571 | 140191155091200 | 25952 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6039_6040 | nebula_kcb | Data Studio | 26511571 | 140622635726592 | 31807 | 25954 | 5 | 0 | net flush data: dn_6155_6156
dn_6039_6040 | nebula_kcb | Data Studio | 26511571 | 140602503067392 | 25954 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6041_6042 | nebula_kcb | Data Studio | 26511571 | 140280103696128 | 31810 | 25953 | 5 | 0 | net flush data: dn_6155_6156
dn_6041_6042 | nebula_kcb | Data Studio | 26511571 | 140271899637504 | 25953 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6043_6044 | nebula_kcb | Data Studio | 26511571 | 139668939077376 | 31808 | 25957 | 5 | 0 | net flush data: dn_6155_6156
dn_6043_6044 | nebula_kcb | Data Studio | 26511571 | 139660802127616 | 25957 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6045_6046 | nebula_kcb | Data Studio | 26511571 | 139956135655168 | 31815 | 25955 | 5 | 0 | net flush data: dn_6155_6156
dn_6045_6046 | nebula_kcb | Data Studio | 26511571 | 139947952568064 | 25955 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6047_6048 | nebula_kcb | Data Studio | 26511571 | 140578348062464 | 31804 | 25956 | 5 | 0 | net flush data: dn_6155_6156
dn_6047_6048 | nebula_kcb | Data Studio | 26511571 | 140583398012672 | 25956 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6049_6050 | nebula_kcb | Data Studio | 26511571 | 140278895736576 | 2022 | 13074 | 5 | 0 | net flush data: dn_6155_6156
dn_6049_6050 | nebula_kcb | Data Studio | 26511571 | 140274160367360 | 13074 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6051_6052 | nebula_kcb | Data Studio | 26511571 | 140525826995968 | 2007 | 13075 | 5 | 0 | net flush data: dn_6155_6156
dn_6051_6052 | nebula_kcb | Data Studio | 26511571 | 140526577776384 | 13075 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6053_6054 | nebula_kcb | Data Studio | 26511571 | 139872513816320 | 2012 | 13078 | 5 | 0 | net flush data: dn_6155_6156
dn_6053_6054 | nebula_kcb | Data Studio | 26511571 | 139870370526976 | 13078 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6055_6056 | nebula_kcb | Data Studio | 26511571 | 140615203419904 | 2019 | 13081 | 5 | 0 | net flush data: dn_6155_6156
dn_6055_6056 | nebula_kcb | Data Studio | 26511571 | 140607968245504 | 13081 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6057_6058 | nebula_kcb | Data Studio | 26511571 | 140324777228032 | 2015 | 13077 | 5 | 0 | net flush data: dn_6155_6156
dn_6057_6058 | nebula_kcb | Data Studio | 26511571 | 140316787078912 | 13077 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6059_6060 | nebula_kcb | Data Studio | 26511571 | 140482847962880 | 2021 | 13079 | 5 | 0 | net flush data: dn_6155_6156
dn_6059_6060 | nebula_kcb | Data Studio | 26511571 | 140479492519680 | 13079 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6061_6062 | nebula_kcb | Data Studio | 26511571 | 140589907572480 | 2013 | 13080 | 5 | 0 | net flush data: dn_6155_6156
dn_6061_6062 | nebula_kcb | Data Studio | 26511571 | 140582764672768 | 13080 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6063_6064 | nebula_kcb | Data Studio | 26511571 | 140099509548800 | 2018 | 13082 | 5 | 0 | net flush data: dn_6155_6156
dn_6063_6064 | nebula_kcb | Data Studio | 26511571 | 140096858748672 | 13082 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6065_6066 | nebula_kcb | Data Studio | 26511571 | 140269718599424 | 8777 | 3195 | 5 | 0 | net flush data: dn_6155_6156
dn_6065_6066 | nebula_kcb | Data Studio | 26511571 | 140262080767744 | 3195 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6067_6068 | nebula_kcb | Data Studio | 26511571 | 140156354950912 | 8781 | 3201 | 5 | 0 | net flush data: dn_6155_6156
dn_6067_6068 | nebula_kcb | Data Studio | 26511571 | 140161979512576 | 3201 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6069_6070 | nebula_kcb | Data Studio | 26511571 | 140682995955456 | 8778 | 3202 | 5 | 0 | net flush data: dn_6155_6156
dn_6069_6070 | nebula_kcb | Data Studio | 26511571 | 140678088619776 | 3202 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6071_6072 | nebula_kcb | Data Studio | 26511571 | 140339046250240 | 8782 | 3198 | 5 | 0 | net flush data: dn_6155_6156
dn_6071_6072 | nebula_kcb | Data Studio | 26511571 | 140329655203584 | 3198 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6073_6074 | nebula_kcb | Data Studio | 26511571 | 140220699768576 | 8780 | 3211 | 5 | 0 | net flush data: dn_6155_6156
dn_6073_6074 | nebula_kcb | Data Studio | 26511571 | 140212957079296 | 3211 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6075_6076 | nebula_kcb | Data Studio | 26511571 | 140074771543808 | 8775 | 3210 | 5 | 0 | net flush data: dn_6155_6156
dn_6075_6076 | nebula_kcb | Data Studio | 26511571 | 140069973255936 | 3210 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6077_6078 | nebula_kcb | Data Studio | 26511571 | 140036452378368 | 8779 | 3206 | 5 | 0 | net flush data: dn_6155_6156
dn_6077_6078 | nebula_kcb | Data Studio | 26511571 | 140033717696256 | 3206 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6079_6080 | nebula_kcb | Data Studio | 26511571 | 140123895232256 | 8776 | 3214 | 5 | 0 | net flush data: dn_6155_6156
dn_6079_6080 | nebula_kcb | Data Studio | 26511571 | 140119575099136 | 3214 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6081_6082 | nebula_kcb | Data Studio | 26511571 | 140653124122368 | 36383 | 11649 | 5 | 0 | net flush data: dn_6155_6156
dn_6081_6082 | nebula_kcb | Data Studio | 26511571 | 140632425232128 | 11649 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6083_6084 | nebula_kcb | Data Studio | 26511571 | 139763621295872 | 36379 | 11651 | 5 | 0 | net flush data: dn_6155_6156
dn_6083_6084 | nebula_kcb | Data Studio | 26511571 | 139755626948352 | 11651 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6085_6086 | nebula_kcb | Data Studio | 26511571 | 140373821224704 | 36378 | 11650 | 5 | 0 | net flush data: dn_6155_6156
dn_6085_6086 | nebula_kcb | Data Studio | 26511571 | 140352862287616 | 11650 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6089_6090 | nebula_kcb | Data Studio | 26511571 | 140547255695104 | 36380 | 11652 | 5 | 0 | net flush data: dn_6155_6156
dn_6089_6090 | nebula_kcb | Data Studio | 26511571 | 140542449022720 | 11652 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6091_6092 | nebula_kcb | Data Studio | 26511571 | 139811587356416 | 36377 | 11653 | 5 | 0 | net flush data: dn_6155_6156
dn_6091_6092 | nebula_kcb | Data Studio | 26511571 | 139803160999680 | 11653 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6093_6094 | nebula_kcb | Data Studio | 26511571 | 139674442004224 | 36376 | 11654 | 5 | 0 | net flush data: dn_6155_6156
dn_6093_6094 | nebula_kcb | Data Studio | 26511571 | 139666535741184 | 11654 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6095_6096 | nebula_kcb | Data Studio | 26511571 | 140548413323008 | 36382 | 11656 | 5 | 0 | net flush data: dn_6155_6156
dn_6095_6096 | nebula_kcb | Data Studio | 26511571 | 140516045879040 | 11656 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6099_6100 | nebula_kcb | Data Studio | 26511571 | 140427973883648 | 6135 | 21340 | 5 | 0 | net flush data: dn_6155_6156
dn_6099_6100 | nebula_kcb | Data Studio | 26511571 | 140407681840896 | 21340 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6101_6102 | nebula_kcb | Data Studio | 26511571 | 139858458703616 | 6141 | 21339 | 5 | 0 | net flush data: dn_6155_6156
dn_6101_6102 | nebula_kcb | Data Studio | 26511571 | 139845418612480 | 21339 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6103_6104 | nebula_kcb | Data Studio | 26511571 | 140584635332352 | 6136 | 21341 | 5 | 0 | net flush data: dn_6155_6156
dn_6103_6104 | nebula_kcb | Data Studio | 26511571 | 140576603240192 | 21341 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6107_6108 | nebula_kcb | Data Studio | 26511571 | 140327155398400 | 6142 | 21342 | 5 | 0 | net flush data: dn_6155_6156
dn_6107_6108 | nebula_kcb | Data Studio | 26511571 | 140306779469568 | 21342 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6109_6110 | nebula_kcb | Data Studio | 26511571 | 140241738397440 | 6137 | 21345 | 5 | 0 | net flush data: dn_6155_6156
dn_6109_6110 | nebula_kcb | Data Studio | 26511571 | 140233400121088 | 21345 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6113_6114 | nebula_kcb | Data Studio | 26511571 | 140294985086720 | 18724 | 36526 | 5 | 0 | net flush data: dn_6155_6156
dn_6113_6114 | nebula_kcb | Data Studio | 26511571 | 140284193142528 | 36526 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6115_6116 | nebula_kcb | Data Studio | 26511571 | 139720092808960 | 18721 | 36520 | 5 | 0 | net flush data: dn_6155_6156
dn_6115_6116 | nebula_kcb | Data Studio | 26511571 | 139712559838976 | 36520 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6117_6118 | nebula_kcb | Data Studio | 26511571 | 140546450384640 | 18717 | 36525 | 5 | 0 | net flush data: dn_6155_6156
dn_6117_6118 | nebula_kcb | Data Studio | 26511571 | 140534949607168 | 36525 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6119_6120 | nebula_kcb | Data Studio | 26511571 | 139712866023168 | 18720 | 36521 | 5 | 0 | net flush data: dn_6155_6156
dn_6119_6120 | nebula_kcb | Data Studio | 26511571 | 139700404745984 | 36521 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6121_6122 | nebula_kcb | Data Studio | 26511571 | 140550917322496 | 18723 | 36522 | 5 | 0 | net flush data: dn_6155_6156
dn_6121_6122 | nebula_kcb | Data Studio | 26511571 | 140543388542720 | 36522 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6123_6124 | nebula_kcb | Data Studio | 26511571 | 139728850515712 | 18725 | 36524 | 5 | 0 | net flush data: dn_6155_6156
dn_6123_6124 | nebula_kcb | Data Studio | 26511571 | 139716313736960 | 36524 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6125_6126 | nebula_kcb | Data Studio | 26511571 | 140497024710400 | 18722 | 36523 | 5 | 0 | net flush data: dn_6155_6156
dn_6125_6126 | nebula_kcb | Data Studio | 26511571 | 140492796847872 | 36523 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6127_6128 | nebula_kcb | Data Studio | 26511571 | 140186906261248 | 18719 | 36527 | 5 | 0 | net flush data: dn_6155_6156
dn_6127_6128 | nebula_kcb | Data Studio | 26511571 | 140177561351936 | 36527 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6135_6136 | nebula_kcb | Data Studio | 26511571 | 139869858817792 | 79291 | 73769 | 5 | 0 | net flush data: dn_6155_6156
dn_6135_6136 | nebula_kcb | Data Studio | 26511571 | 139867254159104 | 73769 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6139_6140 | nebula_kcb | Data Studio | 26511571 | 140616528811776 | 79298 | 73774 | 5 | 0 | net flush data: dn_6155_6156
dn_6139_6140 | nebula_kcb | Data Studio | 26511571 | 140613483755264 | 73774 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6143_6144 | nebula_kcb | Data Studio | 26511571 | 140020761491200 | 79293 | 73773 | 5 | 0 | net flush data: dn_6155_6156
dn_6143_6144 | nebula_kcb | Data Studio | 26511571 | 140013035583232 | 73773 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6145_6146 | nebula_kcb | Data Studio | 26511571 | 139878272595712 | 151922 | 88163 | 5 | 0 | net flush data: dn_6155_6156
dn_6145_6146 | nebula_kcb | Data Studio | 26511571 | 139867057026816 | 88163 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6147_6148 | nebula_kcb | Data Studio | 26511571 | 140633549305600 | 151925 | 88157 | 5 | 0 | net flush data: dn_6155_6156
dn_6147_6148 | nebula_kcb | Data Studio | 26511571 | 140615362799360 | 88157 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6149_6150 | nebula_kcb | Data Studio | 26511571 | 140441085277952 | 151915 | 88159 | 5 | 0 | net flush data: dn_6155_6156
dn_6149_6150 | nebula_kcb | Data Studio | 26511571 | 140435133556480 | 88159 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6151_6152 | nebula_kcb | Data Studio | 26511571 | 139825294341888 | 151919 | 88164 | 5 | 0 | net flush data: dn_6155_6156
dn_6151_6152 | nebula_kcb | Data Studio | 26511571 | 139819820775168 | 88164 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6153_6154 | nebula_kcb | Data Studio | 26511571 | 140576452245248 | 151920 | 88165 | 5 | 0 | net flush data: dn_6155_6156
dn_6153_6154 | nebula_kcb | Data Studio | 26511571 | 140557414299392 | 88165 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6155_6156 | nebula_kcb | Data Studio | 26511571 | 140684736591616 | 151918 | 88160 | 5 | 0 | net flush data: dn_6155_6156
dn_6155_6156 | nebula_kcb | Data Studio | 26511571 | 140680982689536 | 88160 | | 0 | 0 | none
dn_6157_6158 | nebula_kcb | Data Studio | 26511571 | 139931015968512 | 151917 | 88162 | 5 | 0 | net flush data: dn_6155_6156
dn_6157_6158 | nebula_kcb | Data Studio | 26511571 | 139925634676480 | 88162 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6159_6160 | nebula_kcb | Data Studio | 26511571 | 139993691453184 | 151916 | 88167 | 5 | 0 | net flush data: dn_6155_6156
dn_6159_6160 | nebula_kcb | Data Studio | 26511571 | 139975068743424 | 88167 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6161_6162 | nebula_kcb | Data Studio | 26511571 | 140032610400000 | 132330 | 138032 | 5 | 0 | net flush data: dn_6155_6156
dn_6161_6162 | nebula_kcb | Data Studio | 26511571 | 140018664335104 | 138032 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6163_6164 | nebula_kcb | Data Studio | 26511571 | 140144153720576 | 132329 | 138033 | 5 | 0 | net flush data: dn_6155_6156
dn_6163_6164 | nebula_kcb | Data Studio | 26511571 | 140140471121664 | 138033 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6165_6166 | nebula_kcb | Data Studio | 26511571 | 140294288832256 | 132327 | 138034 | 5 | 0 | net flush data: dn_6155_6156
dn_6165_6166 | nebula_kcb | Data Studio | 26511571 | 140286655198976 | 138034 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6167_6168 | nebula_kcb | Data Studio | 26511571 | 140585771988736 | 132332 | 138035 | 5 | 0 | net flush data: dn_6155_6156
dn_6167_6168 | nebula_kcb | Data Studio | 26511571 | 140576460633856 | 138035 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6169_6170 | nebula_kcb | Data Studio | 26511571 | 140296012691200 | 132328 | 138039 | 5 | 0 | net flush data: dn_6155_6156
dn_6169_6170 | nebula_kcb | Data Studio | 26511571 | 140281672365824 | 138039 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6171_6172 | nebula_kcb | Data Studio | 26511571 | 140693263611648 | 132333 | 138036 | 5 | 0 | net flush data: dn_6155_6156
dn_6171_6172 | nebula_kcb | Data Studio | 26511571 | 140674879977216 | 138036 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6177_6178 | nebula_kcb | Data Studio | 26511571 | 140309333800704 | 90650 | 206033 | 5 | 0 | net flush data: dn_6155_6156
dn_6177_6178 | nebula_kcb | Data Studio | 26511571 | 140305227577088 | 206033 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6179_6180 | nebula_kcb | Data Studio | 26511571 | 140498358499072 | 90643 | 206032 | 5 | 0 | net flush data: dn_6155_6156
dn_6179_6180 | nebula_kcb | Data Studio | 26511571 | 140491899270912 | 206032 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6185_6186 | nebula_kcb | Data Studio | 26511571 | 140205411530496 | 90646 | 206038 | 5 | 0 | net flush data: dn_6155_6156
dn_6185_6186 | nebula_kcb | Data Studio | 26511571 | 140197295552256 | 206038 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6187_6188 | nebula_kcb | Data Studio | 26511571 | 140384411838208 | 90644 | 206034 | 5 | 0 | net flush data: dn_6155_6156
dn_6187_6188 | nebula_kcb | Data Studio | 26511571 | 140380213344000 | 206034 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6191_6192 | nebula_kcb | Data Studio | 26511571 | 140117503112960 | 90647 | 206036 | 5 | 0 | net flush data: dn_6155_6156
dn_6191_6192 | nebula_kcb | Data Studio | 26511571 | 140111148738304 | 206036 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6205_6206 | nebula_kcb | Data Studio | 26511571 | 140404762605312 | 4103 | 45228 | 5 | 0 | net flush data: dn_6155_6156
dn_6205_6206 | nebula_kcb | Data Studio | 26511571 | 140399200958208 | 45228 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6207_6208 | nebula_kcb | Data Studio | 26511571 | 140647084324608 | 4098 | 45229 | 5 | 0 | net flush data: dn_6155_6156
dn_6207_6208 | nebula_kcb | Data Studio | 26511571 | 140635700983552 | 45229 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6173_6174 | nebula_kcb | Data Studio | 26511571 | 140381131896576 | 132331 | 138038 | 5 | 0 | net flush data: dn_6155_6156
dn_6173_6174 | nebula_kcb | Data Studio | 26511571 | 140373900916480 | 138038 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6175_6176 | nebula_kcb | Data Studio | 26511571 | 139721388848896 | 132334 | 138037 | 5 | 0 | net flush data: dn_6155_6156
dn_6175_6176 | nebula_kcb | Data Studio | 26511571 | 139717634946816 | 138037 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6181_6182 | nebula_kcb | Data Studio | 26511571 | 139973110003456 | 90649 | 206035 | 5 | 0 | net flush data: dn_6155_6156
dn_6181_6182 | nebula_kcb | Data Studio | 26511571 | 139962062202624 | 206035 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6183_6184 | nebula_kcb | Data Studio | 26511571 | 140110146303744 | 90648 | 206037 | 5 | 0 | net flush data: dn_6155_6156
dn_6183_6184 | nebula_kcb | Data Studio | 26511571 | 140096468678400 | 206037 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6087_6088 | nebula_kcb | Data Studio | 26511571 | 140434252756736 | 36381 | 11655 | 5 | 0 | net flush data: dn_6155_6156
dn_6087_6088 | nebula_kcb | Data Studio | 26511571 | 140422357710592 | 11655 | | 0 | 0 | wait node: dn_6005_6006(5)
dn_6189_6190 | nebula_kcb | Data Studio | 26511571 | 140594542278400 | 90645 | 206039 | 5 | 0 | net flush data: dn_6155_6156
dn_6189_6190 | nebula_kcb | Data Studio | 26511571 | 140586845730560 | 206039 | | 0 | 0 | wait node: dn_6005_6006(5)
(209 rows)
从上面的查询结果冲，我们可以看到，所有的计算节点，全部都是在等待dn_6155_6156这个节点的运行结果，也就意味着，在dn_6155_6156 这个节点上，查询线程的运行较慢
由于直接在数据库中，查询的SQL语句是不完整的，我们可以通过下面的语句去获取完整的SQL语句信息
gsql -d nebula_kcb -p 25108 -c " select query from pg_stat_activity where state='active' and usename <> 'omm';
最终找出对应的SQL语句
SELECT
Entry_Date
,Acct_ID
,Sec_Code
,Tran_Maint_Code
,Order_No
,RANK () over( partition BY Entry_Date ,Sec_Code ORDER BY Last_Upd_Time ASC ,Last_Upd_Time_Dec ASC ,Order_No ASC ) Order_Rank
,Last_Upd_Time
,Last_Upd_Time_Dec
,Credit_Type
,Trade_Dir
,Order_Type
,Order_Price
,Order_Vol
,( CASE WHEN Order_Price = 0 THEN( CASE WHEN Trade_Dir = 'B' THEN Max_Trade_Price
WHEN Trade_Dir = 'S' THEN Min_Trade_Price
ELSE NULL
END ) * Order_Vol
ELSE Order_Price * Order_Vol
END ) AS Order_Amt
,CAST( Trade_Amt * 1.000 / NULLIF( Order_Vol - Order_Bal ,0 ) AS DECIMAL( 18 ,3 ) ) AS Trade_Price
,Order_Vol - Order_Bal AS Trade_Vol
,Trade_Amt
,( CASE WHEN Trade_Dir = 'B' THEN Max_Trade_Price
WHEN Trade_Dir = 'S' THEN Min_Trade_Price
ELSE NULL
END ) AS Last_Trade_Price
,Withdraw_Time
,Withdraw_Time_Dec
,( CASE WHEN Trade_Flag = 'W' THEN Order_Bal
ELSE 0
END ) AS Withdraw_Vol
,Trade_Vol_2
,Trade_Amt_2
,Last_Trade_Price_2
,Trade_Flag
,Max_Trade_Price
,Min_Trade_Price
,First_Trade_Time
,First_Trade_Time_Dec
,Last_Trade_Time
,Last_Trade_Time_Dec
FROM PD_STAGEAREA.TXN_NG_Order_D2_U
;
初步分析阶段
通过前面的定位，我们已经把性能较差的问题SQL找到，语句逻辑很简单，单张表的查询语句，通过rank over的窗口函数对数据进行了处理。
分布式数据库，比较常见的性能问题，一般有两种：
数据分布不均
SQL语句不能下推
我们按照常见的性能情况，逐一进行排查
分析数据分布
查看表pd_stagearea.txn_ng_order_d2_u的数据分布
查看分布键
nebula_kcb=# \d+ PD_STAGEAREA.TXN_NG_Order_D2_U;
Table "pd_stagearea.txn_ng_order_d2_u"
Column | Type | Modifiers | Storage | Stats target | Description
----------------------+-----------------------------+-----------+----------+--------------+-------------
order_no | numeric(18,0) | not null | main | |
tran_maint_code | character(1) | not null | extended | |
last_upd_date | timestamp without time zone | not null | plain | |
last_upd_time | integer | not null | plain | |
last_upd_time_dec | numeric(8,7) | not null | main | |
entry_date | timestamp without time zone | not null | plain | |
entry_time | integer | not null | plain | |
entry_time_dec | numeric(8,7) | not null | main | |
exp_date | timestamp without time zone | not null | plain | |
order_price | numeric(18,5) | not null | main | |
order_exec_vol | numeric(18,3) | not null | main | |
order_vol | numeric(18,3) | not null | main | |
sec_code | character(6) | not null | extended | |
pbu_id | character(5) | not null | extended | |
acct_id | character(10) | not null | extended | |
acct_attr | character(1) | not null | extended | |
branch_id | character(5) | not null | extended | |
pbu_inter_order_no | character varying(16) | not null | extended | |
pbu_inter_txt | character varying(12) | not null | extended | |
clear_pbu_id | character(5) | not null | extended | |
aud_type | character(1) | not null | extended | |
order_type | character(2) | not null | extended | |
trade_restr_type | character(2) | not null | extended | |
order_stat | character(1) | not null | extended | |
trade_dir | character(1) | not null | extended | |
order_restr_type | character(1) | not null | extended | |
short_sell_flag | character(1) | not null | extended | |
credit_type | character(1) | not null | extended | |
stat_pbu_id | character(5) | not null | extended | |
order_bal | numeric(18,3) | not null | main | |
trade_flag | character(1) | not null | extended | |
trade_amt | numeric(18,3) | not null | main | |
trade_cnt | numeric(9,0) | not null | main | |
max_trade_price | numeric(18,5) | | main | |
min_trade_price | numeric(18,5) | | main | |
withdraw_time | integer | | plain | |
withdraw_time_dec | numeric(8,7) | | main | |
first_trade_time | integer | | plain | |
first_trade_time_dec | numeric(8,7) | | main | |
first_trade_no | numeric(18,0) | | main | |
last_trade_time | integer | | plain | |
last_trade_time_dec | numeric(8,7) | | main | |
last_trade_no | numeric(18,0) | | main | |
trade_vol_2 | numeric(18,0) | not null | main | |
trade_amt_2 | numeric(18,3) | not null | main | |
last_trade_price_2 | numeric(18,5) | | main | |
Has OIDs: no
Tablespace: "hdfs_pd_stagearea"
Distribute By: HASH(order_no)
Location Nodes: ALL DATANODES
Options: compression=snappy, orientation=orc, version=0.12
可以看到，是以order_no作为分布键
数据的实际分布情况
nebula_kcb=# SELECT a.count,b.node_name FROM (SELECT count(*) AS count,xc_node_id FROM PD_STAGEAREA.TXN_NG_Order_D2_U GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count desc;
count | node_name
---------+--------------
1105975 | dn_6065_6066
1105894 | dn_6109_6110
1105892 | dn_6087_6088
1105770 | dn_6015_6016
1105768 | dn_6003_6004
1105717 | dn_6001_6002
1105574 | dn_6059_6060
1105559 | dn_6069_6070
1105546 | dn_6053_6054
1105439 | dn_6099_6100
1105427 | dn_6055_6056
1105315 | dn_6107_6108
1104970 | dn_6039_6040
1104938 | dn_6009_6010
1104894 | dn_6071_6072
1104886 | dn_6005_6006
1104858 | dn_6091_6092
1104854 | dn_6045_6046
1104830 | dn_6031_6032
1104818 | dn_6083_6084
1104778 | dn_6095_6096
1104697 | dn_6023_6024
1104496 | dn_6029_6030
1104439 | dn_6049_6050
1104433 | dn_6081_6082
1104367 | dn_6037_6038
1104365 | dn_6061_6062
1104286 | dn_6057_6058
1104285 | dn_6019_6020
1104274 | dn_6075_6076
1104182 | dn_6077_6078
1104142 | dn_6043_6044
1104139 | dn_6013_6014
1104087 | dn_6103_6104
1104034 | dn_6063_6064
1104027 | dn_6047_6048
1103945 | dn_6025_6026
1103935 | dn_6011_6012
1103896 | dn_6105_6106
1103894 | dn_6093_6094
1103891 | dn_6111_6112
1103785 | dn_6085_6086
1103493 | dn_6027_6028
1103483 | dn_6021_6022
1103437 | dn_6007_6008
1103426 | dn_6033_6034
1103399 | dn_6051_6052
1103398 | dn_6017_6018
1103398 | dn_6035_6036
1103350 | dn_6097_6098
1103238 | dn_6101_6102
1103221 | dn_6079_6080
1103189 | dn_6073_6074
1103119 | dn_6041_6042
1103025 | dn_6067_6068
1102690 | dn_6089_6090
1100152 | dn_6123_6124
1099710 | dn_6127_6128
1099426 | dn_6117_6118
1099147 | dn_6159_6160
1099133 | dn_6167_6168
1099006 | dn_6189_6190
1098993 | dn_6155_6156
1098953 | dn_6163_6164
1098561 | dn_6141_6142
1098497 | dn_6151_6152
1098370 | dn_6197_6198
1098335 | dn_6183_6184
1098216 | dn_6177_6178
1098123 | dn_6173_6174
1098105 | dn_6207_6208
1098097 | dn_6205_6206
1098027 | dn_6191_6192
1097908 | dn_6143_6144
1097904 | dn_6185_6186
1097839 | dn_6115_6116
1097801 | dn_6179_6180
1097771 | dn_6145_6146
1097768 | dn_6153_6154
1097641 | dn_6201_6202
1097625 | dn_6149_6150
1097577 | dn_6169_6170
1097546 | dn_6161_6162
1097542 | dn_6187_6188
1097542 | dn_6165_6166
1097527 | dn_6203_6204
1097518 | dn_6119_6120
1097390 | dn_6113_6114
1097375 | dn_6147_6148
1097333 | dn_6157_6158
1097184 | dn_6175_6176
1097165 | dn_6125_6126
1097159 | dn_6137_6138
1097157 | dn_6139_6140
1097108 | dn_6129_6130
1097070 | dn_6195_6196
1096904 | dn_6133_6134
1096869 | dn_6171_6172
1096846 | dn_6135_6136
1096786 | dn_6199_6200
1096704 | dn_6181_6182
1096136 | dn_6121_6122
1095919 | dn_6193_6194
1095719 | dn_6131_6132
可以看到数据基本分布均匀，至此，排除了第一种数据分布不均的情况
判断SQL语句是否下推
由于SQL语句语法很简单，只用到了case when的逻辑判断与rank over的分析函数。所以我们只要判断rank over函数是否支持函数下推
查看执行计划
explain verbose
id | operation | E-rows | E-width | E-costs
----+----------------------------------------------------------------------------------------------------------+--------+---------+---------
1 | -> Row Adapter | 2065 | 330 | 310.34
2 | -> Vector Streaming (type: GATHER) | 2065 | 330 | 310.34
3 | -> Vector WindowAgg | 2065 | 330 | 115.78
4 | -> Vector Sort | 2065 | 330 | 115.20
5 | -> Vector Streaming(type: REDISTRIBUTE) | 2080 | 330 | 114.62
6 | -> Vector Result | 2080 | 330 | 94.52
7 | -> Vector Append(8, 9) | 2080 | 330 | 94.52
8 | -> Dfs Scan on pd_stagearea.txn_ng_order_d2_u | 1040 | 330 | 47.05
9 | -> Vector Adapter | 1039 | 330 | 47.47
10 | -> Seq Scan on cstore.pg_delta_pd_stagearea_txn_ng_order_d2_u txn_ng_order_d2_u | 1040 | 330 | 47.47
(10 rows) Targetlist Information (identified by plan id)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 --Row Adapter
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, (rank() OVER (PARTITION BY pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code ORDER BY pd_stagearea.txn_ng_order_d2_u.last_upd_time USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.order_no USING = NULLS LAST)), pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.order_price = 0::numeric) THEN (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END * pd_stagearea.txn_ng_order_d2_u.order_vol) ELSE (pd_stagearea.txn_ng_order_d2_u.order_price * pd_stagearea.txn_ng_order_d2_u.order_vol) END), ((((pd_stagearea.txn_ng_order_d2_u.trade_amt * 1.000) / NULLIF((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), 0::numeric)))::numeric(18,3)), ((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal)), pd_stagearea.txn_ng_order_d2_u.trade_amt, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END), pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_flag = 'W'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.order_bal ELSE 0::numeric END), pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
2 --Vector Streaming (type: GATHER)
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, (rank() OVER (PARTITION BY pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code ORDER BY pd_stagearea.txn_ng_order_d2_u.last_upd_time USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.order_no USING = NULLS LAST)), pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.order_price = 0::numeric) THEN (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END * pd_stagearea.txn_ng_order_d2_u.order_vol) ELSE (pd_stagearea.txn_ng_order_d2_u.order_price * pd_stagearea.txn_ng_order_d2_u.order_vol) END), ((((pd_stagearea.txn_ng_order_d2_u.trade_amt * 1.000) / NULLIF((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), 0::numeric)))::numeric(18,3)), ((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal)), pd_stagearea.txn_ng_order_d2_u.trade_amt, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END), pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_flag = 'W'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.order_bal ELSE 0::numeric END), pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
Node/s: All datanodes
3 --Vector WindowAgg
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, rank() OVER (PARTITION BY pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code ORDER BY pd_stagearea.txn_ng_order_d2_u.last_upd_time USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.order_no USING = NULLS LAST), pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, CASE WHEN (pd_stagearea.txn_ng_order_d2_u.order_price = 0::numeric) THEN (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END * pd_stagearea.txn_ng_order_d2_u.order_vol) ELSE (pd_stagearea.txn_ng_order_d2_u.order_price * pd_stagearea.txn_ng_order_d2_u.order_vol) END, (((pd_stagearea.txn_ng_order_d2_u.trade_amt * 1.000) / NULLIF((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), 0::numeric)))::numeric(18,3), (pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), pd_stagearea.txn_ng_order_d2_u.trade_amt, CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_flag = 'W'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.order_bal ELSE 0::numeric END, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
4 --Vector Sort
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
Sort Key: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.order_no
5 --Vector Streaming(type: REDISTRIBUTE)
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
Distribute Key: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code
Spawn on: All datanodes
6 --Vector Result
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
7 --Vector Append(8, 9)
8 --Dfs Scan on pd_stagearea.txn_ng_order_d2_u
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
9 --Vector Adapter
Output: cstore.txn_ng_order_d2_u.entry_date, cstore.txn_ng_order_d2_u.acct_id, cstore.txn_ng_order_d2_u.sec_code, cstore.txn_ng_order_d2_u.tran_maint_code, cstore.txn_ng_order_d2_u.order_no, cstore.txn_ng_order_d2_u.last_upd_time, cstore.txn_ng_order_d2_u.last_upd_time_dec, cstore.txn_ng_order_d2_u.credit_type, cstore.txn_ng_order_d2_u.trade_dir, cstore.txn_ng_order_d2_u.order_type, cstore.txn_ng_order_d2_u.order_price, cstore.txn_ng_order_d2_u.order_vol, cstore.txn_ng_order_d2_u.max_trade_price, cstore.txn_ng_order_d2_u.min_trade_price, cstore.txn_ng_order_d2_u.trade_amt, cstore.txn_ng_order_d2_u.order_bal, cstore.txn_ng_order_d2_u.withdraw_time, cstore.txn_ng_order_d2_u.withdraw_time_dec, cstore.txn_ng_order_d2_u.trade_flag, cstore.txn_ng_order_d2_u.trade_vol_2, cstore.txn_ng_order_d2_u.trade_amt_2, cstore.txn_ng_order_d2_u.last_trade_price_2, cstore.txn_ng_order_d2_u.first_trade_time, cstore.txn_ng_order_d2_u.first_trade_time_dec, cstore.txn_ng_order_d2_u.last_trade_time, cstore.txn_ng_order_d2_u.last_trade_time_dec
10 --Seq Scan on cstore.pg_delta_pd_stagearea_txn_ng_order_d2_u txn_ng_order_d2_u
Output: cstore.txn_ng_order_d2_u.entry_date, cstore.txn_ng_order_d2_u.acct_id, cstore.txn_ng_order_d2_u.sec_code, cstore.txn_ng_order_d2_u.tran_maint_code, cstore.txn_ng_order_d2_u.order_no, cstore.txn_ng_order_d2_u.last_upd_time, cstore.txn_ng_order_d2_u.last_upd_time_dec, cstore.txn_ng_order_d2_u.credit_type, cstore.txn_ng_order_d2_u.trade_dir, cstore.txn_ng_order_d2_u.order_type, cstore.txn_ng_order_d2_u.order_price, cstore.txn_ng_order_d2_u.order_vol, cstore.txn_ng_order_d2_u.max_trade_price, cstore.txn_ng_order_d2_u.min_trade_price, cstore.txn_ng_order_d2_u.trade_amt, cstore.txn_ng_order_d2_u.order_bal, cstore.txn_ng_order_d2_u.withdraw_time, cstore.txn_ng_order_d2_u.withdraw_time_dec, cstore.txn_ng_order_d2_u.trade_flag, cstore.txn_ng_order_d2_u.trade_vol_2, cstore.txn_ng_order_d2_u.trade_amt_2, cstore.txn_ng_order_d2_u.last_trade_price_2, cstore.txn_ng_order_d2_u.first_trade_time, cstore.txn_ng_order_d2_u.first_trade_time_dec, cstore.txn_ng_order_d2_u.last_trade_time, cstore.txn_ng_order_d2_u.last_trade_time_dec
(23 rows)
在执行计划中，我们看到Spawn on: All datanodes，这样的操作，代表SQL语句是正常下推到所有的节点执行的，所以至此，两种常见的导致数据库性能问题的情况已经被排除了
详细分析阶段
通过上面查看执行计划，我们发现E-rows只有2065，很显然这个数据是不准确的，这点数据量不可能几个小时查询不出结果
查看真实数据量
nebula_kcb=# select count(*) from PD_STAGEAREA.TXN_NG_Order_D2_U;
count
-----------
114538351
(1 row)
所以，我们可以判断，当前表的统计信息是不准确的那么，在统计信息不准确的情况下，数据库优化器是不一定能够计算出最优的执行计划，我们需要重新收集统计信息
analyze PD_STAGEAREA.TXN_NG_Order_D2_U
再次查看sql语句的执行计划
id | operation | E-rows | E-width | E-costs
----+----------------------------------------------------------------------------------------------------------+-----------+---------+--------------
1 | -> Row Adapter | 114539391 | 125 | 424125646.48
2 | -> Vector Streaming (type: GATHER) | 114539391 | 125 | 424125646.48
3 | -> Vector WindowAgg | 114539391 | 125 | 48293269.76
4 | -> Vector Sort | 114539391 | 125 | 45429784.98
5 | -> Vector Streaming(type: REDISTRIBUTE) | 114539391 | 125 | 3581643.00
6 | -> Vector Result | 114539391 | 125 | 30422.80
7 | -> Vector Append(8, 9) | 114539391 | 125 | 30422.80
8 | -> Dfs Scan on pd_stagearea.txn_ng_order_d2_u | 114538351 | 125 | 30375.33
9 | -> Vector Adapter | 1039 | 330 | 47.47
10 | -> Seq Scan on cstore.pg_delta_pd_stagearea_txn_ng_order_d2_u txn_ng_order_d2_u | 1040 | 330 | 47.47 Targetlist Information (identified by plan id)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 --Row Adapter
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, (rank() OVER (PARTITION BY pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code ORDER BY pd_stagearea.txn_ng_order_d2_u.last_upd_time USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.order_no USING = NULLS LAST)), pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.order_price = 0::numeric) THEN (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END * pd_stagearea.txn_ng_order_d2_u.order_vol) ELSE (pd_stagearea.txn_ng_order_d2_u.order_price * pd_stagearea.txn_ng_order_d2_u.order_vol) END), ((((pd_stagearea.txn_ng_order_d2_u.trade_amt * 1.000) / NULLIF((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), 0::numeric)))::numeric(18,3)), ((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal)), pd_stagearea.txn_ng_order_d2_u.trade_amt, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END), pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_flag = 'W'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.order_bal ELSE 0::numeric END), pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
2 --Vector Streaming (type: GATHER)
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, (rank() OVER (PARTITION BY pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code ORDER BY pd_stagearea.txn_ng_order_d2_u.last_upd_time USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.order_no USING = NULLS LAST)), pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.order_price = 0::numeric) THEN (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END * pd_stagearea.txn_ng_order_d2_u.order_vol) ELSE (pd_stagearea.txn_ng_order_d2_u.order_price * pd_stagearea.txn_ng_order_d2_u.order_vol) END), ((((pd_stagearea.txn_ng_order_d2_u.trade_amt * 1.000) / NULLIF((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), 0::numeric)))::numeric(18,3)), ((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal)), pd_stagearea.txn_ng_order_d2_u.trade_amt, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END), pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_flag = 'W'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.order_bal ELSE 0::numeric END), pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
Node/s: All datanodes
3 --Vector WindowAgg
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, rank() OVER (PARTITION BY pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code ORDER BY pd_stagearea.txn_ng_order_d2_u.last_upd_time USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec USING = NULLS LAST, pd_stagearea.txn_ng_order_d2_u.order_no USING = NULLS LAST), pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, CASE WHEN (pd_stagearea.txn_ng_order_d2_u.order_price = 0::numeric) THEN (CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END * pd_stagearea.txn_ng_order_d2_u.order_vol) ELSE (pd_stagearea.txn_ng_order_d2_u.order_price * pd_stagearea.txn_ng_order_d2_u.order_vol) END, (((pd_stagearea.txn_ng_order_d2_u.trade_amt * 1.000) / NULLIF((pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), 0::numeric)))::numeric(18,3), (pd_stagearea.txn_ng_order_d2_u.order_vol - pd_stagearea.txn_ng_order_d2_u.order_bal), pd_stagearea.txn_ng_order_d2_u.trade_amt, CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'B'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.max_trade_price WHEN (pd_stagearea.txn_ng_order_d2_u.trade_dir = 'S'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.min_trade_price ELSE NULL::numeric END, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, CASE WHEN (pd_stagearea.txn_ng_order_d2_u.trade_flag = 'W'::bpchar) THEN pd_stagearea.txn_ng_order_d2_u.order_bal ELSE 0::numeric END, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
4 --Vector Sort
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
Sort Key: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.order_no
5 --Vector Streaming(type: REDISTRIBUTE)
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
Distribute Key: pd_stagearea.txn_ng_order_d2_u.entry_date
Spawn on: All datanodes
6 --Vector Result
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
7 --Vector Append(8, 9)
8 --Dfs Scan on pd_stagearea.txn_ng_order_d2_u
Output: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.acct_id, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.tran_maint_code, pd_stagearea.txn_ng_order_d2_u.order_no, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.credit_type, pd_stagearea.txn_ng_order_d2_u.trade_dir, pd_stagearea.txn_ng_order_d2_u.order_type, pd_stagearea.txn_ng_order_d2_u.order_price, pd_stagearea.txn_ng_order_d2_u.order_vol, pd_stagearea.txn_ng_order_d2_u.max_trade_price, pd_stagearea.txn_ng_order_d2_u.min_trade_price, pd_stagearea.txn_ng_order_d2_u.trade_amt, pd_stagearea.txn_ng_order_d2_u.order_bal, pd_stagearea.txn_ng_order_d2_u.withdraw_time, pd_stagearea.txn_ng_order_d2_u.withdraw_time_dec, pd_stagearea.txn_ng_order_d2_u.trade_flag, pd_stagearea.txn_ng_order_d2_u.trade_vol_2, pd_stagearea.txn_ng_order_d2_u.trade_amt_2, pd_stagearea.txn_ng_order_d2_u.last_trade_price_2, pd_stagearea.txn_ng_order_d2_u.first_trade_time, pd_stagearea.txn_ng_order_d2_u.first_trade_time_dec, pd_stagearea.txn_ng_order_d2_u.last_trade_time, pd_stagearea.txn_ng_order_d2_u.last_trade_time_dec
9 --Vector Adapter
Output: cstore.txn_ng_order_d2_u.entry_date, cstore.txn_ng_order_d2_u.acct_id, cstore.txn_ng_order_d2_u.sec_code, cstore.txn_ng_order_d2_u.tran_maint_code, cstore.txn_ng_order_d2_u.order_no, cstore.txn_ng_order_d2_u.last_upd_time, cstore.txn_ng_order_d2_u.last_upd_time_dec, cstore.txn_ng_order_d2_u.credit_type, cstore.txn_ng_order_d2_u.trade_dir, cstore.txn_ng_order_d2_u.order_type, cstore.txn_ng_order_d2_u.order_price, cstore.txn_ng_order_d2_u.order_vol, cstore.txn_ng_order_d2_u.max_trade_price, cstore.txn_ng_order_d2_u.min_trade_price, cstore.txn_ng_order_d2_u.trade_amt, cstore.txn_ng_order_d2_u.order_bal, cstore.txn_ng_order_d2_u.withdraw_time, cstore.txn_ng_order_d2_u.withdraw_time_dec, cstore.txn_ng_order_d2_u.trade_flag, cstore.txn_ng_order_d2_u.trade_vol_2, cstore.txn_ng_order_d2_u.trade_amt_2, cstore.txn_ng_order_d2_u.last_trade_price_2, cstore.txn_ng_order_d2_u.first_trade_time, cstore.txn_ng_order_d2_u.first_trade_time_dec, cstore.txn_ng_order_d2_u.last_trade_time, cstore.txn_ng_order_d2_u.last_trade_time_dec
10 --Seq Scan on cstore.pg_delta_pd_stagearea_txn_ng_order_d2_u txn_ng_order_d2_u
Output: cstore.txn_ng_order_d2_u.entry_date, cstore.txn_ng_order_d2_u.acct_id, cstore.txn_ng_order_d2_u.sec_code, cstore.txn_ng_order_d2_u.tran_maint_code, cstore.txn_ng_order_d2_u.order_no, cstore.txn_ng_order_d2_u.last_upd_time, cstore.txn_ng_order_d2_u.last_upd_time_dec, cstore.txn_ng_order_d2_u.credit_type, cstore.txn_ng_order_d2_u.trade_dir, cstore.txn_ng_order_d2_u.order_type, cstore.txn_ng_order_d2_u.order_price, cstore.txn_ng_order_d2_u.order_vol, cstore.txn_ng_order_d2_u.max_trade_price, cstore.txn_ng_order_d2_u.min_trade_price, cstore.txn_ng_order_d2_u.trade_amt, cstore.txn_ng_order_d2_u.order_bal, cstore.txn_ng_order_d2_u.withdraw_time, cstore.txn_ng_order_d2_u.withdraw_time_dec, cstore.txn_ng_order_d2_u.trade_flag, cstore.txn_ng_order_d2_u.trade_vol_2, cstore.txn_ng_order_d2_u.trade_amt_2, cstore.txn_ng_order_d2_u.last_trade_price_2, cstore.txn_ng_order_d2_u.first_trade_time, cstore.txn_ng_order_d2_u.first_trade_time_dec, cstore.txn_ng_order_d2_u.last_trade_time, cstore.txn_ng_order_d2_u.last_trade_time_dec
这个时候，我们发现e-rows已经114539391，和我们数据库中实际的查询结果一致，优化器已经生成了准确的执行计划
对比新老执行计划，并没有什么变化，所以只能深入执行计划中，进行分析
可以看到从id为5的操作步骤开始，e-costs成本就大幅度攀升
5 | -> Vector Streaming(type: REDISTRIBUTE) | 114539391 | 125 | 3581643.00
6 | -> Vector Result | 114539391 | 125 | 30422.80
那么ID=5的执行的是什么操作呢？
Distribute Key: pd_stagearea.txn_ng_order_d2_u.entry_date
是以表中的entry_date字段，进行数据重分布的过程
对应的SQL语句中
RANK () over( partition BY Entry_Date ,Sec_Code ORDER BY Last_Upd_Time ASC ,Last_Upd_Time_Dec ASC ,Order_No ASC ) Order_Rank
果然还是在这个分析函数这儿有问题！
分析真实的执行计划
使用explain performance的执行计划，explain verbose中的内容以外，还额外包括了真实的数据执行相关的信息
其中包括了两个部分信息
Memory Information SQL运行的各个阶段中各个节点内存的消耗情况
Datanode Information SQL运行的各个阶段各个节点上数据的分布情况
我们一般看Datanode Information这部分的内容
需要注意 explain performance会执行的执行SQL语句，实际生产环境执行还需要谨慎
从上面我们知道，是第五阶段开始数据库变慢,所以，分析执行计划,截取ID为5,6两阶段的执行计划
id 6 --Vector Result
dn_6001_6002 (actual time=1731.448..2813.651 rows=1105717 loops=1)
dn_6003_6004 (actual time=1498.567..2979.619 rows=1105768 loops=1)
dn_6005_6006 (actual time=1675.680..2891.039 rows=1104886 loops=1)
dn_6007_6008 (actual time=1603.620..2785.270 rows=1103437 loops=1)
dn_6009_6010 (actual time=5714.644..6963.529 rows=1104938 loops=1)
dn_6011_6012 (actual time=5887.123..7056.414 rows=1103935 loops=1)
dn_6013_6014 (actual time=1723.830..2916.574 rows=1104139 loops=1)
dn_6015_6016 (actual time=1563.834..2703.913 rows=1105770 loops=1)
dn_6017_6018 (actual time=1423.621..2901.767 rows=1103398 loops=1)
dn_6019_6020 (actual time=6193.957..7257.189 rows=1104285 loops=1)
dn_6021_6022 (actual time=1377.906..2512.918 rows=1103483 loops=1)
dn_6023_6024 (actual time=6852.496..8829.465 rows=1104697 loops=1)
dn_6025_6026 (actual time=6880.862..8671.562 rows=1103945 loops=1)
dn_6027_6028 (actual time=6004.853..7046.498 rows=1103493 loops=1)
dn_6029_6030 (actual time=6281.077..7318.069 rows=1104496 loops=1)
dn_6031_6032 (actual time=6221.786..7257.997 rows=1104830 loops=1)
dn_6033_6034 (actual time=5871.689..7070.472 rows=1103426 loops=1)
dn_6035_6036 (actual time=1063.231..2349.170 rows=1103398 loops=1)
dn_6037_6038 (actual time=1297.474..2435.782 rows=1104367 loops=1)
dn_6039_6040 (actual time=5927.732..6969.697 rows=1104970 loops=1)
dn_6041_6042 (actual time=6144.291..7207.707 rows=1103119 loops=1)
dn_6043_6044 (actual time=5956.748..7014.334 rows=1104142 loops=1)
dn_6045_6046 (actual time=6401.117..7496.199 rows=1104854 loops=1)
dn_6047_6048 (actual time=6468.490..7469.760 rows=1104027 loops=1)
dn_6049_6050 (actual time=1552.705..2743.037 rows=1104439 loops=1)
dn_6051_6052 (actual time=1957.829..3278.334 rows=1103399 loops=1)
dn_6053_6054 (actual time=1783.518..3157.491 rows=1105546 loops=1)
dn_6055_6056 (actual time=1710.180..2910.375 rows=1105427 loops=1)
dn_6057_6058 (actual time=1748.144..3076.594 rows=1104286 loops=1)
dn_6059_6060 (actual time=2383.635..3696.429 rows=1105574 loops=1)
dn_6061_6062 (actual time=1610.678..2941.240 rows=1104365 loops=1)
dn_6063_6064 (actual time=2634.579..3748.845 rows=1104034 loops=1)
dn_6065_6066 (actual time=6293.008..7431.072 rows=1105975 loops=1)
dn_6067_6068 (actual time=1100.478..2317.355 rows=1103025 loops=1)
dn_6069_6070 (actual time=6129.560..7238.487 rows=1105559 loops=1)
dn_6071_6072 (actual time=11304.785..12652.553 rows=1104894 loops=1)
dn_6073_6074 (actual time=6464.181..7699.966 rows=1103189 loops=1)
dn_6075_6076 (actual time=1661.428..3105.195 rows=1104274 loops=1)
dn_6077_6078 (actual time=6578.312..7753.859 rows=1104182 loops=1)
dn_6079_6080 (actual time=11254.108..12660.467 rows=1103221 loops=1)
dn_6081_6082 (actual time=1543.359..2949.387 rows=1104433 loops=1)
dn_6083_6084 (actual time=1376.259..2574.678 rows=1104818 loops=1)
dn_6085_6086 (actual time=6025.144..7393.553 rows=1103785 loops=1)
dn_6087_6088 (actual time=1225.916..2500.245 rows=1105892 loops=1)
dn_6089_6090 (actual time=6237.519..7321.983 rows=1102690 loops=1)
dn_6091_6092 (actual time=1262.221..2455.822 rows=1104858 loops=1)
dn_6093_6094 (actual time=1648.880..2988.664 rows=1103894 loops=1)
dn_6095_6096 (actual time=6058.349..7218.655 rows=1104778 loops=1)
dn_6097_6098 (actual time=1805.818..3045.418 rows=1103350 loops=1)
dn_6099_6100 (actual time=1183.156..2470.106 rows=1105439 loops=1)
dn_6101_6102 (actual time=1378.548..2682.999 rows=1103238 loops=1)
dn_6103_6104 (actual time=6261.463..7441.360 rows=1104087 loops=1)
dn_6105_6106 (actual time=1932.658..3171.675 rows=1103896 loops=1)
dn_6107_6108 (actual time=1828.937..4149.937 rows=1105315 loops=1)
dn_6109_6110 (actual time=5999.260..7186.210 rows=1105894 loops=1)
dn_6111_6112 (actual time=6081.135..7276.277 rows=1103891 loops=1)
dn_6113_6114 (actual time=1319.674..2442.375 rows=1097390 loops=1)
dn_6115_6116 (actual time=6326.786..7336.796 rows=1097839 loops=1)
dn_6117_6118 (actual time=6452.541..7487.117 rows=1099426 loops=1)
dn_6119_6120 (actual time=6268.302..7275.619 rows=1097518 loops=1)
dn_6121_6122 (actual time=1628.995..2703.431 rows=1096136 loops=1)
dn_6123_6124 (actual time=6401.294..7401.386 rows=1100152 loops=1)
dn_6125_6126 (actual time=1505.680..2773.485 rows=1097165 loops=1)
dn_6127_6128 (actual time=1372.936..2528.941 rows=1099710 loops=1)
dn_6129_6130 (actual time=1010.268..2037.988 rows=1097108 loops=1)
dn_6131_6132 (actual time=10893.470..12131.711 rows=1095719 loops=1)
dn_6133_6134 (actual time=1135.451..2140.456 rows=1096904 loops=1)
dn_6135_6136 (actual time=10862.098..12092.043 rows=1096846 loops=1)
dn_6137_6138 (actual time=5899.467..6919.706 rows=1097159 loops=1)
dn_6139_6140 (actual time=1001.900..2092.616 rows=1097157 loops=1)
dn_6141_6142 (actual time=5838.976..6856.455 rows=1098561 loops=1)
dn_6143_6144 (actual time=5899.380..6917.391 rows=1097908 loops=1)
dn_6145_6146 (actual time=1161.756..2237.519 rows=1097771 loops=1)
dn_6147_6148 (actual time=1124.467..2265.717 rows=1097375 loops=1)
dn_6149_6150 (actual time=1131.275..2169.603 rows=1097625 loops=1)
dn_6151_6152 (actual time=1086.316..2125.843 rows=1098497 loops=1)
dn_6153_6154 (actual time=1061.433..2111.528 rows=1097768 loops=1)
dn_6155_6156 (actual time=5864.687..6982.171 rows=1098993 loops=1)
dn_6157_6158 (actual time=5931.068..6990.153 rows=1097333 loops=1)
dn_6159_6160 (actual time=5974.744..7107.398 rows=1099147 loops=1)
dn_6161_6162 (actual time=1052.636..2149.850 rows=1097546 loops=1)
dn_6163_6164 (actual time=5797.849..6819.558 rows=1098953 loops=1)
dn_6165_6166 (actual time=1005.942..2046.266 rows=1097542 loops=1)
dn_6167_6168 (actual time=1049.419..2073.274 rows=1099133 loops=1)
dn_6169_6170 (actual time=5880.616..6916.874 rows=1097577 loops=1)
dn_6171_6172 (actual time=1066.553..2094.504 rows=1096869 loops=1)
dn_6173_6174 (actual time=5914.734..6956.801 rows=1098123 loops=1)
dn_6175_6176 (actual time=5894.262..6912.879 rows=1097184 loops=1)
dn_6177_6178 (actual time=5878.343..6935.998 rows=1098216 loops=1)
dn_6179_6180 (actual time=1026.712..2092.953 rows=1097801 loops=1)
dn_6181_6182 (actual time=5946.536..6968.467 rows=1096704 loops=1)
dn_6183_6184 (actual time=5935.293..6952.496 rows=1098335 loops=1)
dn_6185_6186 (actual time=1077.780..2094.872 rows=1097904 loops=1)
dn_6187_6188 (actual time=5910.048..6920.227 rows=1097542 loops=1)
dn_6189_6190 (actual time=5921.636..6980.902 rows=1099006 loops=1)
dn_6191_6192 (actual time=5887.854..6935.619 rows=1098027 loops=1)
dn_6193_6194 (actual time=1193.592..2333.747 rows=1095919 loops=1)
dn_6195_6196 (actual time=1220.639..2389.164 rows=1097070 loops=1)
dn_6197_6198 (actual time=1411.800..2593.765 rows=1098370 loops=1)
dn_6199_6200 (actual time=1220.645..2451.923 rows=1096786 loops=1)
dn_6201_6202 (actual time=5865.711..6934.471 rows=1097641 loops=1)
dn_6203_6204 (actual time=1262.015..2401.812 rows=1097527 loops=1)
dn_6205_6206 (actual time=1130.688..2260.146 rows=1098097 loops=1)
dn_6207_6208 (actual time=1172.719..2306.405 rows=1098105 loops=1)
ID 5 --Vector Streaming(type: REDISTRIBUTE)
dn_6001_6002 (actual time=2399558.035..2399558.035 rows=0 loops=1)
dn_6003_6004 (actual time=2399475.292..2399475.292 rows=0 loops=1)
dn_6005_6006 (actual time=2399545.139..2399545.139 rows=0 loops=1)
dn_6007_6008 (actual time=2399589.214..2399589.214 rows=0 loops=1)
dn_6009_6010 (actual time=2399548.761..2399548.761 rows=0 loops=1)
dn_6011_6012 (actual time=2399419.861..2399419.861 rows=0 loops=1)
dn_6013_6014 (actual time=2399563.043..2399563.043 rows=0 loops=1)
dn_6015_6016 (actual time=2399535.237..2399535.237 rows=0 loops=1)
dn_6017_6018 (actual time=2399561.245..2399561.245 rows=0 loops=1)
dn_6019_6020 (actual time=2399582.068..2399582.068 rows=0 loops=1)
dn_6021_6022 (actual time=2399530.854..2399530.854 rows=0 loops=1)
dn_6023_6024 (actual time=2399560.528..2399560.528 rows=0 loops=1)
dn_6025_6026 (actual time=2399514.307..2399514.307 rows=0 loops=1)
dn_6027_6028 (actual time=2399581.332..2399581.332 rows=0 loops=1)
dn_6029_6030 (actual time=2399507.324..2399507.324 rows=0 loops=1)
dn_6031_6032 (actual time=2399584.535..2399584.535 rows=0 loops=1)
dn_6033_6034 (actual time=2399531.148..2399531.148 rows=0 loops=1)
dn_6035_6036 (actual time=2399527.619..2399527.619 rows=0 loops=1)
dn_6037_6038 (actual time=2399553.395..2399553.395 rows=0 loops=1)
dn_6039_6040 (actual time=2399588.625..2399588.625 rows=0 loops=1)
dn_6041_6042 (actual time=2399549.438..2399549.438 rows=0 loops=1)
dn_6043_6044 (actual time=2399555.716..2399555.716 rows=0 loops=1)
dn_6045_6046 (actual time=2399536.518..2399536.518 rows=0 loops=1)
dn_6047_6048 (actual time=2399558.467..2399558.467 rows=0 loops=1)
dn_6049_6050 (actual time=2399499.283..2399499.283 rows=0 loops=1)
dn_6051_6052 (actual time=2399537.776..2399537.776 rows=0 loops=1)
dn_6053_6054 (actual time=2399566.793..2399566.793 rows=0 loops=1)
dn_6055_6056 (actual time=2399538.241..2399538.241 rows=0 loops=1)
dn_6057_6058 (actual time=2399458.894..2399458.894 rows=0 loops=1)
dn_6059_6060 (actual time=2399574.637..2399574.637 rows=0 loops=1)
dn_6061_6062 (actual time=2399525.327..2399525.327 rows=0 loops=1)
dn_6063_6064 (actual time=2399541.275..2399541.275 rows=0 loops=1)
dn_6065_6066 (actual time=2399582.741..2399582.741 rows=0 loops=1)
dn_6067_6068 (actual time=2399552.960..2399552.960 rows=0 loops=1)
dn_6069_6070 (actual time=2399566.121..2399566.121 rows=0 loops=1)
dn_6071_6072 (actual time=2399585.275..2399585.275 rows=0 loops=1)
dn_6073_6074 (actual time=2399576.782..2399576.782 rows=0 loops=1)
dn_6075_6076 (actual time=2399596.552..2399596.552 rows=0 loops=1)
dn_6077_6078 (actual time=2399573.194..2399573.194 rows=0 loops=1)
dn_6079_6080 (actual time=2399580.191..2399580.191 rows=0 loops=1)
dn_6081_6082 (actual time=2399579.500..2399579.500 rows=0 loops=1)
dn_6083_6084 (actual time=2399501.197..2399501.197 rows=0 loops=1)
dn_6085_6086 (actual time=2399550.735..2399550.735 rows=0 loops=1)
dn_6087_6088 (actual time=2399583.684..2399583.684 rows=0 loops=1)
dn_6089_6090 (actual time=2399502.715..2399502.715 rows=0 loops=1)
dn_6091_6092 (actual time=2399581.526..2399581.526 rows=0 loops=1)
dn_6093_6094 (actual time=2399568.435..2399568.435 rows=0 loops=1)
dn_6095_6096 (actual time=2399600.564..2399600.564 rows=0 loops=1)
dn_6097_6098 (actual time=2399571.299..2399571.299 rows=0 loops=1)
dn_6099_6100 (actual time=2399591.665..2399591.665 rows=0 loops=1)
dn_6101_6102 (actual time=2399525.756..2399525.756 rows=0 loops=1)
dn_6103_6104 (actual time=2399584.860..2399584.860 rows=0 loops=1)
dn_6105_6106 (actual time=2399591.821..2399591.821 rows=0 loops=1)
dn_6107_6108 (actual time=2399558.313..2399558.313 rows=0 loops=1)
dn_6109_6110 (actual time=2399569.707..2399569.707 rows=0 loops=1)
dn_6111_6112 (actual time=2399552.663..2399552.663 rows=0 loops=1)
dn_6113_6114 (actual time=2399561.468..2399561.468 rows=0 loops=1)
dn_6115_6116 (actual time=2399533.713..2399533.713 rows=0 loops=1)
dn_6117_6118 (actual time=2399554.476..2399554.476 rows=0 loops=1)
dn_6119_6120 (actual time=2399591.800..2399591.800 rows=0 loops=1)
dn_6121_6122 (actual time=2399567.725..2399567.725 rows=0 loops=1)
dn_6123_6124 (actual time=2399567.624..2399567.624 rows=0 loops=1)
dn_6125_6126 (actual time=2399555.916..2399555.916 rows=0 loops=1)
dn_6127_6128 (actual time=2399551.622..2399551.622 rows=0 loops=1)
dn_6129_6130 (actual time=2399565.368..2399565.368 rows=0 loops=1)
dn_6131_6132 (actual time=2399584.449..2399584.449 rows=0 loops=1)
dn_6133_6134 (actual time=2399554.033..2399554.033 rows=0 loops=1)
dn_6135_6136 (actual time=2399575.644..2399575.644 rows=0 loops=1)
dn_6137_6138 (actual time=2399552.676..2399552.676 rows=0 loops=1)
dn_6139_6140 (actual time=2399526.686..2399526.686 rows=0 loops=1)
dn_6141_6142 (actual time=2399594.100..2399594.100 rows=0 loops=1)
dn_6143_6144 (actual time=2399577.612..2399577.612 rows=0 loops=1)
dn_6145_6146 (actual time=2399584.418..2399584.418 rows=0 loops=1)
dn_6147_6148 (actual time=2399586.412..2399586.412 rows=0 loops=1)
dn_6149_6150 (actual time=2399582.269..2399582.269 rows=0 loops=1)
dn_6151_6152 (actual time=2399588.554..2399588.554 rows=0 loops=1)
dn_6153_6154 (actual time=2399555.417..2399555.417 rows=0 loops=1)
dn_6155_6156 (actual time=1079.359..150152.267 rows=114538351 loops=1)
dn_6157_6158 (actual time=2399547.243..2399547.243 rows=0 loops=1)
dn_6159_6160 (actual time=2399592.154..2399592.154 rows=0 loops=1)
dn_6161_6162 (actual time=2399571.778..2399571.778 rows=0 loops=1)
dn_6163_6164 (actual time=2399597.023..2399597.023 rows=0 loops=1)
dn_6165_6166 (actual time=2399543.225..2399543.225 rows=0 loops=1)
dn_6167_6168 (actual time=2399571.988..2399571.988 rows=0 loops=1)
dn_6169_6170 (actual time=2399608.585..2399608.585 rows=0 loops=1)
dn_6171_6172 (actual time=2399595.292..2399595.292 rows=0 loops=1)
dn_6173_6174 (actual time=2399587.464..2399587.464 rows=0 loops=1)
dn_6175_6176 (actual time=2399596.388..2399596.388 rows=0 loops=1)
dn_6177_6178 (actual time=2399573.442..2399573.442 rows=0 loops=1)
dn_6179_6180 (actual time=2399565.145..2399565.145 rows=0 loops=1)
dn_6181_6182 (actual time=2399583.348..2399583.348 rows=0 loops=1)
dn_6183_6184 (actual time=2399588.915..2399588.915 rows=0 loops=1)
dn_6185_6186 (actual time=2399569.877..2399569.877 rows=0 loops=1)
dn_6187_6188 (actual time=2399585.274..2399585.274 rows=0 loops=1)
dn_6189_6190 (actual time=2399570.084..2399570.084 rows=0 loops=1)
dn_6191_6192 (actual time=2399580.619..2399580.619 rows=0 loops=1)
dn_6193_6194 (actual time=2399585.018..2399585.018 rows=0 loops=1)
dn_6195_6196 (actual time=2399582.460..2399582.460 rows=0 loops=1)
dn_6197_6198 (actual time=2399466.722..2399466.722 rows=0 loops=1)
dn_6199_6200 (actual time=2399530.122..2399530.122 rows=0 loops=1)
dn_6201_6202 (actual time=2399504.559..2399504.559 rows=0 loops=1)
dn_6203_6204 (actual time=2399577.654..2399577.654 rows=0 loops=1)
dn_6205_6206 (actual time=2399552.371..2399552.371 rows=0 loops=1)
dn_6207_6208 (actual time=2399553.593..2399553.593 rows=0 loops=1)
对比两段，我们很明显可以看到，在 id 6 --Vector Result 数据是分布在所有的节点，进行并行处理
但是到了数据重分布阶段 id 5 --Vector Streaming(type: REDISTRIBUTE)
只有
dn_6155_6156 (actual time=1079.359..150152.267 rows=114538351 loops=1)
rows=114538351条数据，其他dn的rows都=0 这个正好是这张表的全部数据
也就意味着，这表表的数据，在第一阶段全部重分布到了dn_6155_6156这个节点上，而后续的操作
4 --Vector Sort
3 --Vector WindowAgg
2 --Vector Streaming (type: GATHER)
1 --Row Adapter
也全是在dn_6155_6156这个节点上进行的，这就很好的解释了，开始定位数据库线程等待情况的时候，所有的节点，全部在等待dn_6155_6156的原因。
很显然第四阶段，1亿多条数据，进行五个字段的排序，必然执行时间会特别长
Sort Key: pd_stagearea.txn_ng_order_d2_u.entry_date, pd_stagearea.txn_ng_order_d2_u.sec_code, pd_stagearea.txn_ng_order_d2_u.last_upd_time, pd_stagearea.txn_ng_order_d2_u.last_upd_time_dec, pd_stagearea.txn_ng_order_d2_u.order_no
致此，我们已经初步推断出问题的原因：
SQL语句中的这个分析函数
RANK () over( partition BY Entry_Date ,Sec_Code ORDER BY Last_Upd_Time ASC ,Last_Upd_Time_Dec ASC ,Order_No ASC )
会以Entry_Date字段进行数据的重分布，但是由于某些原因，导致表上全部的数据，全部重分布到了一个节点上，出现了常见的一个节点有难，103个节点围观的现场。
那么为什么会出现所有的数据，重分布到一个节点上呢？
以entry_date为字段查询数据分布
nebula_kcb=# select entry_date,count(*) from PD_STAGEAREA.TXN_NG_Order_D2_U GROUP BY entry_date;
entry_date | count
---------------------+-----------
2013-12-25 00:00:00 | 114538351
(1 row)
原因就很清晰了
这张表中，所有的数据entry_date都是同一天，要知道分布式数据库进行数据重分布的时候，是以hash值来选择节点存放数据的。所有的数据都在同一天，也就导致了最终重分布完的数据全部在同一个dn上。最终导致了SQL语句的性能问题。
后续我们只需要改进partition BY中，选择合适的分步键，即可解决这个SQL语句的性能问题
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
