Bug Report: Union All Push Down Query to shard in v21.0.3 chooses a bad plan with temporay table usage

[keyvanl]

Overview of the Issue

Union All Push Down Query to shard in v21.0.3 chooses a bad plan with temporary table usage, whereas in v19.0.8 no temporary table usage is observed.

Reproduction Steps

README_temp_query_regression.md

Binary Version

Server version: 8.0.29-21 Version: 21.0.3 (Git revision 94fdc736eae8928a8fdde44e9ec9c3bee1868d6f branch 'HEAD') built on Wed Feb 12 16:56:27 UTC 2025 by runner@fv-az1705-503 using go1.23.5 linux/amd64

Operating System and Environment details

cat /etc/os-release
NAME="Red Hat Enterprise Linux"
VERSION="8.10 (Ootpa)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="8.10"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Red Hat Enterprise Linux 8.10 (Ootpa)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:8::baseos"
HOME_URL="https://www.redhat.com/"
DOCUMENTATION_URL="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8"
BUG_REPORT_URL="https://issues.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.10
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.10"

uname -sr
Linux 4.18.0-553.34.1.el8_10.x86_64

uname -m
x86_64

Log Fragments

[systay]

Hi @keyvanl 👋

I'm currently looking into this issue. It's not likely to be a quick fix and might not make it into a backport. Meanwhile, I wanted to share another vexplain option that you might find helpful in the future.

The vexplain all option not only shows you the vtgate plan but also provides the JSON execution plans from the underlying MySQL instances.

For instance, in the example below, you can see the two queries we're issuing. Under mysql_explain_json, the detailed MySQL execution plans are included.

I hope this isn't too much of a distraction! I built this feature a couple of years ago and noticed it's not frequently used, which is unfortunate because it can be quite handy.

Here's a quick example:

mysql> vexplain all select * from corder join commerce.product as prod on corder.sku = prod.sku;
{
  "OperatorType": "Join",
  "Variant": "Join",
  "JoinColumnIndexes": "L:1,L:2,L:3,L:4,R:0,R:1,R:2",
  "JoinVars": {
    "corder_sku": 0
  },
  "TableName": "corder_product",
  "Inputs": [
    {
      "OperatorType": "Route",
      "Variant": "Scatter",
      "Keyspace": {
        "Name": "customer",
        "Sharded": true
      },
      "FieldQuery": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder where 1 != 1",
      "Query": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder",
      "Table": "corder",
      "mysql_explain_json": {
        "query_block": {
          "select_id": 1,
          "cost_info": {
            "query_cost": "0.65"
          },
          "table": {
            "table_name": "corder",
            "access_type": "ALL",
            "rows_examined_per_scan": 4,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.40",
              "prefix_cost": "0.65",
              "data_read_per_join": "640"
            },
            "used_columns": [
              "order_id",
              "customer_id",
              "sku",
              "price"
            ]
          }
        }
      }
    },
    {
      "OperatorType": "Route",
      "Variant": "Unsharded",
      "Keyspace": {
        "Name": "commerce",
        "Sharded": false
      },
      "FieldQuery": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where 1 != 1",
      "Query": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where prod.sku = :corder_sku",
      "Table": "product",
      "mysql_explain_json": {
        "query_block": {
          "select_id": 1,
          "cost_info": {
            "query_cost": "1.00"
          },
          "table": {
            "table_name": "prod",
            "access_type": "const",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "sku"
            ],
            "key_length": "130",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "272"
            },
            "used_columns": [
              "sku",
              "description",
              "price"
            ]
          }
        }
      }
    }
  ]
}

[keyvanl]

Thanks @systay re the 'vexplain all' tip - very useful ! Any update on this one? we're concerned as if we spill to temp this may cause our queries to fall outside of accepted SLO.