[MDEV-745] LP:770012 - Optimizer chooses a suboptimal plan for a join Created: 2011-04-24  Updated: 2015-11-09

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.2.14, 10.1
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Igor Babaev Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug770012.xml    

 Description   

I noticed the following suboptimal choice of the optimizer in mariadb-5.2 (the similar behavior of the optimizer can be found in any version of mariadb or mysql). Frequently the optimizer discards as inferior the plan that
uses a compound key with major coinstant components for a join.

To demonstrate the problem I use a standard DBT-3 innodb database of scale factor 10 with one additional index on supplier(s_acctbal).

The problem can be demonstrated with the query:
select max(l_discount) from supplier, lineitem
where s_acctbal between 2900 and 2910 and s_suppkey=l_suppkey and l_partkey=304540;

For this query the optimizer chooses the plan:

MariaDB [dbt3sf10]> explain select max(l_discount) from supplier, lineitem where s_acctbal between 2900 and 2910 and s_suppkey=l_suppkey and l_partkey=304540\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: lineitem
 
         type: ref
 
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
 
          key: i_l_suppkey_partkey
 
      key_len: 5
 
          ref: const
 
         rows: 58
 
        Extra: Using where
 
*************************** 2. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: supplier
 
         type: eq_ref
 
possible_keys: PRIMARY,i_s_acctbal
 
          key: PRIMARY
 
      key_len: 4
 
          ref: dbt3sf10.lineitem.l_suppkey
 
         rows: 1
 
        Extra: Using where
 
2 rows in set (0.00 sec)

An execution by this plan took mariadb-5.2 about 0.06 sec.

However the following alternative plan is better as it took only 0.02 sec

MariaDB [dbt3sf10]> explain select straight_join max(l_discount) from supplier, lineitem where s_acctbal between 2900 and 2910 and s_suppkey=l_suppkey and l_partkey=304540\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: supplier
 
         type: range
 
possible_keys: PRIMARY,i_s_acctbal
 
          key: i_s_acctbal
 
      key_len: 9
 
          ref: NULL
 
         rows: 101
 
        Extra: Using where; Using index
 
*************************** 2. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: lineitem
 
         type: ref
 
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
 
          key: i_l_suppkey_partkey
 
      key_len: 10
 
          ref: const,dbt3sf10.supplier.s_suppkey
 
         rows: 3
 
        Extra: Using where
 
2 rows in set (0.00 sec)

Most probably the optimizer does not take into account that when executing by the alternative plan the server accesses at most 3 pages of the table lineitem.

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-11-11 ]

Launchpad bug id: 770012

Comment by Elena Stepanova [ 2015-11-09 ]

Plans are still the same on 10.1.8, although execution time (at least on my machine) is not several times different, but maybe 40% better for the second query (it is by no means accurate value)

Generated at Thu Feb 08 06:31:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.