maria-developers team mailing list archive

["Berrocal, Eduardo" <eduardo.berrocal@xxxxxxxxx>]

Hi Sergei,

Thanks for the info. Very useful. Regarding your question:

    "A question: do you intend to push down and check conditions in arbitrary form?
    In case they are just equalities, it might make sense for storage engine to pretend having an index and then Batched Key    
    Access optimization will be used, and the SQL layer will provide batches of keys to lookup."


The answer is yes. I would like to check more than just equalities, if possible. The Batched key access is something worth looking into, in case that that can be used as a way to speed up at least conditions with equalities. 

A follow up question for you: When you say that traversing the linked join buffers is difficult... how difficult exactly? Like a linked list, a tree... ?  I still think it is worth exploring in our case given that the speedup can be huge if we can avoid a full scan of a table with TBs of data.



Eduardo Berrocal García de Carellán
Senior Software Engineer


Intel Corporation  |  intel.com


-----Original Message-----
From: Sergey Petrunia <sergey@xxxxxxxxxxx> 
Sent: Monday, July 12, 2021 2:49 AM
To: Berrocal, Eduardo <eduardo.berrocal@xxxxxxxxx>
Cc: Sergei Golubchik <serg@xxxxxxxxxxx>; maria-developers@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Maria-developers] Accessing read records during join for condition pushdown

Hi Eduardo,


On Thu, Jul 08, 2021 at 06:43:05PM +0000, Berrocal, Eduardo wrote:
> I have tried to use that route but without success. The problem is that I can only access the last record read from table1. 
> 
Yes, this is expected.

> Let say that, during the JOIN, 3 rows are read from table1. These 3 
> rows will be compared against all rows from table2. I want to avoid a 
> full scan of
> table2 by passing the value for the field from those 3 rows down to 
> the storage engine.

Looks reasonable.

> When condition pushdown is called on table2, and I try to access those 
> 3 rows, I can only access the last one read through
> f1->field->ptr (or f1->table->record[0]).
> 
> For what I understand, f1->table does not store an array of read 
> records during JOIN. Am I right? That is stored in the JOIN_CACHE 
> object. Please correct me if I am wrong here.

You're correct.

Other storage engines have only requested pushdown of basic conditions, so the code at SQL layer side doesn't handle the case where the Storage Engine would want to check the conditions that refer to the contents of the join buffer (aka JOIN_CACHE structure).

Note that join buffer can get complex for multi-table joins.
Consider a query:

select *
from
  t1, t2, t3
where
    t1.col1=t3.col1 and t2.col2=t3.col2

And a query plan (one gets something like this by default):

+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 10   |                                                        |
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using join buffer (flat, BNL join)                     |
|    1 | SIMPLE      | t3    | ALL  | NULL          | NULL | NULL    | NULL | 40   | Using where; Using join buffer (incremental, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+

Suppose t3 is your storage engine table. you would want to push down the whole

    t1.col1=t3.col1 and t2.col2=t3.col2

When the join buffer is "incremental" (like in the above EXPLAIN), it will actually be two join buffers linked through pointers. Traversing these might be complicated.

(whether join buffer is incremental is controlled by this setting:
https://mariadb.com/kb/en/server-system-variables/#join_cache_level)

A question: do you intend to push down and check conditions in arbitrary form? 
In case they are just equalities, it might make sense for storage engine to pretend having an index and then Batched Key Access optimization will be used, and the SQL layer will provide batches of keys to lookup. 

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