SELECT *
FROM “es-test”.“cif.apply_main”.“default” where _id in (SELECT _id FROM “es-test”.“cif.tom_orders”.“default” where _id < 5000)
Plan below:
00-00 Screen : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition): rowcount = 1.6481603E7, cumulative cost = {2.2763095361E8 rows, 1.539105859145E9 cpu, 0.0 io, 8.794592673792E11 network, 8.3524012088E7 memory}, id = 345641
00-01 Project(Fragment=[$0], Records=[$1], Path=[$2], Metadata=[$3], Partition=[$4]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition): rowcount = 1.6481603E7, cumulative cost = {2.2598279331E8 rows, 1.537457698845E9 cpu, 0.0 io, 8.794592673792E11 network, 8.3524012088E7 memory}, id = 345640
00-02 WriterCommitter(temp=[/home/databus/data/dremio/pdfs/results/.25ab7df7-bee3-6122-b446-0c1a83fcce00-1515487751914], final=[/home/databus/data/dremio/pdfs/results/25ab7df7-bee3-6122-b446-0c1a83fcce00]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition): rowcount = 1.6481603E7, cumulative cost = {2.0950119031E8 rows, 1.455049683845E9 cpu, 0.0 io, 8.794592673792E11 network, 8.3524012088E7 memory}, id = 345639
00-03 UnionExchange : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition): rowcount = 1.6481603E7, cumulative cost = {1.9301958731E8 rows, 1.438568080845E9 cpu, 0.0 io, 8.794592673792E11 network, 8.3524012088E7 memory}, id = 345638
01-01 Writer : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition): rowcount = 1.6481603E7, cumulative cost = {1.7653798431E8 rows, 1.306715256845E9 cpu, 0.0 io, 5.419160379392E11 network, 8.3524012088E7 memory}, id = 345637
01-02 Project(contract_no=[$0], apply_no=[$1], product_code=[$2], _index=[$3], _type=[$4], _id=[$5], _uid=[$6]) : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid): rowcount = 1.6481603E7, cumulative cost = {1.6005638131E8 rows, 1.290233653845E9 cpu, 0.0 io, 5.419160379392E11 network, 8.3524012088E7 memory}, id = 345636
01-03 Project(contract_no=[$0], apply_no=[$1], product_code=[$2], _index=[$3], _type=[$4], _id=[$5], _uid=[$6]) : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid): rowcount = 1.6481603E7, cumulative cost = {1.4357477831E8 rows, 1.174862432845E9 cpu, 0.0 io, 5.419160379392E11 network, 8.3524012088E7 memory}, id = 345635
01-04 HashJoin(condition=[=($5, $7)], joinType=[inner]) : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid, VARCHAR(65536) _id0): rowcount = 1.6481603E7, cumulative cost = {1.2709317531E8 rows, 1.059491211845E9 cpu, 0.0 io, 5.419160379392E11 network, 8.3524012088E7 memory}, id = 345634
01-06 Project(contract_no=[$0], apply_no=[$1], product_code=[$2], _index=[$3], _type=[$4], _id=[$5], _uid=[$6]) : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid): rowcount = 1.6481603E7, cumulative cost = {8.2408015E7 rows, 6.09819311E8 cpu, 0.0 io, 5.40069167104E11 network, 0.0 memory}, id = 345624
01-08 HashToRandomExchange(dist0=[[$5]]) : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 1.6481603E7, cumulative cost = {6.5926412E7 rows, 4.9444809E8 cpu, 0.0 io, 5.40069167104E11 network, 0.0 memory}, id = 345623
02-01 UnorderedMuxExchange : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 1.6481603E7, cumulative cost = {4.9444809E7 rows, 2.30742442E8 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 345622
04-01 Project(contract_no=[$0], apply_no=[$1], product_code=[$2], _index=[$3], _type=[$4], _id=[$5], _uid=[$6], E_X_P_R_H_A_S_H_F_I_E_L_D=[hash32($5)]) : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 1.6481603E7, cumulative cost = {3.2963206E7 rows, 2.14260839E8 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 345621
04-02 ElasticScan(resource=[cif.apply_main/default], columns=[[contract_no, apply_no, product_code, _index, _type, _id, _uid]], pushdown
=[{
“from” : 0,
“size” : 4000,
“query” : {
“match_all” : { }
}
}]) : rowType = RecordType(VARCHAR(65536) contract_no, VARCHAR(65536) apply_no, VARCHAR(65536) product_code, VARCHAR(65536) _index, VARCHAR(65536) _type, VARCHAR(65536) _id, VARCHAR(65536) _uid): rowcount = 1.6481603E7, cumulative cost = {1.6481603E7 rows, 1.6481603E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 345452
01-05 Project(_id0=[$0]) : rowType = RecordType(VARCHAR(65536) _id0): rowcount = 11272.404999999995, cumulative cost = {2.819228490500001E7 rows, 1.1994966160499997E8 cpu, 0.0 io, 1.8468708351999996E9 network, 8.332561776E7 memory}, id = 345633
01-07 HashAgg(group=[{0}]) : rowType = RecordType(VARCHAR(65536) _id): rowcount = 11272.404999999995, cumulative cost = {2.8181012500000007E7 rows, 1.1993838919999997E8 cpu, 0.0 io, 1.8468708351999996E9 network, 8.332561776E7 memory}, id = 345632
01-09 Project(_id=[$0]) : rowType = RecordType(VARCHAR(65536) _id): rowcount = 225448.09999999995, cumulative cost = {2.7955564400000006E7 rows, 1.1813480439999998E8 cpu, 0.0 io, 1.8468708351999996E9 network, 7.93577312E7 memory}, id = 345631
01-10 HashToRandomExchange(dist0=[[$0]]) : rowType = RecordType(VARCHAR(65536) _id, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 225448.09999999995, cumulative cost = {2.7730116300000004E7 rows, 1.1790935629999998E8 cpu, 0.0 io, 1.8468708351999996E9 network, 7.93577312E7 memory}, id = 345630
03-01 UnorderedMuxExchange : rowType = RecordType(VARCHAR(65536) _id, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 225448.09999999995, cumulative cost = {2.7504668200000003E7 rows, 1.1430218669999999E8 cpu, 0.0 io, 0.0 network, 7.93577312E7 memory}, id = 345629
05-01 Project(_id=[$0], E_X_P_R_H_A_S_H_F_I_E_L_D=[hash32($0)]) : rowType = RecordType(VARCHAR(65536) _id, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 225448.09999999995, cumulative cost = {2.72792201E7 rows, 1.140767386E8 cpu, 0.0 io, 0.0 network, 7.93577312E7 memory}, id = 345628
05-02 HashAgg(group=[{0}]) : rowType = RecordType(VARCHAR(65536) _id): rowcount = 225448.09999999995, cumulative cost = {2.7053772E7 rows, 1.1272405E8 cpu, 0.0 io, 0.0 network, 7.93577312E7 memory}, id = 345627
05-03 SelectionVectorRemover : rowType = RecordType(VARCHAR(65536) _id): rowcount = 4508962.0, cumulative cost = {2.254481E7 rows, 7.6652354E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 345626
05-04 Filter(condition=[<(CAST($0):BIGINT, 5000)]) : rowType = RecordType(VARCHAR(65536) _id): rowcount = 4508962.0, cumulative cost = {1.8035848E7 rows, 7.2143392E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 345625
05-05 ElasticScan(resource=[cif.tom_orders/default], columns=[[_id]], pushdown
=[{
“from” : 0,
“size” : 4000,
“query” : {
“match_all” : { }
}
}]) : rowType = RecordType(VARCHAR(65536) _id): rowcount = 9017924.0, cumulative cost = {9017924.0 rows, 9017924.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 345455