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

Re: Very specialised query

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Marc Mamin <M(dot)Mamin(at)intershop(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very specialised query
Date: 2009-03-30 10:17:48
Message-ID: alpine.DEB.2.00.0903301101410.21772@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 27 Mar 2009, Marc Mamin wrote:
> if your data are mostly static and you have a few mains objects,
> maybe you can have some gain while defining conditional indexes for those plus one for the rest
> and then slicing the query:

Maybe. I thought about doing that. However, I am not convinced that would 
be much of a gain, and would require major rewriting of the queries, as 
you suggest.

> 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.

Matthew

-- 
 I'd try being be a pessimist, but it probably wouldn't work anyway.

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-03-30 10:24:32
Subject: Re: Very specialised query
Previous:From: Josh BerkusDate: 2009-03-29 21:33:30
Subject: Re: Proposal of tunable fix for scalability of 8.4

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