Thanks much for responding guys. I have tried both, building multi column indexes and GIST, with no improvement. I have reduced the window from 180 days to 30 days and below are the numbers
Composite index - takes 30 secs
With Btree indexing - takes 9 secs
With GIST - takes >30 secs with kind of materialize plan in explain
Any other ideas I can do for window based joins.
--yr