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.postg...@gmail.com> wrote: > On Thu, Jul 30, 2015 at 12:51 AM, Ram N <yrami...@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 >
