maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

On Mon, Jun 04, 2018 at 12:34:49PM +0530, Varun wrote:
> revision-id: 56d339111a4e5c868ffa3b1f2538c5f101f67767 (mariadb-10.0.30-366-g56d339111a4)
> parent(s): a61724a3ca187f6eb44fc67948acb76a94efa783
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2018-06-04 12:26:56 +0530
> message:
> 
> MDEV-16374: Filtered shows 0 for materilization scan for a semi join, which makes optimizer always picks
>             materialization scan over materialization lookup
> 
> For non-mergeable semi-joins we don't store the estimates of the IN subquery in table->file->stats.records.
> In the function TABLE_LIST::fetch_number_of_rows, we store the number of rows in the tables
> (estimates in case of derived table/views).
> Currently we don't store the estimates for non-mergeable semi-joins, which leads to a problem of selecting
> materialization scan over materialization lookup.
> Fixed this by storing these estimated appropriately
> 

* This is a change in the optimizer. Should we really do it in 10.0? In my
opinion, this should go into 10.3 or 10.4.  Let's discuss this, and I think
Igor may have something to say, too.

* The patch updates selectivity.test but not selectivity_innodb.result (which
includes selectivity.test).


* Please see my last comments at https://jira.mariadb.org/browse/MDEV-16374 .
Here is an alternative patch (it passes the testsuite, and produces the same
result for selectivity* tes):

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1285835..884997c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6602,7 +6602,7 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint,
   {
     TABLE *table= s->table;
     double sel= table->cond_selectivity;
-    double table_records= (double)table->stat_records();
+    double table_records= s->records; // psergey-10
     dbl_records= table_records * sel;
     return dbl_records;
   }

What do you think about it?
I would like to get Igor's opinion also because I'm not certain which member
variable should be changed here.

(Why does derived table modify table->stats.records while JTBM semi-joins
modify JOIN_TAB::records? These seem like two solutions for the same problem?)

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog