maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

So, we're comparing two plans that were mentioned earlier.

FirstMatch plan has:

  (gdb) p *current_read_time
      $426 = 360931
  (gdb) p *current_record_count
      $427 = 4500

which is much more greater than the SJ-Materialization plan, which has:

  (gdb) print current_record_count
    $634 = 4500
  (gdb) print current_read_time
    $635 = 8663.1088587744125

Let's check whether the difference can be overcame by adding the cost of the scalar-subquery predicate.

Cost of one execution of scalar subquery predicate  is 4.5 ~= 5.
SJ-Materialization plan will execute the subquery predicate 2378 times:

  (gdb) p join->best_read
    $575 = 6229.5578587744112
  (gdb) p join->record_count
    $576 = 2387

which gives added cost of 

  2378 * 5 = 11890.   
   
   8663 + 11890 is still much less than 360931.

Hence, just taking scalar-subquery cost into account is not sufficient for query plan to be changed.

ps.
psergey's opinion: too much unaccounted-for WHERE clauses. to be elaborated further...

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog