foo2.sql

explain SELECT 
          date_trunc('week', ovscweek_start_date) as week,
          to_char(ovscweek.ovscweek_start_date, 'MONYY') as start_date,  
          SUM(ftconsum.ftconsum_fish_cnt) as fish, 
          SUM(ftconsum_fish_age * ftconsum_fish_cnt) / SUM(ftconsum_fish_cnt) as days_old, 
          SUM(ftconsum.ftconsum_removal_watch_cnt) as watch, 
          SUM(ftconsum.ftconsum_removal_order_cnt) as order, 
          SUM(ftconsum.ftconsum_removal_retired_cnt) as retired, 
          SUM(ftconsum.ftconsum_removal_other_cnt) as other
        FROM 
          ovscweek
        LEFT OUTER JOIN ftconsum 
          ON ftconsum_scweek_id = ovscweek_id
        
        WHERE 
          ovscweek_start_date BETWEEN '2007-01-01' AND current_date
          /*
          ovscweek_start_date < current_date
          AND
          ovscweek_start_date > '2007-01-01'
          */
        GROUP BY ftconsum_scweek_id,ovscweek_start_date

 
        /*ORDER BY ovscweek_start_date ASC */

"GroupAggregate  (cost=84413.76..99379.02 rows=252200 width=32)"
"  ->  Sort  (cost=84413.76..85258.37 rows=337846 width=32)"
"        Sort Key: ftconsum.ftconsum_scweek_id, ovscweek.ovscweek_start_date"
"        ->  Hash Right Join  (cost=59.99..45304.35 rows=337846 width=32)"
"              Hash Cond: (ftconsum.ftconsum_scweek_id = ovscweek.ovscweek_id)"
"              ->  Seq Scan on ftconsum  (cost=0.00..34912.20 rows=1854320 width=28)"
"              ->  Hash  (cost=53.93..53.93 rows=485 width=8)"
"                    ->  Bitmap Heap Scan on ovscweek  (cost=13.23..53.93 rows=485 width=8)"
"                          Recheck Cond: ((ovscweek_start_date >= '2007-01-01'::date) AND (ovscweek_start_date <= ('now'::text)::date))"
"                          ->  Bitmap Index Scan on ovscweek_start_date_idx  (cost=0.00..13.11 rows=485 width=0)"
"                                Index Cond: ((ovscweek_start_date >= '2007-01-01'::date) AND (ovscweek_start_date <= ('now'::text)::date))"