Question about TiDB execution plan optimization issues

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 关于tidb执行计划不优问题请问

| username: wenyi

The following SQL statement, r.plan_exec_time has an index, but it does not use the index and performs a full table scan, resulting in low SQL performance. Why is this happening?

| username: wzf0072 | Original post link

Have you enabled TiFlash? Enable column storage for these tables.

| username: wenyi | Original post link

Why does the table his.t_inp_order_exec_record perform a full table scan instead of using the index on the r.plan_exec_time column, which could filter a lot of data?

| username: wenyi | Original post link

Why is it like this without enabling TiFlash? I want to understand.

| username: wzf0072 | Original post link

After filtering, there are still 4 million rows of data? Try collecting statistics with ANALYZE TABLE.

| username: wzf0072 | Original post link

The optimizer performed a full table scan for 40% of the data.

| username: wenyi | Original post link

Statistics have already been collected.
image

| username: wzf0072 | Original post link

After collecting, execute EXPLAIN ANALYZE to see the actual execution situation.

| username: wenyi | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: Kongdom | Original post link

I remember there was a bug with subqueries before.

| username: buddyyuan | Original post link

The time range you selected is too large. Retrieving too much data may result in higher costs for the optimizer if it uses an index to first fetch the rowid and then scans the region by going back to the table.

| username: wzf0072 | Original post link

If this type of query is very frequent, you might consider adding a covering index.

| username: zhanggame1 | Original post link

If the filtered data accounts for too much of the total table data, the index won’t be used. Try adding a hint.

| username: DBRE | Original post link

Execute show stats_healthy where table_name = 'xxxx' to check the health of the table. If it is relatively low, first run analyze table, and then check if there are any changes in the execution plan.

| username: Hacker007 | Original post link

New tables or newly created indexes might not be analyzed automatically. You should manually execute ANALYZE TABLE.

| username: 胡杨树旁 | Original post link

Try executing the subquery separately to see if the r table can use the index. The selectivity looks pretty good, filtering out about half of the data. If the subquery does not use the index when executed separately, add a hint to force the use of the index and check again.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.