Skip to content

cost_trace explain has unnecessary item #61127

Closed

Description

@hawkingrei

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

You can use TPCH Q4

explain format='cost_trace' select
        o_orderpriority,
        count(*) as order_count
from
        orders
where
        o_orderdate >= '1995-01-01'
        and o_orderdate < date_add('1995-01-01', interval '3' month)
        and exists (
                select    *
                from
                        lineitem
                where
                        l_orderkey = o_orderkey
                        and l_commitdate < l_receiptdate  )
group by  o_orderpriority
order by       o_orderpriority

You can check the costFormula result

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

(((((((cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(291)*tikv_scan_factor(40.7)))) + (net(0.8*rowsize(24)*tidb_kv_net_factor(3.96))))/15.00)*2852410.25)/6.00) + (cpu(2.2819282e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(2.2819282e+06*0*tidb_cpu_factor(49.9))) + (hashmem(2.2819282e+06*24*tidb_mem_factor(0.2))) + (hashbuild(2.2819282e+06*tidb_cpu_factor(49.9)))))

3. What did you see instead (Required)

***************************[ 4. row ]***************************
id            |     └─IndexJoin_29
estRows       | 2281928.20
estCost       | 1482782425.72
costFormula   | (cpu(10*3*tidb_cpu_factor(49.9))) + (((((scan(2.85241025e+06*logrowsize(46.17)*tiflash_scan_factor(11.6))) + (scan(10000*logrowsize(46.17)*tiflash_scan_factor(11.6))))) + (net(2.85241025e+06*rowsize(46.17)*tidb_flash_net_factor(2.2))))/15.00) + (cpu(2.85241025e+06*filters(0)*tidb_cpu_factor(49.9))) + (cpu(2.85241025e+06*10*tidb_cpu_factor(49.9))) + ((() + ((((((cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(291)*tikv_scan_factor(40.7)))) + (net(0.8*rowsize(24)*tidb_kv_net_factor(3.96))))/15.00)*2852410.25)/6.00) + (cpu(2.2819282e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(2.2819282e+06*0*tidb_cpu_factor(49.9))) + (hashmem(2.2819282e+06*24*tidb_mem_factor(0.2))) + (hashbuild(2.2819282e+06*tidb_cpu_factor(49.9)))))/5.00)
task          | root
access object |
operator info | semi join, inner:TableReader_25, left side:TableReader_61, outer key:test.orders.o_orderkey, inner key:test.lineitem.l_orderkey, equal cond:eq(test.orders.o_orderkey, test.lineitem.l_orderkey)
(() + ((((((cpu(1*filters(1)*tikv_cpu_factor(49.9))) + (scan(1*logrowsize(291)*tikv_scan_factor(40.7)))) + (net(0.8*rowsize(24)*tidb_kv_net_factor(3.96))))/15.00)*2852410.25)/6.00) + (cpu(2.2819282e+06*filters(0)*tidb_cpu_factor(49.9))) + ((hashkey(2.2819282e+06*0*tidb_cpu_factor(49.9))) + (hashmem(2.2819282e+06*24*tidb_mem_factor(0.2))) + (hashbuild(2.2819282e+06*tidb_cpu_factor(49.9)))))

4. What is your TiDB version? (Required)

Activity

  1. self-assigned this
    on May 15, 2025
  2. added a commit that references this issue on May 15, 2025
    027a098
  3. added
    affects-8.1This bug affects the 8.1.x(LTS) versions.
    affects-7.5This bug affects the 7.5.x(LTS) versions.
    on Jun 19, 2025
  4. added a commit that references this issue on Jun 21, 2025
    bfab5eb
  5. added a commit that references this issue on Jun 30, 2025
    551012f
  6. added a commit that references this issue on Aug 4, 2025
    c0ed0bc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

affects-7.5This bug affects the 7.5.x(LTS) versions.affects-8.1This bug affects the 8.1.x(LTS) versions.affects-8.5This bug affects the 8.5.x(LTS) versions.severity/minorsig/plannerSIG: Plannertype/bugThe issue is confirmed as a bug.

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions