Re: Performance issue with NestedLoop query

Поиск
Список
Период
Сортировка
От Ram N
Тема Re: Performance issue with NestedLoop query
Дата
Msg-id CACGZU35yrnhOj7wyu7TiAx0ix5AwobfOx0EGWJq6ysCRPKLpnQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Performance issue with NestedLoop query  (Qingqing Zhou <zhouqq.postgres@gmail.com>)
Ответы Re: Performance issue with NestedLoop query  (Qingqing Zhou <zhouqq.postgres@gmail.com>)
Список pgsql-performance
Дерево обсуждения

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.postgres@gmail.com> wrote:
On Fri, Jul 31, 2015 at 10:55 AM, Ram N <yramiyer@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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Alexandre de Arruda Paes
Дата:
Сообщение: Slow HashAggregate/cache access
Следующее
От: Qingqing Zhou
Дата:
Сообщение: Re: Performance issue with NestedLoop query