> > He's talking about this: > > http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php > > Hmm... > > So the example would be that if you have just read a record from t > that gave you value t.a=a1, t.b=b1, t.c=c1, the next one, based on > a,b,c, would be... > > select * from t where a >= a1 and b >= b1 and c >= c1 > order by a,b,c > limit 1 offset 1; > > Right?
Wrong! :) Don't feel bad, no one ever gets this right the first time, including me! If you use values 2,2,2 for a1,b1,c1, the query will not return (3,1,2)...you have to look at the key as a whole instead if specific fields. The proper SQL construct without row constructor is: select * from t where a >= a1 and (a > a1 or b>= b1) and (a > a1 or b > b1 or c > c1) order by a,b,c limit 1 ^ no offset necessary confused yet? There is a boolean reverse of the above which is even more complicated. The above query is correct but the planner doesn't 'get it' beyond the a >= a1 expreesion...not to mention the mental jumping jacks to get there in the first place. The planner always 'gets' the row constructor expression with Tom's new changes which is where the performance speedup comes in. IMO, the sql 92 row constructor was inserted for ISAM style key based table browsing without cursors...more or less a 'lost art' these days but still relevant. This is a key strategy in dealing with large tables. Blog entry is forthcoming :). Merlin ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org