maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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