yes if some table have many operation (insert / update) in 24 hours, the query performance go down significantly mrs is not col-storage i guess ... View more

I know it will be nested loop join But the performance is too low, it look like the operator 'like' has bug in this situation If i use 'locate' function instead of 'like', it's much more faster   ---LIKE--- select count(*) from test_like t1 left join test_like t2 on t1.full_id like t2.full_id Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail -------------------------------------------------------------------------------------------------------------------------- 06:AGGREGATE 1 0.000ns 0.000ns 1 1 16.00 KB 10.00 MB FINALIZE 05:EXCHANGE 1 0.000ns 0.000ns 1 1 16.00 KB 16.00 KB UNPARTITIONED 03:AGGREGATE 1 16.000ms 16.000ms 1 1 135.00 KB 10.00 MB 02:NESTED LOOP JOIN 1 13s708ms 13s708ms 1.00M -1 147.00 KB 2.00 GB LEFT OUTER JOIN, BROADCAST |--04:EXCHANGE 1 0.000ns 0.000ns 1.00K -1 72.00 KB 56.99 KB BROADCAST | 01:SCAN KUDU 1 4.000ms 4.000ms 1.00K -1 59.00 KB 384.00 KB default.test_like t2 00:SCAN KUDU 1 36.001ms 36.001ms 1.00K -1 59.00 KB 384.00 KB default.test_like t1 ---LOCATE--- select count(*) from test_like t1 left join test_like t2 on locate(t1.full_id, t2.full_id) > 0 Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail ---------------------------------------------------------------------------------------------------------------------------- 06:AGGREGATE 1 0.000ns 0.000ns 1 1 16.00 KB 10.00 MB FINALIZE 05:EXCHANGE 1 0.000ns 0.000ns 1 1 16.00 KB 16.00 KB UNPARTITIONED 03:AGGREGATE 1 68.001ms 68.001ms 1 1 135.00 KB 10.00 MB 02:NESTED LOOP JOIN 1 460.009ms 460.009ms 1.00M -1 147.00 KB 2.00 GB LEFT OUTER JOIN, BROADCAST |--04:EXCHANGE 1 0.000ns 0.000ns 1.00K -1 72.00 KB 56.99 KB BROADCAST | 01:SCAN KUDU 1 4.000ms 4.000ms 1.00K -1 59.00 KB 384.00 KB default.test_like t2 00:SCAN KUDU 1 0.000ns 0.000ns 1.00K -1 59.00 KB 384.00 KB default.test_like t1 ... View more

Yes, KUDU-1400 is a reasonable explanation here. It's not exactly a bug, just a lack of a feature. In any case, the next release of Kudu has an enhanced compaction procedure that will handle this case. ... View more