Re: Overlapping ranges

On 06/18/2014 04: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 not sure what you mean by "specify a single id" but a couple comments.

1. This query will return all ids since there is no constraint to
prevent a1 from finding the matching record in a2 which will, of course,
overlap. You need to add something like ...and a1.id != a2.id...

2. Even without the above issue there is a great potential to have this
query run a very long time - especially if the indexes are such that
each row on a1 requires scanning all rows in a2. I'd test it on a small
table to make sure it gives the results you want and read up on what
indexes are most appropriate to help speed it up. (I can't help much
here as I haven't yet experimented enough with indexing on range types.)

Cheers,
Steve