Re: Performance issue with NestedLoop query

On Thu, Jul 30, 2015 at 4:51 AM, Ram N <yramiyer(at)gmail(dot)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.000000
> +00:00:00' and a.ts < '2015-07-01 19:50:44.000000 +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.000000 +00:00:00'
group by a.ts, st
order by a.ts

Regards,
--
Matheus de Oliveira