On Tue, Aug 4, 2015 at 8:40 PM, Ram N <yramiyer@xxxxxxxxx> 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 indexing - takes 9 secs > > With GIST - takes >30 secs with kind of materialize plan in explain > > Any other ideas I can do for window based joins. > >From this query: 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 We can actually derive that b.start_date > '2015-07-01 19:50:44.000000 +00:00:00' and b.end_date < '2015-01-01 20:50:44.000000 +00:00:00'. If we add these two predicates to the original query, does it help? Thanks, Qingqing -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
