Long window optimization effect for Real-time Feature processing is not obvious

**Bug Description**
version：openmldb 0.6.1
dataset:20220814-20220821
total data：870w
Real-time Feature Processing
remove w10d： 9.5k qps
with w10d：130 qps

CREATE TABLE deployment_test_for_error(
event_id STRING,
type STRING,
p0 STRING,
p1 STRING,
p2 STRING,
p3 STRING,
p4 STRING,
p5 STRING,
p6 STRING,
p7 STRING,
event_occur_time TIMESTAMP,
report_time TIMESTAMP,
app_ver STRING,
req_id STRING,
device_name STRING,
pro_ver STRING, INDEX(KEY=event_id, TS=event_occur_time, ttl_type=absolute, ttl=60d),INDEX(KEY=(device_name,pro_ver), TS=event_occur_time, ttl_type=absolute, ttl=60d), INDEX(KEY=(app_ver,pro_ver), TS=event_occur_time, ttl_type=absolute, ttl=60d)
) OPTIONS(partitionnum=8, replicanum=3);

DEPLOY demo_lw OPTIONS(long_windows="cat_w10d:1d,ab_w10d:1d")
select
count_where(device_name, pro_ver = '12.0') OVER ab_w10m AS service_click_w10m_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w30m AS service_click_w30m_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w1h AS service_click_w1h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w3h AS service_click_w3h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w7h AS service_click_w7h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w12h AS service_click_w12h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w1d AS service_click_w1d_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w10d AS service_click_w10d_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w10m AS service_subcribe_w10m_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w30m AS service_subcribe_w30m_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w1h AS service_subcribe_w1h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w3h AS service_subcribe_w3h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w7h AS service_subcribe_w7h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w12h AS service_subcribe_w12h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w1d AS service_subcribe_w1d_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w10d AS service_subcribe_w10d_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w10m AS category_click_w10m_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w30m AS category_click_w30m_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w1h AS category_click_w1h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w3h AS category_click_w3h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w7h AS category_click_w7h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w12h AS category_click_w12h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w1d AS category_click_w1d_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w10d AS category_click_w10d_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w10m AS category_subcribe_w10m_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w30m AS category_subcribe_w30m_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w1h AS category_subcribe_w1h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w3h AS category_subcribe_w3h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w7h AS category_subcribe_w7h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w12h AS category_subcribe_w12h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w1d AS category_subcribe_w1d_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w10d AS category_subcribe_w10d_cnt
FROM deployment_test_for_error
WINDOW ab_w10m AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10m PRECEDING AND CURRENT ROW),
ab_w30m AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 30m PRECEDING AND CURRENT ROW),
ab_w1h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1h PRECEDING AND CURRENT ROW),
ab_w3h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 3h PRECEDING AND CURRENT ROW),
ab_w7h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 7h PRECEDING AND CURRENT ROW),
ab_w12h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 12h PRECEDING AND CURRENT ROW),
ab_w1d AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1d PRECEDING AND CURRENT ROW),
ab_w10d AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10d PRECEDING AND CURRENT ROW),
cat_w10m AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10m PRECEDING AND CURRENT ROW),
cat_w30m AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 30m PRECEDING AND CURRENT ROW),
cat_w1h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1h PRECEDING AND CURRENT ROW),
cat_w3h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 3h PRECEDING AND CURRENT ROW),
cat_w7h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 7h PRECEDING AND CURRENT ROW),
cat_w12h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 12h PRECEDING AND CURRENT ROW),
cat_w1d AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1d PRECEDING AND CURRENT ROW),
cat_w10d AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10d PRECEDING AND CURRENT ROW);

DEPLOY demo
select
count_where(device_name, pro_ver = '12.0') OVER ab_w10m AS service_click_w10m_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w30m AS service_click_w30m_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w1h AS service_click_w1h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w3h AS service_click_w3h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w7h AS service_click_w7h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w12h AS service_click_w12h_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w1d AS service_click_w1d_cnt,
count_where(device_name, pro_ver = '12.0') OVER ab_w10d AS service_click_w10d_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w10m AS service_subcribe_w10m_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w30m AS service_subcribe_w30m_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w1h AS service_subcribe_w1h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w3h AS service_subcribe_w3h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w7h AS service_subcribe_w7h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w12h AS service_subcribe_w12h_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w1d AS service_subcribe_w1d_cnt,
count_where(device_name, pro_ver = '6.1') OVER ab_w10d AS service_subcribe_w10d_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w10m AS category_click_w10m_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w30m AS category_click_w30m_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w1h AS category_click_w1h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w3h AS category_click_w3h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w7h AS category_click_w7h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w12h AS category_click_w12h_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w1d AS category_click_w1d_cnt,
count_where(app_ver, pro_ver = '12.0') OVER cat_w10d AS category_click_w10d_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w10m AS category_subcribe_w10m_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w30m AS category_subcribe_w30m_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w1h AS category_subcribe_w1h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w3h AS category_subcribe_w3h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w7h AS category_subcribe_w7h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w12h AS category_subcribe_w12h_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w1d AS category_subcribe_w1d_cnt,
count_where(app_ver, pro_ver = '6.1') OVER cat_w10d AS category_subcribe_w10d_cnt
FROM deployment_test_for_error
WINDOW ab_w10m AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10m PRECEDING AND CURRENT ROW),
ab_w30m AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 30m PRECEDING AND CURRENT ROW),
ab_w1h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1h PRECEDING AND CURRENT ROW),
ab_w3h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 3h PRECEDING AND CURRENT ROW),
ab_w7h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 7h PRECEDING AND CURRENT ROW),
ab_w12h AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 12h PRECEDING AND CURRENT ROW),
ab_w1d AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1d PRECEDING AND CURRENT ROW),
ab_w10d AS (PARTITION BY deployment_test_for_error.device_name,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10d PRECEDING AND CURRENT ROW),
cat_w10m AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10m PRECEDING AND CURRENT ROW),
cat_w30m AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 30m PRECEDING AND CURRENT ROW),
cat_w1h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1h PRECEDING AND CURRENT ROW),
cat_w3h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 3h PRECEDING AND CURRENT ROW),
cat_w7h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 7h PRECEDING AND CURRENT ROW),
cat_w12h AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 12h PRECEDING AND CURRENT ROW),
cat_w1d AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 1d PRECEDING AND CURRENT ROW),
cat_w10d AS (PARTITION BY deployment_test_for_error.app_ver,deployment_test_for_error.pro_ver ORDER BY deployment_test_for_error.event_occur_time ROWS_RANGE BETWEEN 10d PRECEDING AND CURRENT ROW);

`10.97.152.110:6527/> select * from __INTERNAL_DB.PRE_AGG_META_INFO;
------------------------------------------------------------- -------------- --------- --------------------------- ------------- ------------- ---------------------- ------------------ ------------- ------------
aggr_table aggr_db base_db base_table aggr_func aggr_col partition_cols order_by_col bucket_size filter_col
------------------------------------------------------------- -------------- --------- --------------------------- ------------- ------------- ---------------------- ------------------ ------------- ------------
pre_demo_db_demo_lw_cat_w10d_count_where_app_ver_pro_ver __PRE_AGG_DB demo_db deployment_test_for_error count_where app_ver app_ver,pro_ver event_occur_time 1d pro_ver
pre_demo_db_demo_lw_cat_w10d_count_where_app_ver_pro_ver __PRE_AGG_DB demo_db deployment_test_for_error count_where app_ver app_ver,pro_ver event_occur_time 1d pro_ver
pre_demo_db_demo_lw_ab_w10d_count_where_device_name_pro_ver __PRE_AGG_DB demo_db deployment_test_for_error count_where device_name device_name,pro_ver event_occur_time 1d pro_ver
pre_demo_db_demo_lw_ab_w10d_count_where_device_name_pro_ver __PRE_AGG_DB demo_db deployment_test_for_error count_where device_name device_name,pro_ver event_occur_time 1d pro_ver `

**Expected Behavior**

**Steps to Reproduce**

1.
2.
3.
4.

この issue では、OpenMLDB のリアルタイム feature 処理で長いウィンドウ (w10d) を使用するとパフォーマンスが低下すると報告されています。long_windows オプションを指定した SQL deployment を調べ、指定していないものと比較してください。query engine のウィンドウ最適化ロジックを確認してください。おそらく src/engine または同様のディレクトリにあります。提供されている SQL を実行して QPS の差を再現し、その後プロファイリングを行って、長いウィンドウの処理におけるボトルネックを特定してください。

索引モデルが issue の本文から書いたものです。