maria-developers team mailing list archive

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

Sergei,

I created a pull request to add the iteration methods in JOIN_CACHE_BNL public.

Here it is: https://github.com/MariaDB/server/pull/1878

Thanks,


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


Intel Corporation  |  intel.com


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

Hi Eduardo,

On Thu, Jul 22, 2021 at 12:28:09AM +0000, Berrocal, Eduardo wrote:
> 
> Thanks a lot for your response. The way you showed seems to be the way to go.
> There is just one problem: all those functions are protected inside 
> the JOIN_CACHE (and JOIN_CACHE_*) Classes. Which seems to suggest that code changes to the MariaDB core code is a necessity in this case.
> 

Hi Eduardo,

(Adding Igor Babaev, the author of this code, to CC:)

== Short ==
* I think, making the iteration methods public is not a big deal. This would
  allow to iterate JOIN_CACHE_BNL.

* It seems, one can't iterate JOIN_CACHE_BNLH as easily.

== Long ==

=== Making iteration methods public ===

In my opinion, we can accept a patch that makes JOIN_CACHE iteration methods public.
This will allow one to iterate over records in JOIN_CACHE_BNL.

(One can check the join cache type by calling JOIN_CACHE::get_join_alg() which is public, and check for linked join cache(s) by traversing JOIN_CACHE::prev_cache which is also public)

=== On iterating JOIN_CACHE_BNLH ===

JOIN_CACHE_BNLH is the "hashed" variant of the join buffer. The join buffer is not an array but rather a hash table with the key being the value of some
column(s) in the join buffer.

When one calls JOIN_CACHE_BNLH::prepare_look_for_matches(), that function calls get_matching_chain_by_join_key(), which has an "implicit" parameter - the value of the join column.

Then, *next_candidate_for_match() functions iterate only over the rows with the matching hash value.


This means the interface won't work if one just wants to examine the contents of the join buffer.

Possible ways out:
A. Ingore this. The default @@join_cache_level value is 2, looking it up here:
https://mariadb.com/kb/en/server-system-variables/#join_cache_level one can see that this means hashed join buffers are not used by default.

2. Develop a method to iterate over all values in the join buffer. This should be doable.

> 
> 
> Eduardo Berrocal García de Carellán
> Senior Software Engineer
> 
> 
> Intel Corporation  |  intel.com
> 
> 
> -----Original Message-----
> From: Sergey Petrunia <sergey@xxxxxxxxxxx>
> Sent: Wednesday, July 14, 2021 2:26 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 Mon, Jul 12, 2021 at 11:30:35PM +0000, Berrocal, Eduardo wrote:
> >     "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.
> > 
> 
> Iterating the join buffer shouldn't be hard. It is just a memory area which is filled with variable-size records which have certain fields.
> 
> Debugging a query that uses join buffers, one can see that the 
> iteration over contents of the join buffer is done in 
> JOIN_CACHE::join_matching_records()
> using these calls:
> 
>   prepare_look_for_matches();
>   while (get_next_candidate_for_match()) {
>      read_next_candidate_for_match();
>   }
> 
> The code in JOIN_CACHE::join_matching_records() is complex, because it does other things besides just iterating the cache:
> 
> * Checking of "first match" flag for queries which only need one matching row from the second table.
> 
> * Handling outer JOINs, where we apply the restriction from ON expression always, and apply the restriction from WHERE expression only after we've figured out if LEFT JOIN has a match.
> 
> AFAIU, you should ignore these inside the storage engine (produce all matches, don't check the Item_func_trig_cond items).  This will make your copy of join_matching_records simpler.
> 
> 
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
> 
> 

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