title: hadoop pig self join performance
body: <p>I have a dataset of users and elements in which I would like to find any pairs of users that have at least one overlapping element. My data structure looks like:</p>
  
  <pre><code>id    element
  --------------
  1     a    
  1     b
  1     b
  2     b
  3     a
  4     c
  </code></pre>
  
  <p>In this case, I would generate the following tuples:</p>
  
  <pre><code>(1,2) // both have element "b" in common
  (1,3) // both have element "a" in common
  </code></pre>
  
  <p>I've written the following pig script that works at small scales, but when I even 1 million rows (~500MB), I killed the job after 1.5 hours because it had generated nearly 40GB of data, which seems a little out of proportion for what I'm trying to accomplish. I'm new to pig, so I'm hoping this can be optimized a bit. Any help would be appreciated.</p>
  
  <pre><code>-- load the data
  mydata = LOAD '/path/to/my/data' USING PigStorage('\t') AS (user:int, element:chararray);
  -- generate a copy to do a self join with
  A = FOREACH mydata GENERATE user as user_2, element as element_2;
  -- join them based on common tags
  B = JOIN mydata BY element, A by element_2;
  -- we only want the mapping in one direction, e.g. (1,2) is the same as (2,1)
  C = FILTER B BY user &lt; user_2;
  -- we're only interested in the user ids
  D = FOREACH C generate user, user_2;
  -- remove any duplicate tuples
  E = DISTINCT D;
  STORE E INTO '/path/to/output';
  </code></pre>
  
  <p>Note: This is a follow-up to my previous question <a href="https://stackoverflow.com/questions/22498259/hadoop-pig-joining-on-any-matching-tuple-values">hadoop pig joining on any matching tuple values</a> with a slightly different approach</p>