Efficiently selecting single row from a select with window functions row_number, lag and lead

From: Andrew Bailey <hazlorealidad(at)gmail(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Efficiently selecting single row from a select with window functions row_number, lag and lead
Date: 2016-01-02 03:39:16
Message-ID: CAAFKzn5EOhyfAjqnfioHqZ-pfiyS9tR2uSgsREBi73fq=Oj3Yw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I would like to do the following:

select id, row_number() over w as rownum, lag(id, 1) over w as prev,
lead(id, 1) over w as next from route where id=1350 window w as (order by
shortname, id asc rows between 1 preceding and 1 following) order by
shortname, id ;

However this gives the result
1350;1;;

The following query gives the result I am expecting

select * from (select id, row_number() over w as rownum,
lag(id, 1) over w as prev, lead(id, 1) over w as next
from route window w as (order by shortname, id
rows between 1 preceding and 1 following) order by shortname, id) as s
where id=1350

1350;3;1815;1813

The explain plan is
"Subquery Scan on s (cost=0.14..15.29 rows=1 width=32)"
" Filter: (s.id = 1350)"
" -> WindowAgg (cost=0.14..13.51 rows=143 width=12)"
" -> Index Only Scan using route_idx on route (cost=0.14..10.29
rows=143 width=12)"

as it makes use of the index created as follows

CREATE INDEX route_idx
ON route
USING btree
(shortname COLLATE pg_catalog."default", id);

I believe that the index has all the data that is needed to obtain the
results in a single query.
Is it possible to write the query as a single select and if so how?

Thanks in advance

Andrew Bailey

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dane Foster 2016-01-02 03:47:39 Enforcing referential integrity against a HSTORE column
Previous Message Charles Clavadetscher 2016-01-01 19:34:34 Re: How do I implement a .XSD in Postgres?