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

More information regarding access of a table to be printed inside the optimizer_trace

Details

    Description

      Example:

                              "table": "lineitem",
      		 
                              "best_access_path": {
      		 
                                "considered_access_paths": [
      		 
                                  {
      		 
                                    "access_type": "ref",
      		 
                                    "index": "PRIMARY",
      		 
                                    "rows": 1,
      		 
                                    "cost": 250.69,
      		 
                                    "chosen": true
      		 
                                  },
      		 
                                  {
      		 
                                    "access_type": "ref",
      		 
                                    "index": "i_l_orderkey",
      		 
                                    "rows": 1,
      		 
                                    "cost": 498.4,
      		 
                                    "chosen": false,
      		 
                                    "cause": "cost"
      		 
                                  },
      		 
                                  {
      		 
                                    "access_type": "ref",
      		 
                                    "index": "i_l_orderkey_quantity",
      		 
                                    "rows": 1,
      		 
                                    "cost": 498.4,
      		 
                                    "chosen": false,
      		 
                                    "cause": "cost"
      		 
                                  },
      		 
                                  {
      		 
                                    "type": "scan",
      		 
                                    "chosen": false,
      		 
                                    "cause": "cost"
      		 
                                  }
      		 
                                ]

      So here we see all the ref accesses and table_scan considered on table lineitem, just add the best access picked to the optimizer trace to make it clear

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-6111 optimizer trace

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20443 More information regarding access of a table to be printed inside the optimizer_trace

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            varun Varun Gupta (Inactive) created issue -
            varun Varun Gupta (Inactive) made changes -
            Field Original Value New Value
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            Labels optimizer_trace
            varun Varun Gupta (Inactive) made changes -
            Description Example:
            {noformat}
                                    "table": "lineitem",
                                    "best_access_path": {
                                      "considered_access_paths": [
                                        {
                                          "access_type": "ref",
                                          "index": "PRIMARY",
                                          "rows": 1,
                                          "cost": 250.69,
                                          "chosen": true
                                        },
                                        {
                                          "access_type": "ref",
                                          "index": "i_l_orderkey",
                                          "rows": 1,
                                          "cost": 498.4,
                                          "chosen": false,
                                          "cause": "cost"
                                        },
                                        {
                                          "access_type": "ref",
                                          "index": "i_l_orderkey_quantity",
                                          "rows": 1,
                                          "cost": 498.4,
                                          "chosen": false,
                                          "cause": "cost"
                                        },
                                        {
                                          "type": "scan",
                                          "chosen": false,
                                          "cause": "cost"
                                        }
                                      ]
            {noformat}
            So here we see all the ref accesses and table_scan considered on table lineitem, just add the best access picked to the optimizer trace to make it clear
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) added a comment - - edited

            Also there has been request to add

            • selectivity of conditions when joining a table(computed by function table_cond_selectivity()) [already done in MDEV-20440]
            • best join order picked
            • count of records in the prefix of a partial join(this is already there we already have record count and the selectivity, so this would just record_count*selectivity)
            • total join cardinality
            varun Varun Gupta (Inactive) added a comment - - edited Also there has been request to add selectivity of conditions when joining a table(computed by function table_cond_selectivity()) [already done in MDEV-20440] best join order picked count of records in the prefix of a partial join(this is already there we already have record count and the selectivity, so this would just record_count*selectivity) total join cardinality
            varun Varun Gupta (Inactive) made changes -
            psergei Sergei Petrunia added a comment -

            Some of that information is alreadyt printed, see https://github.com/MariaDB/server/commit/2dbe472ed011a951b28434ae8e67945e964d2030

            psergei Sergei Petrunia added a comment - Some of that information is alreadyt printed, see https://github.com/MariaDB/server/commit/2dbe472ed011a951b28434ae8e67945e964d2030
            varun Varun Gupta (Inactive) added a comment -

            Patch
            http://lists.askmonty.org/pipermail/commits/2019-September/013963.html

            varun Varun Gupta (Inactive) added a comment - Patch http://lists.askmonty.org/pipermail/commits/2019-September/013963.html
            psergei Sergei Petrunia added a comment -

            Ok to push

            psergei Sergei Petrunia added a comment - Ok to push
            varun Varun Gupta (Inactive) made changes -
            Fix Version/s 10.4.8 [ 23721 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 99292 ] MariaDB v4 [ 134073 ]

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              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.