Cannot find the constants for the Optimizer Cost Model in v11.5

You are viewing an old version of this question. View the current version here.

We are using MariaDB v11.5, and according to the New Cost Model in MariaDB official Knowledge Base, there should be a table `information_schema.optimizer_costs` containing the constants used for running time estimation. And the online document provides the below sample query:

 select * from information_schema.optimizer_costs where engine="default"\G
*************************** 1. row ***************************
                         ENGINE: default
       OPTIMIZER_DISK_READ_COST: 10.240000
OPTIMIZER_INDEX_BLOCK_COPY_COST: 0.035600
     OPTIMIZER_KEY_COMPARE_COST: 0.011361
...

In our testing server, the Optimizer Trace is working on this database instance, and during test, we have retrieved the cost estimation as shown in the screenshot below. However, we are unable to find the table `information_schema.optimizer_costs`.

Question:

So we wonder where to look for the table.

Screenshot of Optimizer Trace:

MariaDB [database]> SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+---------------------
| TRACE               
+---------------------
| {
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select count(0) AS `count(*)` from eav_value"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "eav_value",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "eav_value",
                "table_scan": {
                  "rows": 2207198,
                  "cost": 9003
                }
              }
            ]
          },
...

Answer

It seems you aren't running the MariaDB-11.0 version. Check with:

SELECT VERSION()

v11.5 looks like a client version as that server version hasn't even been developed yet let alone released.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.