Idea: per-partition joins #716

Activity

1. erizocosmico

   added

   enhancementNew feature or requestNew feature or request

   performancePerformance improvementsPerformance improvements

   on Feb 28, 2019
2. ajnavarro commented on Mar 5, 2019

   

   ajnavarro

   on Mar 5, 2019

   Last edited by ajnavarro

   Contributor

   More actions

   If I understood correctly, this will be in that way only if the joins conditions are relations that are only possible inside one repository, right?

   In that case, what is the difference between this parallelized join and squashed tables?.
3. erizocosmico commented on Mar 5, 2019

   

   erizocosmico

   on Mar 5, 2019

   ContributorAuthor

   More actions

   @ajnavarro yup

   This is tailored for joins after squashing. There are cases in which you will have joins for sure, for example, when you are joining the result of two subqueries, which is a pretty common thing to have in any real world application.

   Consider the following query:

   SELECT uast_extract(
       uast(blob_content, 'PHP', "//uast:String"),
       '@pos') AS positions,
       repository_id,
       file_path
   FROM (
       SELECT f.repository_id,
           f.file_path,
           b.blob_content
       FROM (
           SELECT *
           FROM refs r
           NATURAL JOIN commit_blobs cb
           NATURAL JOIN blobs
           WHERE r.ref_name = 'HEAD'
               AND NOT IS_BINARY(blob_content)
       ) b
       INNER JOIN (
           SELECT repository_id, file_path, blob_hash
           FROM refs r
           NATURAL JOIN commit_files cf
           WHERE r.ref_name = 'HEAD'
       ) f
       ON b.blob_hash = f.blob_hash
           AND b.repository_id = f.repository_id
       WHERE language(f.file_path, b.blob_content) = 'PHP'
   ) t
   WHERE positions IS NOT NULL

   This cannot be squashed. Because of it, you're performing a massive cross join on a lot of data if you run it on a big dataset. If you do the joins per-partition, you're generating way less data and thus making it much faster.
   It also has another advantage: since we know only rows from the same partition will match, in-memory joins become more feasible. You don't need to keep in memory all rows of one side, only all rows of a partition. Then do next partition and so on.