Re: Performance issue with NestedLoop query

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(dot)postgres(at)gmail(dot)com>
wrote:

> On Fri, Jul 31, 2015 at 10:55 AM, Ram N <yramiyer(at)gmail(dot)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
>