[#MDEV-31479] Inconsistency between MRR and SQL layer costs can cause poor query plan

[MDEV-31479] Inconsistency between MRR and SQL layer costs can cause poor query plan Created: 2023-06-14 Updated: 2023-07-31 Resolved: 2023-06-14
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.5, 10.6, 10.7, 10.8, 10.9, 10.11, 11.0, 11.1
Fix Version/s:	10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2

Type:

Bug

Priority:

Major

Reporter:

Sergei Petrunia

Assignee:

Sergei Petrunia

Resolution:

Fixed

Votes:

Labels:

None

Issue Links:

Blocks
Relates

Description

This is based on a customer case.

Based on DDL+optimizer trace, I've created a syntethic testcase. The SQL script is attached to the linked issue.

Failure scenario is as follows:

set optimizer_switch='mrr=on';

Can also optionally have mrr_cost_based=on.

create table t1 (

   col1,

   col2,

  INDEX idx1(col1),

  INDEX idx2(col1, col2)

);

and a query

select * from t1

where

  col1=const and

  col2 IN (...)

Then optimization runs like this

  "range_scan_alternatives": [

      "index": "idx2",

      "ranges": [

        -- single-point range here

],

      "rowid_ordered": false,

      "using_mrr": true, --- IMPORTANT

      "index_only": false,

      "rows": (good number)

      "cost":  (also good)

      "chosen": true

      "index": "idx1",

       -- Doesn't matter

      "rows": ..., --  Much worse than above

      "chosen": false,

      "cause": "cost"

Then in best_access_path we will have this:

    "access_type": "ref",

    "index": "idx1",

    "used_range_estimates": true,

...

    "rows": ...

    "cost": NEGATIVE_NUMBER

    "chosen": true

},

Because the cost is negative, the optimizer will use ref(idx1) instead of range(idx2) which was much cheaper.

Comments

Comment by Sergei Petrunia [ 2023-06-14 ]

That is, the problem has appeared in 10.5

Generated at Thu Feb 08 10:24:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.