title: Optimizing multiple joins
body: <p>I'm trying to figure out a way to speed up a particularly cumbersome query which aggregates some data by date across a couple of tables.  The full (ugly) query is below along with an <code>EXPLAIN ANALYZE</code> to show just how horrible it is.</p>
  
  <p>If anyone could take a peek and see if they can spot any major issues (which is likely, I'm not a Postgres guy) that would be superb.</p>
  
  <p>So here goes. The query is:</p>
  
  <pre><code>SELECT 
   to_char(p.period, 'DD/MM/YY') as period,
   coalesce(o.value, 0) AS outbound,
   coalesce(i.value, 0) AS inbound
  FROM (
   SELECT
    date '2009-10-01' + s.day 
    AS period 
    FROM generate_series(0, date '2009-10-31' - date '2009-10-01') AS s(day)
  ) AS p 
  LEFT OUTER JOIN(
   SELECT
    SUM(b.body_size) AS value, 
    b.body_time::date AS period 
   FROM body AS b 
   LEFT JOIN 
    envelope e ON e.message_id = b.message_id 
   WHERE 
    e.envelope_command = 1 
    AND b.body_time BETWEEN '2009-10-01' 
    AND (date '2009-10-31' + INTERVAL '1 DAY') 
   GROUP BY period 
   ORDER BY period
  ) AS o ON p.period = o.period
  LEFT OUTER JOIN( 
   SELECT 
    SUM(b.body_size) AS value, 
    b.body_time::date AS period 
   FROM body AS b 
   LEFT JOIN 
    envelope e ON e.message_id = b.message_id 
   WHERE 
    e.envelope_command = 2 
    AND b.body_time BETWEEN '2009-10-01' 
    AND (date '2009-10-31' + INTERVAL '1 DAY') 
   GROUP BY period 
   ORDER BY period
  ) AS i ON p.period = i.period 
  </code></pre>
  
  <p>The <code>EXPLAIN ANALYZE</code> can be found here: <a href="http://explain.depesz.com/s/1LA" rel="noreferrer">on explain.depesz.com</a></p>
  
  <p>Any comments or questions are appreciated. </p>
  
  <p>Cheers</p>