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

Re: Query composite index range in an efficient way

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: Havasvölgyi Ottó <havasvolgyi(dot)otto(at)gmail(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query composite index range in an efficient way
Date: 2009-02-17 16:16:13
Message-ID: 499A8E6D.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
>>> Havasvölgyi Ottó <havasvolgyi(dot)otto(at)gmail(dot)com> wrote: 
 
> WHERE (id1>12 or id1=12 and id2>=34)
>   and (id1<56 or id1=56 and id2<=78)
 
As others have pointed out, if you are using 8.2 or later, you should
write this as:
 
WHERE (id1, id2) >= (12, 34) and (id1, id2) <= (56, 78)
 
On earlier versions you might want to try the logically equivalent:
 
WHERE (id1 >= 12 and (id1 > 12 or id2 >= 34))
  and (id1 <= 56 and (id1 < 56 or id2 <= 78))
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Alexander GorbanDate: 2009-02-17 16:17:14
Subject: Call of function inside trigger much slower than explicit function call
Previous:From: Tom LaneDate: 2009-02-17 15:53:39
Subject: Re: Query composite index range in an efficient way

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