From: | "news(dot)gmane(dot)org" <nis(at)superlativ(dot)dk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: a JOIN on same table, but 'slided over' |
Date: | 2007-06-26 15:16:11 |
Message-ID: | f5raju$1ia$1@sea.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Karin | 2007-06-26 15:28:49 | pg_catalog.pg_get_serial_sequence() returns NULL |
Previous Message | Andrew Sullivan | 2007-06-26 15:15:24 | Re: how to implement unusual constraint |