On Tue, Aug 4, 2015 at 8:40 PM, Ram N wrote:
>
> Thanks much for responding guys. I have tried both, building multi column
> indexes and GIST, with no improvement. I have reduced the window from 180
> days to 30 days and below are the numbers
>
> Composite index - takes 30 secs
>
> With Btree ind
Thanks much for responding guys. I have tried both, building multi column
indexes and GIST, with no improvement. I have reduced the window from 180
days to 30 days and below are the numbers
Composite index - takes 30 secs
With Btree indexing - takes 9 secs
With GIST - takes >30 secs with kind
On Fri, Jul 31, 2015 at 10:55 AM, Ram N 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 optimiz
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
On Thu, Jul 30, 2015 at 4:51 AM, Ram N 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
Yo
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
On Thu, Jul 30, 2015 at 12:51 AM, Ram N 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.sta
Hi,
I am trying to see if I can do anything to optimize the following plan.
I have two tables and I am doing a join between them. After joining it
calculates aggregates (Sum and Count)
Table 1 : timestamp (one per day) for 2 years (730 records)
Table 2 : Window based validity records. Window here
