Re: Very specialised query

>> WHERE (l2.start BETWEEN  l1.start AND l1.end
>>          OR
>>          l1.start BETWEEN  l2.start AND l2.end
>>          )

>Yes, that's another way to calculate an overlap. However, it turns out to not be that fast.
>The problem is that OR there, which causes a bitmap index scan, as the leaf of a nested loop join,
>which can be rather slow.


Ok , than splitting these checks in 2 Queries with UNION  is better.
But I often read that BETWEEN is faster than using 2 comparison operators.
Here I guess that a combined index on (start,end) makes sense:

..
WHERE l2.start BETWEEN  l1.start AND l1.end
..
UNION
..
WHERE l1.start BETWEEN  l2.start AND l2.end
..


The first clause being equivalent to

    AND l1.start <= l2.end
    AND l1.end   >= l2.start
    AND l1.start <= l2.start

I don't know how you have to deal the limit conditions...


Marc Mamin