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

[Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>]

OK. Have tried this one.... looks like close to 6 times slower then the
'non-standard' phrase with 'distinct on'. 

On the small dataset that I've included in my original post (ten rows of
data within TEST), I've run both queries through EXPLAIN ANALYSE, with
the following result summary (for clearity, I've cut away the details
from EXPLAIN output):

-----------STANDARD
 Total runtime: 10.660 ms
-----------DISTINCT-ON
 Total runtime: 1.479 ms
-----------

Would there be ways to optimise the standard query to get the
performance closer to the none-standard one?


-R


On Tue, 2007-06-26 at 18:05 +0530, Gurjeet Singh wrote:
> Hi Rafal,
> 
>     Just a note that this is not standard SQL... 'distinct on' is an
> extension to SQL provided by postgres.
> 
> Following query utilizes the standard SQL to get the same results:
> 
> select    t1.id as id, t2.id as "id+1",
>         t1.thread as thread, t2.thread as "thread+1",
>         t1.info as info, t2.info as "info+1"
> from test as t1, test as t2
> where t2.id = ( select min(id) from test as t3 where t3.id > t1.id);
> 
> HTH
> -- 
> gurjeet[(dot)singh](at)EnterpriseDB(dot)com
> singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
> 
> 17°29'34.37"N  78°30'59.76"E - Hyderabad *
> 18°32'57.25"N  73°56'25.42 "E - Pune
> 
> Sent from my BlackLaptop device 
> 
> On 6/26/07, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
>         Marvelous! Thenx!
>         
>         -R
>         
>         On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski
>         wrote:
>         > On 6/26/07, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote: 
>         >         Is there an SQL construct to get it?
>         >
>         > select
>         >     distinct on (t1.id)
>         >     t1.*, t2.*
>         > from
>         >     test t1
>         >     join test t2 on t2.id > t1.id
>         > order by t1.id asc, t2.id asc
>         >
>         > should do the trick.
>         >
>         > depesz
>         >
>         > --
>         > http://www.depesz.com/ - nowy, lepszy depesz
>         
>         ---------------------------(end of
>         broadcast)---------------------------
>         TIP 4: Have you searched our list archives?
>         
>                        http://archives.postgresql.org/
>