Tracking: improve TPC-H performance

[lmatz]

See performance numbers at https://www.notion.so/risingwave-labs/TPCH-Performance-Numbers-Table-e098ef82884546949333409f0513ada7?pvs=4#8de0bf4bda51444c8381f3b0c10ddfe1

• perf: improve tpc-h q1 performance (single-topic) #15034
• perf: improve tpc-h q4 performance (single-topic) #14811
• perf: improve tpc-h q6 performance (single-topic) #15035
• perf: improve tpc-h q17 performance (single-topic) #14799
• perf: improve tpc-h q20 performance (single-topic) #14797

Improvements needed:

• too aggressive and early cache eviction #15305 -> Q4 has such a phenomenon but is less affected. Q17 and Q20 are greatly affected.
• better query rewrite for TPC-H q17 #15247 -> Q17
• perf: throughput of stateless query with no computation drops 20% over time #14815 -> Q1,Q6
• potential cpu usage improvement for metrics #15478 -> Q4 but also in general

[lmatz]

2024-02-22

A temporary summary:

1. q1 and q6 requires perf: throughput of stateless query with no computation drops 20% over time #14815
2. q17's plan is still suboptimal
3. q4's bottleneck is in LeftSemiJoin
4. q20's bottleneck is also likely to be LeftSemiJoin as it has 2 LeftSemiJoin out of total 4 joins

2024-02-26

after some investigation:

q4's bottleneck is in LeftSemiJoin

Changing the barrier of both systems to 10s and using the latest image, the performance is about the same.

q20's bottleneck is also likely to be LeftSemiJoin as it has 2 LeftSemiJoin out of total 4 joins

The bottleneck is in the subquery, see #14797 for details

1 and 2 remains

2024-02-28

q20 reveals a potential problem in cache eviction strategy: #15305

2024-02-29

q4 also has a very similar observation caused by the cache eviction strategy: #14811 (comment)
Q4's LocalStoreIter::{Closure} takes an substantial amount of CPU time. See threads in the issue of Q4.