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>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Very specialised query
Date: 2009-03-27 22:53:22
Message-ID: C4DAC901169B624F933534A26ED7DF311F9C05@JENMAIL01.ad.intershop.net (view raw or flat)
Thread:
Lists: pgsql-performance
Hello,

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:


create index o_1x on X (start,end,id) where object_id = 1
create index o_2x on X (start,end,id) where object_id = 2
create index o_3x on X (start,end,id) where object_id = 3
create index o_4x on X (start,end,id) where object_id = 4
...
create index o_4x on X (start,end,id) where object_id not in (1,2,3,4..)


I'm not sure that putting all in one index and using the BETWEEN clause 
as in my example is the best method though.

Marc Mamin


SELECT 
    l1.id AS id1,
    l2.id AS id2
FROM
    location l1,
    location l2
WHERE l1.objectid = 1
    AND (l2.start BETWEEN  l1.start AND l1.end
         OR 
         l1.start BETWEEN  l2.start AND l2.end
         )
         l1.start
    AND l2.start <> l2.start -- if required
    AND l2.start <> l2.end   -- if required
    AND l1.id <> l2.id


UNION ALL

...
	WHERE l1.objectid = 2
...	

UNION ALL

...
	WHERE l1.objectid not in (1,2,3,4..)

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2009-03-29 21:33:30
Subject: Re: Proposal of tunable fix for scalability of 8.4
Previous:From: David ReesDate: 2009-03-27 20:33:18
Subject: Re: I have a fusion IO drive available for testing

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