title: Improve query speed with large table and multiple SUM
body: <p>I have a very large table that looks something like this:</p>
  
  <pre><code>event_date (DATE), event_cd (TEXT), bat_id (TEXT)
  </code></pre>
  
  <p>which has a few million rows. <code>type</code> is number stored as a string that represents a type of event ('14', '16', etc...). The goal is to calculate a value given a date range and an identifier. I created a function that takes an identifier, a start date (as TEXT) and an end date (as TEXT) and does the following:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION wOBA_period(player TEXT, start_date TEXT, end_date TEXT)
      RETURNS NUMERIC AS $$
  WITH guts AS (SELECT * FROM weights WHERE season = substring(start_date FROM 1 FOR 4) :: INT)
  SELECT CASE WHEN sum(CASE WHEN ab_fl = 'T' THEN 1 ELSE 0 END) = 0 THEN NULL ELSE (round(
      ((SELECT wbb FROM guts) * sum(CASE WHEN event_cd = '14' THEN 1 ELSE 0 END)
          + (SELECT whbp FROM guts) * sum(CASE WHEN event_cd = '16' THEN 1 ELSE 0 END)
          + (SELECT w1b FROM guts) * sum(CASE WHEN event_cd = '20' THEN 1 ELSE 0 END)
          + (SELECT w2b FROM guts) * sum(CASE WHEN event_cd = '21' THEN 1 ELSE 0 END)
          + (SELECT w3b FROM guts) * sum(CASE WHEN event_cd = '22' THEN 1 ELSE 0 END)
          + (SELECT whr FROM guts) * sum(CASE WHEN event_cd = '23' THEN 1 ELSE 0 END)) :: NUMERIC
          / (sum(CASE WHEN ab_fl = 'T' THEN 1 ELSE 0 END) + sum(CASE WHEN event_cd = '14' THEN 1 ELSE 0 END)
          + sum(CASE WHEN sf_fl = 'T' THEN 1 ELSE 0 END) + sum(CASE WHEN event_cd = '16' THEN 1 ELSE 0 END)) :: NUMERIC,
      3)) END AS woba
  FROM events WHERE bat_id = player AND event_date BETWEEN start_date AND end_date
  GROUP BY bat_id;
  $$
  LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;
  </code></pre>
  
  <p>Context: it calculates wOBA (weighted on-base average) based on certain events. The formula is the following:</p>
  
  <pre><code>wOBA = (wBB*BB + wHBP*HBP + w1B*1B + w2B*2B + w3B*3B) / (AB+BB+SF+HBP)
  </code></pre>
  
  <p>and in the <code>events</code> table, the <code>event_cd</code> corresponds to one of each of the components in the formula ('20' = 1B, '14' = BB, etc.). Finally, the weights (wXX in the formula) are different each year and stored in a table (<code>weights</code>) which I fetched for the given year and temporarily make available as <code>guts</code>.</p>
  
  <p>The one problem is that this is very low. It takes ~ 8 seconds to get the result of a call of the <code>wOBA_period</code> function. It becomes a bit of a problem when I try to update a different table that needs to make multiple function calls for each of its 5000+ rows.</p>
  
  <p>Is there any way the function could be improved to make it a bit faster?</p>
  
  <p>Thanks</p>
  
  <p>Sample data:</p>
  
  <p>from <code>events</code>:</p>
  
  <pre><code>event_date  bat_id      event_cd
  2015-05-30  linda001    2
  2015-05-30  hellj001    20
  2015-05-30  incie001    20
  2015-05-30  polla001    2
  2015-05-30  goldp001    9
  2015-05-30  goldp001    23
  2015-05-30  trumm001    21
  2015-05-30  hilla001    2
  2015-05-30  pachj001    2
  2015-05-30  ramia001    2
  </code></pre>
  
  <p>So <code>sum(case when event_cd = '20' then 1 else 0 end) group by bat_id</code> would return the number of times '20' appeared for a given <code>bat_id</code>. This is the number of '1B' in the wOBA formula.</p>
  
  <p>The <code>weights</code> table has a line like this for each year:</p>
  
  <pre><code>year    wbb     whbp    w1b     w2b     w3b     whr
  2016    0.691   0.721   0.878   1.242   1.569   2.015
  </code></pre>
  
  <p>Those are the weights that multiple each component of the formula.</p>
  
  <p>The <code>wOBA_period</code> function takes a <code>bat_id</code> and 2 dates and should return a single number.</p>