maria-developers team mailing list archive
Mailing list archive
Cost analysis: comparison of the two approaches
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.
psergey's opinion: too much unaccounted-for WHERE clauses. to be elaborated further...
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org