| From: | "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> |
|---|---|
| To: | "Matthew Wakeling" <matthew(at)flymine(dot)org> |
| Cc: | <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Very specialised query |
| Date: | 2009-03-30 15:35:47 |
| Message-ID: | C4DAC901169B624F933534A26ED7DF31010A50A4@JENMAIL01.ad.intershop.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
>> 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Matthew Wakeling | 2009-03-30 15:57:16 | Re: Very specialised query |
| Previous Message | Mario Splivalo | 2009-03-30 15:34:31 | Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance |