Skip site navigation (1)
Skip section navigation (2)
## Re: best way to fetch next/prev record based on index

### In response to

### pgsql-performance by date

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes: > > do it for multi-column keys. It seems it would be nice if some syntax > > similar to (a,b,c) > (a1,b1,c1) worked for this. > > 'nice' would be an understatement... > > if the above syntax is not defined in the standard, I would humbly suggest, > well, beg for it to work as you thought it did. That would be GREAT! ISMT it > may be that that is in fact standard...(I don't have it, so I don't know). Hum. It would seem my intuition matches the SQL92 spec and Postgres gets this wrong. From page 208 (Section 8.2.7) of http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 7) Let Rx and Ry be the two <row value constructor>s of the <com- parison predicate> and let RXi and RYi be the i-th <row value constructor element>s of Rx and Ry, respectively. "Rx <comp op> Ry" is true, false, or unknown as follows: a) "x = Ry" is true if and only if RXi = RYi for all i. b) "x <> Ry" is true if and only if RXi <> RYi for some i. c) "x < Ry" is true if and only if RXi = RYi for all i < n and RXn < RYn for some n. d) "x > Ry" is true if and only if RXi = RYi for all i < n and RXn > RYn for some n. ... (This is A July 10, 1992 Proposed revision, I don't know how far it differs from the final. I imagine they mean "Rx" in all the places they use "x" alone) That fairly clearly specifies (a,b,c) < (a1,b1,c1) to work the way you want it to. Less-than-or-equal is then defined based on the above definition. Even if Postgres did this right I'm not sure that would solve your index woes. I imagine the first thing Postgres would do is rewrite it into regular scalar expressions. Ideally the optimizer should be capable of then deducing from the scalar expressions that an index scan would be useful. -- greg

- Re: best way to fetch next/prev record based on index at 2004-07-27 17:28:08 from Merlin Moncure

Next: From:Merlin MoncureDate:2004-07-27 19:20:49Subject: Re: best way to fetch next/prev record based on indexPrevious: From: Merlin MoncureDate: 2004-07-27 18:17:15Subject: best way to fetch next/prev record based on index