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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

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 AS id1, AS id2
    location l1,
    location l2
WHERE l1.objectid = 1
    AND (l2.start BETWEEN  l1.start AND l1.end
         l1.start BETWEEN  l2.start AND l2.end
    AND l2.start <> l2.start -- if required
    AND l2.start <> l2.end   -- if required
    AND <>


	WHERE l1.objectid = 2


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

In response to


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-2017 The PostgreSQL Global Development Group