Abnormal Execution Plan

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

Original topic: 执行计划不正常

| username: Hacker_5KEgzcj2

[Overview] SQL execution plan is abnormal, causing the statement to execute too slowly

[Background] The health score of other worksheet tables is 99

[TiDB Version] V5.4.1

Generally, it should first filter according to create_time, resulting in a result set of 500 rows. Then sort, but this execution plan sorts the entire set first and then filters, causing the SQL to execute too slowly.

Related indexes:

| username: h5n1 | Original post link

count(1) is for testing purposes, not for actual business queries, right? What is the first column? Try removing the order by clause.

| username: xfworld | Original post link

Since it’s already counted, why do we still need to add order by?

| username: caiyfc | Original post link

This execution plan first performs a full table scan, then filters based on the filter conditions, and finally sorts the results. This is the expected order. However, this execution plan does not use an index, and considering you mentioned the result set is 500, the expected number of result set entries seen from the execution plan is 3.94, which is quite a discrepancy. This could be due to inaccurate statistics.

| username: forever | Original post link

Your SQL is mainly slow due to a full table scan. A full table scan is similar to Oracle predicate crossing. You can try collecting statistics again and test it. What are you mainly testing? If the type is already equal, why do you still need to order by? :joy:

| username: Hacker_5KEgzcj2 | Original post link

Original sentence:
select * from worksheet where type = 1 and create_time >= ‘2022-10-13 11:52:25’ order by create_time desc, type limit 10

| username: Hacker_5KEgzcj2 | Original post link

Analyze and recollect statistics, the issue is resolved, but the table’s health score is 99.

| username: Hacker_5KEgzcj2 | Original post link

That’s right, but the health score is 99, which is hard to prevent.

| username: forever | Original post link

This is similar to Oracle’s predicate pushdown, where queries for the latest time may result in an inaccurate execution plan.

| username: caiyfc | Original post link

In the case of an inaccurate execution plan, you can consider binding the execution plan. Refer to this: SQL Plan Management (SPM) | PingCAP Docs

| username: system | Original post link

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