Re: a JOIN on same table, but 'slided over'

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