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
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? |