What can I do about this plan?
HashAggregate (cost=8035443.21..8035445.17 rows=157 width=24)
-> Nested Loop (cost=37680.95..7890528.72 rows=28982898 width=24) <<<<< suspect
Join Filter: ((a.test_run_start_date_time >= date.start_time) AND (a.test_run_start_date_time <=
date.end_time))
-> Bitmap Heap Scan on d_trh_pbert a (cost=37677.22..1369372.99 rows=1661440 width=24)
Recheck Cond: ((test_run_start_date_time >= '2009-05-08 07:00:00'::timestamp without time zone) AND
(test_run_start_date_time<= '2009-05-15 06:59:59'::timestamp without time zone))
-> Bitmap Index Scan on idx_d_trh_pbert_sdate (cost=0.00..37261.86 rows=1661440 width=0)
Index Cond: ((test_run_start_date_time >= '2009-05-08 07:00:00'::timestamp without time zone) AND
(test_run_start_date_time<= '2009-05-15 06:59:59'::timestamp without time zone))
-> Materialize (cost=3.73..5.30 rows=157 width=24)
-> Seq Scan on lookup_ww_date2 date (cost=0.00..3.57 rows=157 width=24)
The query
select week_id,count(serial_number)
from d_trh a
inner join lookup_ww_date2 date
on ( a.test_run_start_date_time between start_time and end_time)
where a.test_run_start_date_time between '5/8/2009 7:00:00 AM' and '5/15/2009 6:59:59 AM'
group by
week_id
the lookup_ww_date looks like this
( week_id bigint
start_time timestamp
end_time timestamp
)
eg: 200949|5/8/2009 7:00:00am|5/15/2009 6:59:59AM
The whole aim of this exercise is to look at which WW the particular date falls into.