Gurjeet Singh skrev:
> I missed the ORDER BY clause... Here it goes:
>
> select t1.id <http://t1.id> as id, t2.id <http://t2.id> as "id+1",
> t1.thread as thread, t2.thread as "thread+1",
> t1.info <http://t1.info> as info, t2.info <http://t2.info> as
> "info+1"
> from test as t1, test as t2
> where t2.id <http://t2.id> = ( select min(id) from test as t3 where
> t3.id <http://t3.id> > t1.id <http://t1.id> )
> order by t1.id <http://t1.id> asc;
>
> Also note that this query is much cheaper that the 'distinct on' query
> by more than two orders on magnitude ( 217.86 vs. 98040.67):
No it isn't. The estimate is much lower, but the actual times are very
close:
[explain of distinct on]
> Time: 5.003 ms
[explain of correlated subquery]
> Time: 4.125 ms
I tried on a larger table (16384 rows), and in this case the numbers are
strongly in favor of the subquery. In fact, I am still waiting for the
"distinct on" version to return ...
/Nis
