Postgres Pro
Downloads
Home   >   mailing lists

Re: Performance issue with NestedLoop query - Mailing list pgsql-performance

From Ram N
Subject Re: Performance issue with NestedLoop query
Date
Msg-id CACGZU34Aw7r4bbRXNX14Be7bx9K4t6DH87KyAOcqhYJOYJikag@mail.gmail.com
Whole thread Raw
In response to Re: Performance issue with NestedLoop query  (Qingqing Zhou <zhouqq.postgres@gmail.com>)
Responses Re: Performance issue with NestedLoop query
List pgsql-performance
Tree view

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. I am assuming its in memory join, so I am not sure why it should take such a lot of time. Increase work_mem has helped in reducing the processing time but it's still > 1 min.

--yr

On Thu, Jul 30, 2015 at 1:24 PM, Qingqing Zhou <zhouqq.postgres@gmail.com> wrote:
On Thu, Jul 30, 2015 at 12:51 AM, Ram N <yramiyer@gmail.com> wrote:
> "              ->  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"

In your case, do you have index built for both b.end_date and
b.start_date? If so, can you try

set enable_index=off

to see if bitmap heap scan helps?

Regards,
Qingqing

pgsql-performance by date:

Previous
From: Qingqing Zhou
Date:
Subject: Re: Performance issue with NestedLoop query
Next
From: Matheus de Oliveira
Date:
Subject: Re: Performance issue with NestedLoop query