[MDEV-6407] Incredibly slow performance on SELECT query (and its corresponding EXPLAIN), works fine on MySQL Created: 2014-06-30  Updated: 2022-11-08

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.38, 10.0.12, 10.1.0
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Steven Don Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: optimizer
Environment:

opensuse 13.1, kernel 3.11.10 on x86, SSD drive, 2GB RAM

Attachments: File bug.sql    
Issue Links:
Duplicate
is duplicated by MDEV-6724 Slow query optimizer with certain sub... Open
Sprint: 10.1.6-2, 10.0.21

 Description   

With a very, very small dataset (less than 1KB of actual data), a SELECT query takes several minutes. The EXPLAIN query takes just as long, leading me to believe it is an optimiser bug. On MySQL 5.5.33 it [the EXPLAIN query] takes 0.00s. On MariaDB 5.5.33 it takes an eternity (also tried on latest stable 5.x and 10.x versions, 5.5.38 and 10.0.12 at the time of writing). This is with the highly reduced testcase I'm including. With my actual dataset (still quite small less than 1MB in total table sizes according to phpMyAdmin), I killed the EXPLAIN query after 6 HOURS!

Admittedly, the query has a redundant join in it, which I removed and it fixed the problem I was experiencing, but I think this is a bug anyway.

 Comments   
Comment by Elena Stepanova [ 2014-06-30 ]

Hi Steven,

Thanks for the report.

EXPLAIN on current MariaDB 5.5:

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                                            |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary                                     |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                      |
 
|    1 | PRIMARY     | b     | index  | PRIMARY       | PRIMARY | 4       | NULL        |    2 |   100.00 | Using index; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (incremental, BNL join)           |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using index                                                      |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                      |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                      |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (incremental, BNL join)           |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (incremental, BNL join)           |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                      |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                      |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 |                                                                  |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.b_id |    1 |   100.00 | Using index                                                      |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; End temporary; Using join buffer (flat, BNL join)   |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+
 
25 rows in set, 1 warning (1 min 29.09 sec)
 

| Note  | 1003 | select `test`.`a`.`id` AS `id` from `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) join `test`.`a` where ((`test`.`c`.`val` = 'c1') and (`test`.`d`.`val` = 'd1') and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`c`.`val` = 'c2') and (`test`.`d`.`val` = 'd2') and (`test`.`c`.`val` = 'c3') and (`test`.`d`.`val` = 'd3') and (`test`.`c`.`val` = 'c4') and (`test`.`d`.`val` = 'd4') and (`test`.`c`.`val` = 'c5') and (`test`.`d`.`val` = 'd5') and (`test`.`c`.`val` = 'c6') and (`test`.`d`.`val` = 'd6') and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`c`.`b_id` = `test`.`b`.`id`) and (`test`.`a`.`b_id` = `test`.`b`.`id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`c`.`b_id` = `test`.`a`.`b_id`) and (`test`.`b`.`id` = `test`.`a`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`)) |

EXPLAIN on MySQL 5.6:

+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+
 
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                                             |
 
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+
 
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary                                      |
 
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
 
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
 
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using index                                                       |
 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
 
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
 
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; End temporary; Using join buffer (Block Nested Loop) |
 
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+
 
25 rows in set, 1 warning (0.06 sec)
 

| Note  | 1003 | /* select#1 */ select `test`.`a`.`id` AS `id` from `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) join `test`.`a` where ((`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`d`.`val` = 'd6') and (`test`.`c`.`val` = 'c6') and (`test`.`d`.`val` = 'd5') and (`test`.`c`.`val` = 'c5') and (`test`.`d`.`val` = 'd4') and (`test`.`c`.`val` = 'c4') and (`test`.`d`.`val` = 'd3') and (`test`.`c`.`val` = 'c3') and (`test`.`d`.`val` = 'd2') and (`test`.`c`.`val` = 'c2') and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`val` = 'd1') and (`test`.`c`.`val` = 'c1')) |

ANALYZE on current 10.1:

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | r_rows | filtered | r_filtered | Extra                                                            |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |      1 |   100.00 |       0.00 | Using where; Start temporary                                     |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
 
|    1 | PRIMARY     | b     | index  | PRIMARY       | PRIMARY | 4       | NULL        |    2 |   NULL |   100.00 |       NULL | Using index; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (incremental, BNL join)           |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Using join buffer (incremental, BNL join)           |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (incremental, BNL join)           |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where                                                      |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where                                                      |
 
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL |                                                                  |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (flat, BNL join)                  |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index; End temporary                                       |
 
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
 
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; End temporary; Using join buffer (flat, BNL join)   |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+
 
25 rows in set (1 min 32.09 sec)

Comment by Steven Don [ 2014-06-30 ]

Runtime seems to be exponentially increasing dependent on the number of subqueries (even though they are independent) and 2 records in table B seems to be the sweet (or sour) spot.

The original dataset had covering indexes that could be used for all the joins (and both MySQL and MariaDB used them in the cases where I waited long enough to get output), but that didn't make any difference to the performance of the EXPLAIN query or even to the SELECT, given the small amount of data, so I left them out of the testcase.

SHOW PROCESSLIST just tells me it's in the "statistics" phase, burning 100% CPU.

Comment by Sergei Petrunia [ 2014-07-14 ]

if I set

optimizer_search_depth=1

then EXPLAIN finishes in 0.01 sec. The generated plan is different (not sure if it's better or worse). It seems, the time is spent in the join optimizer.

Comment by Sergei Petrunia [ 2014-07-14 ]

Checked how many possible join orders are considered by the optimizer. The
number is huge:

105,099,658 /tmp/trace1

(If you count tables inside semi-joins, the join has 25 tables)

If I change all of the subqueries to be joins, then EXPLAIN finishes in 0.01 sec.

1167 /tmp/trace1

The number of considered join combinations is much smaller.

It seems, something is wrong with join plan pruning when semi-joins are present.

Comment by Sergei Petrunia [ 2014-07-18 ]

the default value for @@optimizer_search_depth is 62. That is, the optimizer performs exhaustive plan search, the branches are cut off via pruning.

If I set optimizer_search_depth=0 (which means "set depth automatically), I get:

time to run EXPLAIN (debug build, lenovo edge e130):
join - 0.02 sec
subqueries -0.10 sec.

determine_search_depth()=7 for both cases.

Comment by Sergei Golubchik [ 2014-07-22 ]

This won't be fixed in 5.5, because a fix can change many existing execution plans and we cannot do that in 5.5 anymore. But there is a simple workaround for this bug (set optimizer_search_depth to a smaller value).

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