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 On Fri, Jul 31, 2015 at 11:37 AM, Qingqing Zhou <zhouqq.postg...@gmail.com> wrote: > On Fri, Jul 31, 2015 at 10:55 AM, Ram N <yrami...@gmail.com> wrote: > > > > Thanks Qingqing for responding. That didn't help. It in fact increased > the > > scan time. Looks like a lot of time is being spent on the NestedLoop Join > > than index lookups though I am not sure how to optimize the join. > > > > Good news is that optimizer is right this time :-). The NLJ here does > almost nothing but schedule each outer row to probing the inner index. > So the index seek is the major cost. > > Have you tried build a two column index on (b.start_date, b.end_date)? > > Regards, > Qingqing >
