Performance issue with NestedLoop query

[Ram N <yramiyer@xxxxxxxxx>]

Hi,

I am trying to see if I can do anything to optimize the following plan. 

I have two tables and I am doing a join between them. After joining it calculates aggregates (Sum and Count)

Table 1 : timestamp (one per day) for 2 years (730 records)

Table 2 : Window based validity records. Window here means start and end timestamp indicating a period of validity for a record. 

Hash some 10 odd columns including start_time and end_time.  (1 million records)

Machine has 244 GB RAM. Queries are taking more than a min and in some case 2-3 mins.

Below is the plan I am getting. The Nested loop blows up the number of records and we expect that. I have tried playing around work_mem and cache configs which hasn't helped. 

Query

select sum(a), count(id), a.ts, st from table1 a, table2 b where a.ts > b.start_date and a.ts < b.end_date and a.ts > '2015-01-01 20:50:44.000000 +00:00:00' and a.ts < '2015-07-01 19:50:44.000000 +00:00:00' group by a.ts, st order by a.ts

Plan (EXPLAIN ANALYZE)

"Sort  (cost=10005447874.54..10005447879.07 rows=1810 width=44) (actual time=178883.936..178884.159 rows=1355 loops=1)"

"  Output: (sum(b.a)), (count(b.id)), a.ts, b.st"

"  Sort Key: a.ts"

"  Sort Method: quicksort  Memory: 154kB"

"  Buffers: shared hit=47068722 read=102781"

"  I/O Timings: read=579.946"

"  ->  HashAggregate  (cost=10005447758.51..10005447776.61 rows=1810 width=44) (actual time=178882.874..178883.320 rows=1355 loops=1)"

"        Output: sum(b.a), count(b.id), a.ts, b.st"

"        Group Key: a.ts, b.st"

"        Buffers: shared hit=47068719 read=102781"

"        I/O Timings: read=579.946"

"        ->  Nested Loop  (cost=10000000000.43..10004821800.38 rows=62595813 width=44) (actual time=0.167..139484.854 rows=73112419 loops=1)"

"              Output: a.ts, b.st, b.a, b.id"

"              Buffers: shared hit=47068719 read=102781"

"              I/O Timings: read=579.946"

"              ->  Seq Scan on public.table1 a  (cost=0.00..14.81 rows=181 width=8) (actual time=0.058..0.563 rows=181 loops=1)"

"                    Output: a.ts"

"                    Filter: ((a.ts > '2015-01-01 20:50:44+00'::timestamp with time zone) AND (a.ts < '2015-07-01 19:50:44+00'::timestamp with time zone))"

"                    Rows Removed by Filter: 540"

"                    Buffers: shared read=4"

"                    I/O Timings: read=0.061"

"              ->  Index Scan using end_date_idx on public.table2 b  (cost=0.43..23181.37 rows=345833 width=52) (actual time=0.063..622.274 rows=403936 loops=181)"

"                    Output: b.serial_no, b.name, b.st, b.end_date, b.a, b.start_date"

"                    Index Cond: (a.ts < b.end_date)"

"                    Filter: (a.ts > b.start_date)"

"                    Rows Removed by Filter: 392642"

"                    Buffers: shared hit=47068719 read=102777"

"                    I/O Timings: read=579.885"

"Planning time: 0.198 ms"

"Execution time: 178884.467 ms"

Any pointers on how to go about optimizing this? 

--yr