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

EXPLAIN FORMAT=JSON: Better output

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1
        • Fix Version/s: None
        • Component/s: Optimizer
        • Labels:
          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

            Gliffy Diagrams

              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

                  Hide
                  Permalink
                  psergey 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)"
                  Show
                  psergey 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

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      psergey Sergei Petrunia
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated: