Potential performance challenges for index creation in big data scenarios

**Bug Description**

While conducting memory tests on OpenMLDB, I created an index on the "app" column of the TalkingData train dataset. However, I soon realized that creating an index on the 'app' column held no practical significance due to severe data skew in the "app" column. Nonetheless, during the process, I observed an abnormal phenomenon: the index creation task took over two days to complete, far beyond the expected time. This observation may indicate potential issues that could arise when creating indexes in scenarios involving large amounts of data.


**Expected Behavior**
The index creation task should be able to complete within a few minutes or a few tens of minutes.

**Steps to Reproduce**
1. get dataset from here: https://www.kaggle.com/c/talkingdata-adtracking-fraud-detection/data, download train.csv;
2. create db and table (test_talking_mem);
3. `LOAD DATA INFILE 'file:///tmp/train.csv' INTO TABLE test_talking_mem OPTIONS(delimiter = ',', mode = 'append');`
4. `CREATE INDEX idx_app ON test_talking_mem (app);`

**Some potentially useful information**

1. Table info

Table info

```json
{
"name": "test_talking_mem",
"table_partition":
[
{
"pid": 0,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp03:10922",
"is_leader": true,
"offset": 50113235,
"record_cnt": 23044430,
"record_byte_size": 5653552902,
"tablet_has_partition": true,
"diskused": 8434468909
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 23044430,
"record_byte_size": 5653552902,
"diskused": 8434468909
},
{
"pid": 1,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp02:10921",
"is_leader": true,
"offset": 24437207,
"record_cnt": 23070342,
"record_byte_size": 2466003867,
"tablet_has_partition": true,
"diskused": 2609541904
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 23070342,
"record_byte_size": 2466003867,
"diskused": 2609541904
},
{
"pid": 2,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp03:10922",
"is_leader": true,
"offset": 40330630,
"record_cnt": 23083002,
"record_byte_size": 4280679623,
"tablet_has_partition": true,
"diskused": 3809344191
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 23083002,
"record_byte_size": 4280679623,
"diskused": 3809344191
},
{
"pid": 3,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp02:10921",
"is_leader": true,
"offset": 29068056,
"record_cnt": 24328781,
"record_byte_size": 2979013120,
"tablet_has_partition": true,
"diskused": 3058134341
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 24328781,
"record_byte_size": 2979013120,
"diskused": 3058134341
},
{
"pid": 4,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp03:10922",
"is_leader": true,
"offset": 42039005,
"record_cnt": 22953742,
"record_byte_size": 4655333631,
"tablet_has_partition": true,
"diskused": 6806149618
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 22953742,
"record_byte_size": 4655333631,
"diskused": 6806149618
},
{
"pid": 5,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp02:10921",
"is_leader": true,
"offset": 50715685,
"record_cnt": 22860283,
"record_byte_size": 5464521228,
"tablet_has_partition": true,
"diskused": 4573758877
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 22860283,
"record_byte_size": 5464521228,
"diskused": 4573758877
},
{
"pid": 6,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp03:10922",
"is_leader": true,
"offset": 35192075,
"record_cnt": 22315199,
"record_byte_size": 3697029230,
"tablet_has_partition": true,
"diskused": 3359285519
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 22315199,
"record_byte_size": 3697029230,
"diskused": 3359285519
},
{
"pid": 7,
"partition_meta":
[
{
"endpoint": "m7-pce-hdp02:10921",
"is_leader": true,
"offset": 43004593,
"record_cnt": 23248111,
"record_byte_size": 4602049655,
"tablet_has_partition": true,
"diskused": 6754254073
}
],
"term_offset":
[
{
"term": 1,
"offset": 0
}
],
"record_cnt": 23248111,
"record_byte_size": 4602049655,
"diskused": 6754254073
}
],
"tid": 47,
"partition_num": 8,
"replica_num": 1,
"compress_type": "kNoCompress",
"column_desc":
[
{
"name": "ip",
"data_type": "kVarchar",
"not_null": false
},
{
"name": "app",
"data_type": "kInt",
"not_null": false
},
{
"name": "device",
"data_type": "kInt",
"not_null": false
},
{
"name": "os",
"data_type": "kInt",
"not_null": false
},
{
"name": "channel",
"data_type": "kInt",
"not_null": false
},
{
"name": "click_time",
"data_type": "kTimestamp",
"not_null": false
},
{
"name": "is_attributed",
"data_type": "kInt",
"not_null": false
}
],
"column_key":
[
{
"index_name": "INDEX_0_1711463208",
"col_name":
[
"ip"
],
"ttl":
{
"ttl_type": "kAbsoluteTime",
"abs_ttl": 0
}
},
{
"index_name": "idx_app",
"col_name":
[
"app"
],
"ttl":
{
"ttl_type": "kAbsoluteTime",
"abs_ttl": 0
}
}
],
"db": "mem_test",
"storage_mode": "kMemory"
}
```

2. data skew

https://www.kaggle.com/c/talkingdata-adtracking-fraud-detection/data?select=train.csv

The issue is about slow index creation on a skewed column in a large dataset. Start by examining the index creation logic in the OpenMLDB codebase, likely in the storage engine or SQL execution modules. Look for performance bottlenecks in sorting, partitioning, or data shuffling. Reproduce the issue using the provided dataset and steps, then profile the index creation to identify where time is spent. 'Done' means identifying the root cause and proposing a fix, such as optimizing the algorithm for skewed data.

Scritto dal modello di indicizzazione a partire dal testo della issue.