Skip to content
New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

EXPLAIN FOR CONNECTION does not include "access object", resulting in partition pruning is not included. #55669

Closed
mjonss opened this issue Aug 26, 2024 · 1 comment · Fixed by #57415
Assignees
Labels
affects-8.5 This bug affects the 8.5.x(LTS) versions. severity/moderate sig/sql-infra SIG: SQL Infra type/bug The issue is confirmed as a bug.

Comments

@mjonss
Copy link
Contributor

mjonss commented Aug 26, 2024

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select connection_id(); -- Use this in the last step!
create table t (a int, b int, c json, primary key (a,b), key (b)) partition by range (b) (partition p0 values less than (1000000), partition pMax values less than (maxvalue));
explain analyze select a,b,c from t where b between 1 and 10;
explain for connection 1400897542; -- Use the actual connection id from above!

2. What did you expect to see? (Required)

Same output as for the direct EXPLAIN ANALYZE

3. What did you see instead (Required)

The "access object" column is empty for the first row "partition:p0"

tidb> select connection_id(); -- Use this in the last step!
+-----------------------------------------------+
| connection_id() -- Use this in the last step! |
+-----------------------------------------------+
|                                    1400897542 |
+-----------------------------------------------+
1 row in set (0.00 sec)

tidb> create table t (a int, b int, c json, primary key (a,b), key (b)) partition by range (b) (partition p0 values less than (1000000), partition pMax values less than (maxvalue));
Query OK, 0 rows affected (0.02 sec)

tidb> explain analyze select a,b,c from t where b between 1 and 10;
+-------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------+------+
| id                      | estRows  | actRows | task      | access object | execution info                                                                                                                                                                                                                                                  | operator info                     | memory    | disk |
+-------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------+------+
| TableReader_7           | 250.00   | 0       | root      | partition:p0  | time:241.7µs, loops:1, RU:0.480514, cop_task: {num: 1, max: 221µs, proc_keys: 0, tot_proc: 16.5µs, tot_wait: 51.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 2.71µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:215.5µs}} | data:Selection_6                  | 231 Bytes | N/A  |
| └─Selection_6       | 250.00   | 0       | cop[tikv] |               | tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 44.2µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 16.5µs, total_wait_time: 51.9µs, tikv_wall_time: 140.1µs}                                                    | ge(test.t.b, 1), le(test.t.b, 10) | N/A       | N/A  |
|   └─TableFullScan_5 | 10000.00 | 0       | cop[tikv] | table:t       | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                    | keep order:false, stats:pseudo    | N/A       | N/A  |
+-------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-----------+------+
3 rows in set (0.00 sec)

tidb> explain for connection 1400897542; -- Use the actual connection id from above!
+-------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+--------+------+
| id                      | estRows  | actRows | task      | access object | execution info                                                                                                                                                                                                                                                  | operator info                     | memory | disk |
+-------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+--------+------+
| TableReader_7           | 250.00   | 0       | root      |               | time:241.7µs, loops:1, RU:0.480514, cop_task: {num: 1, max: 221µs, proc_keys: 0, tot_proc: 16.5µs, tot_wait: 51.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 2.71µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:215.5µs}} | data:Selection_6                  | N/A    | N/A  |
| └─Selection_6       | 250.00   | 0       | cop[tikv] |               | tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 44.2µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 16.5µs, total_wait_time: 51.9µs, tikv_wall_time: 140.1µs}                                                    | ge(test.t.b, 1), le(test.t.b, 10) | N/A    | N/A  |
|   └─TableFullScan_5 | 10000.00 | 0       | cop[tikv] | table:t       | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                    | keep order:false, stats:pseudo    | N/A    | N/A  |
+-------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+--------+------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v8.4.0-alpha-33-gac9916a21e
Edition: Community
Git Commit Hash: ac9916a21e7ab38c563ce7c3d6a4b134e11f978f
Git Branch: HEAD
UTC Build Time: 2024-08-26 11:58:51
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
@mjonss mjonss added the type/bug The issue is confirmed as a bug. label Aug 26, 2024
@ti-chi-bot ti-chi-bot bot added the affects-8.5 This bug affects the 8.5.x(LTS) versions. label Nov 1, 2024
@Defined2014 Defined2014 self-assigned this Nov 15, 2024
@ti-chi-bot ti-chi-bot bot closed this as completed in a22b842 Nov 18, 2024
@ti-chi-bot ti-chi-bot bot added the affects-8.5 This bug affects the 8.5.x(LTS) versions. label Nov 18, 2024
# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
affects-8.5 This bug affects the 8.5.x(LTS) versions. severity/moderate sig/sql-infra SIG: SQL Infra type/bug The issue is confirmed as a bug.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants
@jebter @mjonss @Defined2014 and others