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

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            psergei Sergei Petrunia made changes -
            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 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.

            h2. Very important
            * ORDER BY/GROUP BY/ DISTINCT (MDEV-6995)

            h2. 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"
            psergei Sergei Petrunia made changes -
            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.

            h2. Very important
            * ORDER BY/GROUP BY/ DISTINCT (MDEV-6995)

            h2. 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"
            		 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.

            h2. 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.

            h2. 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"
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            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.

            h2. 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.

            h2. 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"
            		 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.

            h2. 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.

            h2. 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"
            * UNION/UNION ALL should display whether a temp table is used.
            psergei Sergei Petrunia made changes -
            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.

            h2. 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.

            h2. 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"
            * UNION/UNION ALL should display whether a temp table is used.             		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.

            h2. 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.

            h2. 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.
            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)"
            psergei Sergei Petrunia made changes -
            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.

            h2. 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.

            h2. 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.
            		 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.

            h2. 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.

            h2. 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.

            h2. Minor
            * Both us and MySQL print "ref": ["func"] . This made sense for tabular output but makes much less sense now.
            psergei Sergei Petrunia made changes -
            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.

            h2. 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.

            h2. 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.

            h2. Minor
            * Both us and MySQL print "ref": ["func"] . This made sense for tabular output but makes much less sense now.
            		 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.

            h2. 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.

            h2. 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.

            h2. 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?
            psergei Sergei Petrunia made changes -
            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.

            h2. 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.

            h2. 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.

            h2. 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?
            		 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.

            h2. 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.

            h2. 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.

            h2. 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:
            {noformat}"(not(<in_optimizer>(t1.a,t1.a in (subquery#2))))"{noformat}
              should this be removed?
            psergei Sergei Petrunia made changes -
            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.

            h2. 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.

            h2. 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.

            h2. 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:
            {noformat}"(not(<in_optimizer>(t1.a,t1.a in (subquery#2))))"{noformat}
              should this be removed?
            		 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.

            h2. 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.

            h2. 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.
            * are "expensive const" conditions printed anywhere? (not sure if MySQL has them.. they should?)

            h2. 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:
            {noformat}"(not(<in_optimizer>(t1.a,t1.a in (subquery#2))))"{noformat}
              should this be removed?
            psergei Sergei Petrunia made changes -
            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.

            h2. 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.

            h2. 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.
            * are "expensive const" conditions printed anywhere? (not sure if MySQL has them.. they should?)

            h2. 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:
            {noformat}"(not(<in_optimizer>(t1.a,t1.a in (subquery#2))))"{noformat}
              should this be removed?
            		 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.

            h2. 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.

            h2. 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.

            h2. 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:
            {noformat}"(not(<in_optimizer>(t1.a,t1.a in (subquery#2))))"{noformat}
              should this be removed?


            h2. Done
            * -are "expensive const" conditions printed anywhere-
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 58812 ] MariaDB v3 [ 64769 ]
            elenst Elena Stepanova made changes -
            Affects Version/s 10.1 [ 16100 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 64769 ] MariaDB v4 [ 130290 ]

            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.