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

EXPLAIN FORMAT=JSON: Better output

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      MDEV-6109 has added support for EXPLAIN=JSON. The basic structure is there, but the output can be improved for a number of cases. This is a task to list/work on them.

      Very important

      • JSON-ish output for ORDER BY/GROUP BY/ DISTINCT (MDEV-6995)
      • Walk through what we currently produce and see if it all makes sense.

      Not very important

      • Subquery cache feature (ANALYZE should print cache's statistics)
      • Better/systematic display of subquery types (Materialization with/without keys, single-row subquery, etc)
      • Check how hash join is displayed. Could we do better?
      • Better display for MRR scans (display them as special kinds of scans)
        • ANALYZE should display scan restarts/buffer refills for "Key/Rowid-ordered scan"
        • non-bka case also should display buffer refills.
      • UNION/UNION ALL should display whether a temp table is used.

      Minor

      • Both us and MySQL print "ref": ["func"] . This made sense for tabular output but makes much less sense now.
      • MariaDB doesn't print subqueries in the WHERE clause. However, it still prints item_in_optimizer: 

        "(not(<in_optimizer>(t1.a,t1.a in (subquery#2))))"

        should this be removed?

      Done

      • are "expensive const" conditions printed anywhere

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-6995 EXPLAIN JSON and ORDER BY, GROUP BY, etc

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-6109 EXPLAIN JSON

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            psergei Sergei Petrunia added a comment -

            To be investigated: MySQL has improved printout of triggered conditions. At least, they did it for the "Full scan on NULL key" case:

            Currently, MariaDB prints:

                 "attached_condition": "trigcond(((<cache>(outer_t1.a) = t1.a) or isnull(t1.a)))",

            while in 5.6 I see:

                 "attached_condition": "<if>(outer_field_is_not_null, ((<cache>(`j2`.`outer_t1`.`a`) = `j2`.`t1`.`a`) or isnull(`j2`.`t1`.`a`)), true)"

            psergei Sergei Petrunia added a comment - To be investigated: MySQL has improved printout of triggered conditions. At least, they did it for the "Full scan on NULL key" case: Currently, MariaDB prints: "attached_condition": "trigcond(((<cache>(outer_t1.a) = t1.a) or isnull(t1.a)))", while in 5.6 I see: "attached_condition": "<if>(outer_field_is_not_null, ((<cache>(`j2`.`outer_t1`.`a`) = `j2`.`t1`.`a`) or isnull(`j2`.`t1`.`a`)), true)"

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              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.