Русский

Re: Very specialised query

thread

List

Post date

Sort by

From:	Marc Mamin
Subject:	Re: Very specialised query
Date:	March 30, 2009 , 15:36:12
Msg-id:	C4DAC901169B624F933534A26ED7DF31010A50A4@JENMAIL01.ad.intershop.net (view: Whole thread, Raw)
In response to:	Re: Very specialised query (Matthew Wakeling)
Responses:	Re: Very specialised query (Matthew Wakeling)
List:	pgsql-performance

Tree view

Tree view

Very specialised query (Matthew Wakeling, 26 March 2009, 14:31:09 )
Re: Very specialised query ("Kevin Grittner", 26 March 2009, 14:47:44 )
Re: Very specialised query (Tom Lane, 26 March 2009, 15:50:35 )
Re: Very specialised query (Matthew Wakeling, 26 March 2009, 16:06:07 )
Re: Very specialised query (Matthew Wakeling, 27 March 2009, 12:46:00 )
Re: Very specialised query (Tom Lane, 27 March 2009, 14:30:13 )
Re: Very specialised query (Віталій Тимчишин, 27 March 2009, 16:36:55 )
Re: Very specialised query (Matthew Wakeling, 27 March 2009, 17:34:36 )
Re: Very specialised query (Tom Lane, 27 March 2009, 18:43:27 )
Re: Very specialised query (Matthew Wakeling, 30 March 2009, 10:24:41 )
Re: Very specialised query (Matthew Wakeling, 30 March 2009, 15:57:23 )
Re: Very specialised query (Віталій Тимчишин, 30 March 2009, 17:29:20 )
Re: Very specialised query (Matthew Wakeling, 31 March 2009, 17:08:04 )
Re: Very specialised query (Віталій Тимчишин, 31 March 2009, 21:12:02 )
Re: Very specialised query (Matthew Wakeling, 01 April 2009, 17:10:49 )
Re: Very specialised query (Dimitri Fontaine, 27 March 2009, 20:08:43 )
Re: Very specialised query (Matthew Wakeling, 30 March 2009, 10:26:16 )
Re: Very specialised query ("Marc Mamin", 27 March 2009, 22:55:30 )
Re: Very specialised query (Matthew Wakeling, 30 March 2009, 10:28:30 )
Re: Very specialised query ("Marc Mamin", 30 March 2009, 15:36:12 )
Re: Very specialised query (Matthew Wakeling, 30 March 2009, 15:59:11 )
Re: Very specialised query (Matthew Wakeling, 30 March 2009, 16:22:18 )
Re: Very specialised query (Віталій Тимчишин, 30 March 2009, 18:15:45 )
Re: Very specialised query (Matthew Wakeling, 01 April 2009, 17:33:21 )
Re: Very specialised query (Matthew Wakeling, 01 April 2009, 18:27:25 )
Re: Very specialised query (Matthew Wakeling, 02 April 2009, 15:02:35 )
Re: Very specialised query (Craig Ringer, 02 April 2009, 16:36:15 )
Re: Very specialised query ("Marc Mamin", 30 March 2009, 17:48:38 )
Re: Very specialised query (Matthew Wakeling, 31 March 2009, 11:54:52 )

>> 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 by date:

From: dan@sidhe.org
Date: 30 March 2009, 15:34:21
Subject: Re: Trying to track down weird query stalls

From: Alvaro Herrera
Date: 30 March 2009, 15:38:49
Subject: Re: Trying to track down weird query stalls