From: | Ram N <yramiyer(at)gmail(dot)com> |
---|---|
To: | Qingqing Zhou <zhouqq(dot)postgres(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Performance issue with NestedLoop query |
Date: | 2015-07-31 17:55:44 |
Message-ID: | CACGZU34Aw7r4bbRXNX14Be7bx9K4t6DH87KyAOcqhYJOYJikag@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)postgres(at)gmail(dot)com>
wrote:
> On Thu, Jul 30, 2015 at 12:51 AM, Ram N <yramiyer(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Matheus de Oliveira | 2015-07-31 18:06:23 | Re: Performance issue with NestedLoop query |
Previous Message | Qingqing Zhou | 2015-07-30 20:24:51 | Re: Performance issue with NestedLoop query |