Skip site navigation (1) Skip section navigation (2)

Re: Very specialised query

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 (view raw or flat)
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

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-03-30 15:57:16
Subject: Re: Very specialised query
Previous:From: Mario SplivaloDate: 2009-03-30 15:34:31
Subject: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group