Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29572

Time reported as spent in optimizer exceeds total query time

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • N/A
    • 10.11
    • Optimizer
    • None

    Description

      ANALYZE FORMAT=JSON on the original dataset and query from MDEV-28073 (the branch is based on old 10.10 and doesn't contain the full fix MDEV-28852 yet, so the query spends a lot of time in optimization).

      preview-10.11-mdev-28926 81f857928 non-debug

      | {
        "query_optimization": {
          "r_total_time_ms": 1199082.822
        },
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 38.21517253,
      ...
      1 row in set (19 min 58.541 sec)
      

      So, even the query optimization time alone (1199082.822), not counting the query block time, already exceeds the total execution time reported by the client (19 min 58.541 sec = 1198541).
      The difference here is negligible, but the mere fact that it can occur raises a question about possible bigger discrepancies.

      Faster achievable with the same data/query under optimizer_search_depth=15:

      | {
        "query_optimization": {
          "r_total_time_ms": 23634.83577
        },
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.099659582,
      ...
      1 row in set (23.626 sec)
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.