Re: sql row constructor...works!

> > 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