Re: Very specialised query

Список
Период
Сортировка
От Marc Mamin
Тема Re: Very specialised query
Дата
Msg-id C4DAC901169B624F933534A26ED7DF31010A50A4@JENMAIL01.ad.intershop.net
обсуждение исходный текст
Ответ на Re: Very specialised query  (Matthew Wakeling)
Ответы Re: Very specialised query  (Matthew Wakeling)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Mario Splivalo
Дата:
Сообщение: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Следующее
От: Matthew Wakeling
Дата:
Сообщение: Re: Very specialised query