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

EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache

    XMLWordPrintable

Details

    • 10.1.6-1

    Description

      EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON should show subquery cache use. ANALYZE must include #hits and hit ratio.
      An example query:

      MariaDB [dbt3sf1]> analyze FORMAT=JSON select count(*) from customer where (select sum(o_totalprice) from orders where o_custkey=c_custkey) > 1000*1000\G
      *************************** 1. row ***************************
      EXPLAIN: {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_time_ms": 2497.7,
          "table": {
            "table_name": "customer",
            "access_type": "index",
            "key": "i_c_nationkey",
            "key_length": "5",
            "used_key_parts": ["c_nationkey"],
            "r_loops": 1,
            "rows": 150747,
            "r_rows": 150000,
            "r_time_ms": 34.957,
            "filtered": 100,
            "r_filtered": 60.691,
            "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
            "using_index": true
          },
          "subqueries": [
            {
              "query_block": {
                "select_id": 2,
                "r_loops": 150000,
                "r_time_ms": 2395.5,
                "table": {
                  "table_name": "orders",
                  "access_type": "ref",
                  "possible_keys": ["i_o_custkey"],
                  "key": "i_o_custkey",
                  "key_length": "5",
                  "used_key_parts": ["o_custkey"],
                  "ref": ["dbt3sf1.customer.c_custkey"],
                  "r_loops": 150000,
                  "rows": 7,
                  "r_rows": 10,
                  "r_time_ms": 2120.7,
                  "filtered": 100,
                  "r_filtered": 100
                }
              }
            }
          ]
        }
      }

      EXPLAIN EXTENDED+subquery cache shows the query uses subquery cache.

      • EXPLAIN FORMAT=JSON should show it: subquery's query_block should be wrapped inside subquery_cache element.
      • ANALYZE FORMAT=JSON should also show r_hits (how many times cache was invoked) and r_hit_ratio (0...100% - fraction of times there was a cache hit)

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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