Re: Query composite index range in an efficient way - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Query composite index range in an efficient way
Date
Msg-id 499A8E6D.EE98.0025.0@wicourts.gov
Whole thread Raw
In response to Query composite index range in an efficient way  (Havasvölgyi Ottó <havasvolgyi.otto@gmail.com>)
Responses Re: Query composite index range in an efficient way  (Havasvölgyi Ottó <havasvolgyi.otto@gmail.com>)
List pgsql-performance
>>> Havasvölgyi Ottó <havasvolgyi.otto@gmail.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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Query composite index range in an efficient way
Next
From: Alexander Gorban
Date:
Subject: Call of function inside trigger much slower than explicit function call