Re: [PERFORM] Performance issue with NestedLoop query

[Matheus de Oliveira]

On Thu, Jul 30, 2015 at 4:51 AM, Ram N yrami...@gmail.com wrote:

 select sum(a), count(id), a.ts, st from table1 a, table2 b where a.ts 
 b.start_date and a.ts  b.end_date and a.ts  '2015-01-01 20:50:44.00
 +00:00:00' and a.ts  '2015-07-01 19:50:44.00 +00:00:00' group by a.ts,
 st order by a.ts


You could try to use a range type:

CREATE INDEX ON table2 USING gin (tstzrange(start_date, end_date,
'()'));

Then:

select sum(a), count(id), a.ts, st
from table1 a, table2 b
where tstzrange(b.start_date, b.end_date, '()') @ a.ts
and a.ts  '2015-07-01 19:50:44.00 +00:00:00'
group by a.ts, st
order by a.ts

Regards,
-- 
Matheus de Oliveira

Re: [PERFORM] Performance issue with NestedLoop query

[Qingqing Zhou]

On Fri, Jul 31, 2015 at 10:55 AM, Ram N yrami...@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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Performance issue with NestedLoop query

[Matheus de Oliveira]

On Fri, Jul 31, 2015 at 3:06 PM, Matheus de Oliveira 
matioli.math...@gmail.com wrote:

 CREATE INDEX ON table2 USING gin (tstzrange(start_date, end_date,
 '()'));


The index should be USING GIST, not GIN. Sorry.


-- 
Matheus de Oliveira

Re: [PERFORM] Performance issue with NestedLoop query

[Ram N]

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