Re: Overlapping ranges

On Wed, 2014-06-18 at 18:08 -0600, Rob Sargent wrote:

> On 06/18/2014 05:47 PM, Jason Long wrote:
>
>
> > I have a large table of access logs to an application.
> >
> > I want is to find all rows that overlap startdate and enddate with any
> > other rows.
> >
> > The query below seems to work, but does not finish unless I specify a
> > single id.
> >
> > select distinct a1.id
> > from t_access a1,
> > t_access a2
> > where tstzrange(a1.startdate, a1.enddate) &&
> > tstzrange(a2.startdate, a2.enddate)
> >
> >
> >
> >
>
> I'm sure you're best bet is a windowing function, but your
> descriptions suggests there is no index on start/end date columns.
> Probably want those in any event.

There are indexs on startdate and enddate.
If I specify a known a1.id=1234 then the query returns all records that
overlap it, but this takes 1.7 seconds.

There are about 2 million records in the table.

I will see what I come up with on the window function.

If anyone else has some suggestions let me know.

I get with for EXPLAIN ANALYZE the id specified.

Nested Loop (cost=0.43..107950.50 rows=8825 width=84) (actual
time=2803.932..2804.558 rows=11 loops=1)
Join Filter: (tstzrange(a1.startdate, a1.enddate) &&
tstzrange(a2.startdate, a2.enddate))
Rows Removed by Join Filter: 1767741
-> Index Scan using t_access_pkey on t_access a1 (cost=0.43..8.45
rows=1 width=24) (actual time=0.016..0.019 rows=1 loops=1)
Index Cond: (id = 1928761)
-> Seq Scan on t_access a2 (cost=0.00..77056.22 rows=1764905
width=60) (actual time=0.006..1200.657 rows=1767752 loops=1)
Filter: (enddate IS NOT NULL)
Rows Removed by Filter: 159270
Total runtime: 2804.599 ms

and for EXPLAIN without the id specified. EXPLAIN ANALYZE will not
complete without the id specified.

Nested Loop (cost=0.00..87949681448.20 rows=17005053815 width=84)
Join Filter: (tstzrange(a1.startdate, a1.enddate) &&
tstzrange(a2.startdate, a2.enddate))
-> Seq Scan on t_access a2 (cost=0.00..77056.22 rows=1764905
width=60)
Filter: (enddate IS NOT NULL)
-> Materialize (cost=0.00..97983.33 rows=1927022 width=24)
-> Seq Scan on t_access a1 (cost=0.00..77056.22 rows=1927022
width=24)